2

I would like to run a report of UK postcodes with aggregation. The first four characters of the postcode give an area of perhaps a few square miles, the last three characters are specific to a street so not very helpful.

Is it possible to do this in SearchKit by rewriting the text or using the field transformation to do this? I did wonder about using the Data Segmentation but we can have up to 70 different variations (and this isn't always predictable) so this would have to be perhaps 40-50 of the most common ones and then 'Other' as a catch all. Setting up the criteria for 'Other' could also be problematic if we have to say != one of 40 other defined patterns.

We currently use Views with some simple raw SQL :

Left(civicrm_address.postal_code,4)

...but a colleague has spotted that the numbers don't add up properly. I can try to troubleshoot that but I'd rather recreate it in SearchKit if it's possible.

(If you're interested, the issue in Views is that the subtotal appears to be correct and matches a search of contacts in the main database, but if I copy and paste the list into Excel and run a SUM formula, it's about 5% out).

2 Answers 2

4

I've put up a PR to make LEFT(), RIGHT() and SUBSTRING() available. If you don't want to wait for the release with these, just put a copy of those files into the right place.

That will let you show the truncated string in SearchKit but you can't add the Group By directly although SearchKit does understand it. So ...

  1. Set up the rest of your search and add a Group By using the relevant postal_code (ignore the truncation part for now)

  2. Save your search, go to the main search listing and click Export.

  3. Use the Copy to clipboard button

  4. Close the pop-up, and click Import on the Saved Searches listing.

  5. In the pop-up, paste what you just exported.

  6. Go the groupBy section and change address_primary.postal_code to LEFT(address_primary.postal_code, 4)

enter image description here

  1. Finish with Import

  2. Now open the search. You should see the grouping correctly displayed eg:

enter image description here

... and the results should be grouped as you want. :-)

2
  • 1
    Amazing, thank you for the instructions. Only two slight comments - when I imported it, it overwrote the one that used to export/copied from and I wasn't expecting that, although it makes perfect sense. I'm just stating it here in case anyone is following this and wants to keep a separate copy of before and after. Secondly, the agreggation is working but the label in my Postcode column still shows full postcodes - should I be able to use LEFT in the field transformation (it's not listed)? Commented Jun 18, 2023 at 15:00
  • 1) Good catch about overwriting 2) yes, the LEFT in the Group By is separate from any displayed columns, so if you want to display the postcode, also add a LEFT to it, but you can do that in the field transformation part of the GUI
    – Aidan
    Commented Jun 18, 2023 at 18:07
0

Civi provides a smarty modifier that's the same as php substr, so if you add a search display to your searchkit you can check the box for rewrite on the field and use that modifier, like:

{'[postal_code]'|substring:0:4}

The single quotes are important.

The exact token depends your query. It might be something like [Address.postal_code]. You can use the </> dropdown to insert it.

P.S. I am seeing some strangeness though. It works for 1:4, but not 0:4. Odd.

5
  • This was my first thought too, but I think the asker is trying to aggregate by the first four digits, which would have to happen in the search itself, I think, rather than the display.
    – Lars SG
    Commented Jun 14, 2023 at 18:20
  • 1
    Ok if that's the case then perhaps core would accept adding LEFT as an option: github.com/civicrm/civicrm-core/tree/…. It seems generally useful.
    – Demerit
    Commented Jun 14, 2023 at 18:32
  • Agreed, seems like a good addition to me.
    – Lars SG
    Commented Jun 14, 2023 at 20:13
  • Thank you both for the replies. Yes, it would need to be in the search so it would be great if that could be added as an option! Commented Jun 16, 2023 at 8:39
  • 1
    There's a PR up to add it but now that I'm looking at it, for this specific use case I'm not sure it will help. I don't see where you can group-by on a transformed field in searchkit.
    – Demerit
    Commented Jun 16, 2023 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.