List Records IF({Postcode}=ARRAY())

Ruben_Rubens · ‎Apr 10, 2018

Hi I’m new to databases let alone working out the powerful features of Formulas and Lookups and stuff

I need to sort my contacts into municipalities.
Each Contact has a Postcode
Each Municipality has multiple postcodes.

I need to be able to print out lists of contacts in multiple different municipalities
(so simply using filters isn’t ideal)

Should I write a very long nested IF formula eg.
IF({Postal Code}=ARRAY(3000,3001,3002), “Melbourne”,IF ({Postal Code}=ARRAY(2000,2001,2002), “Sydney”, “Somewhere Else”))

& how would I write this so that it actually works?!

Or is there a better way to do this with linked records?

Thanks heaps for your advice
Ruben
oldertenants.org.au

Jeremy_Oglesby · ‎Apr 10, 2018

If I understand your question correctly, I think this is probably best done with linked records.

Here’s an example base that emulates your use case:

Linked Records Example - Airtable

Explore the "Linked Records Example" base on Airtable.

The first table holds your contacts.
The second table holds your zip codes.
The third table holds your municipalities.

Contacts have a single postcode (link to records [postcodes] - single record only):

Create a Link to another record field in the Contacts table, linked to the Postcodes table, and disallow linking to multiple records

Postcodes have a single municipality (link to records [municipalities] - single record only):

Create a Link to another record field in the Postcodes table, linked to the Municipalities table, and disallow linking to multiple records

Contacts now have a single municipality (lookup the [municipality] linked to the Contact’s [postcode]):

Create a Lookup field in the Contacts table that looks at the Postcode field, and finds the Municipality the post code belongs to

Now you can create a View in the Contacts table that groups on the Municipality field - and likewise in the Postcode table, which can group on the Municipality field as well.

Poke around in the base I shared and let me know if you need any further help.

Ruben_Rubens · ‎Apr 11, 2018

Thanks so much for your clear explanation Jeremy…
I think I understand but still having an issue setting this up with the records in our existing base

That base has records of contacts, with a number field for post code, already populated.
The info on postcodes I’ve reformatted from a crazy government spreadsheet into this format

Victorian State Electorates - Airtable

Explore the "Victorian State Electorates" base on Airtable.

So ideally I could turn the Postcode field in the Contacts grid into a Link field, and those numbers turn into a link, but I don’t know if thats possible?

Or again, I’m probably going about this in all the wrong ways.

Jeremy_Oglesby · ‎Apr 11, 2018

I think you’ll want to reformat that. Try following these steps:

Make a duplicate of your base with all records and make these changes in the duplicate (in case you mess up)
Make a third table called Postcodes (delete the records and the Notes and Attachments fields that it makes automatically)
Go to your Victorian Government Electorate Info table and select the entire Postcode field by clicking on the title bar (you should see the field and all it’s records turn blue)
(Ctrl / Cmd) + C to copy all the whole field and all its records
Go to your newly made Postcodes table, select the Name field (which should be the only field in there), and (Ctrl / Cmd + V) to copy the Postcode field into that table - rename as necessary
Go back to your Victorian Government Electorate Info table, and change the Postcode field from a “multi-select” field to a “Link to another record” field linked to the Postcodes table - click “Ok” at the prompt, and watch Airtable’s magic happen
Go to your Contacts table and change the Postcode field from a “Number” field to a “Link to another record” field linked to the Postcodes table

That should do you what you are wanting to do. Post back if there are any issues or if this doesn’t accomplish what you are looking for.

Jeremy_Oglesby · ‎Apr 11, 2018

Looking back at your base again, I realize that you’ll want to do some further editing of the Victorian Government Electorate Info table, which may prove to be a bit onerous, in order to have only a single record for each electorate (whereas right now you have multiple records for each electorate, one for each postcode in the electorate).

I copied your base and I’m working on it now to see if I can expedite the process of pruning that down.

Jeremy_Oglesby · ‎Apr 11, 2018

Well, there was no super elegant solution to fixing the duplicates in the Electorate table, but I was able to find a method that made it go pretty quickly.

Here’s a quick screen-recording of how I did it:
Recording

I’m sure somebody smarter than myself can tell me how I could have done that more efficiently, but it only took about 15 minutes to get all the duplicates removed.

However, there’s another small issue you’ll notice, that some postcodes actually span multiple electorates - which means in the contacts table, where I have the lookup field finding the contact’s electorate based on postcode, there is the possibility of there being multiple electorates listed for that postcode. I’m not sure how you want to go about fixing that.

Here’s the resulting base - feel free to copy the entire base into your workspace (top-right corner when viewing the base) and resume doing what you need to do with it:

Victorian State Electorates - Airtable

Explore the "Victorian State Electorates" base on Airtable.

Ruben_Rubens · ‎Apr 12, 2018

Wow thank you so much Jeremy.
Really above and beyond support!

Such a powerful use of linking feature!
With it I’ve been able to print out a list of all our member’s emails who live in each electorate, which is a massively useful campaign tool for a little community group like ours.

You’re right that there is a big issue with postcodes spanning multiple electorates, so a significant group of members may in fact be living in adjacent electorates. The only way I can think of to solve this issue with the address data we have is to segment emails into those who definitely live in an electorate and those who may.

Although GIS data of electorate boundaries is available if anyone knows how to solve that issue but its beyond me and probs beyond the scope of airtable https://www.aec.gov.au/Electorates/gis/gis_datadownload.htm - dogooder.co must have worked it out somehow.

Anyway this more simple tool is super useful and I’ll share it with other grassroots community and environment campaigns before the state elections!