Skip to main content
Solved

Assign salesperson based on Postcode - How to overcome 1 letter vs 2 letter postcode

  • July 9, 2020
  • 3 replies
  • 29 views
L

Hi,
I’m not very clued up when it comes to the more complex features of Airtable so I’m hoping someone in this community can offer some assistance :slightly_smiling_face:

I managed to solve part of my issue through searching these community forums here

I am using UK postcodes to determine a salesperson’s region. I have a list of these vs salesperson which can be viewed here.

I have a webform that is pulling into Airtable using Zapier so it populates the fields whenever a form is submitted. I’m now adding some extra fields to this so that our sales team can utilise the base as a leads tracker.

I’ve got as far as using a formula to assign a salesperson based on the first 2 digits of the postcode:

And that is working ok, but it leaves me with the issue of when a postcode starts with a single letter as is the case on line 5 here.

I also wondered whether it’s possible to add a collaborator based on the postcode so that they would be notified, but I think that might be a step too far vs what Airtable can actually do.

Thanks in advance for any advice I can get.
Lucy

Best answer by Justin_Barrett

Welcome to the community, @Lucy_Evans! :grinning_face_with_big_eyes: My first thought is that you could write a formula that nests various SWITCH() functions. Here’s a rough example using just a few options:

SWITCH(
    LEFT(Postcode & "", 1),
    "A", SWITCH(
        MID(Postcode & "", 2, 1),
        "B", "Salesperson 2",
        "L", "Salesperson 1"
    ),
    "E", SWITCH(
        MID(Postcode & "", 2, 1),
        "C", "Salesperson 1",
        "H", "Salesperson 2",
        "N", "Salesperson 1",
        "X", "Salesperson 7",
        "Salesperson 1"
    )
)

The first thing we check is the leftmost character in the postcode. If it matches a certain letter, the result depends on the next SWITCH() level, which checks the second character. With the A postcodes in your sample table, there are only two options, and both have second letters we can pull from. With the E group, there are for with second letters, and one that’s just E on its own, so that E-only option becomes the default if the others don’t match.

Does that make sense?

    3 replies

    Justin_Barrett
    Forum|alt.badge.img+21

    Welcome to the community, @Lucy_Evans! :grinning_face_with_big_eyes: My first thought is that you could write a formula that nests various SWITCH() functions. Here’s a rough example using just a few options:

    SWITCH(
    LEFT(Postcode & "", 1),
    "A", SWITCH(
        MID(Postcode & "", 2, 1),
        "B", "Salesperson 2",
        "L", "Salesperson 1"
    ),
    "E", SWITCH(
        MID(Postcode & "", 2, 1),
        "C", "Salesperson 1",
        "H", "Salesperson 2",
        "N", "Salesperson 1",
        "X", "Salesperson 7",
        "Salesperson 1"
    )
)

    The first thing we check is the leftmost character in the postcode. If it matches a certain letter, the result depends on the next SWITCH() level, which checks the second character. With the A postcodes in your sample table, there are only two options, and both have second letters we can pull from. With the E group, there are for with second letters, and one that’s just E on its own, so that E-only option becomes the default if the others don’t match.

    Does that make sense?

    JonathanBowen
    Forum|alt.badge.img+18

    Hi @Lucy_Evans - unless you are only operating in a few postcodes areas, this approach might not be scalable for you. There are more than 3000 “outer” postcodes in the UK (the first part of the postcode), so that formula could get a bit long if you need to accommodate even a small fraction of these. If you get new salespeople or responsibilities change, then you will have to edit the formula which is fiddly.

    Another approach might be to have a reference table of the outer postcodes and assign salespeople to these - like this:

    Then, in your main table you can extract the outer part of the postcode - everything up to the space. (This assumes that all of your postcodes have the space!).

    The final step is to link it to the postcode table so that you can lookup the salesperson. There’s various ways to do this - copy and paste into a linked field, a script, Zapier for example.

    Justin_Barrett
    JonathanBowen
    Forum|alt.badge.img+18

    Hi @Lucy_Evans - unless you are only operating in a few postcodes areas, this approach might not be scalable for you. There are more than 3000 “outer” postcodes in the UK (the first part of the postcode), so that formula could get a bit long if you need to accommodate even a small fraction of these. If you get new salespeople or responsibilities change, then you will have to edit the formula which is fiddly.

    Another approach might be to have a reference table of the outer postcodes and assign salespeople to these - like this:

    Then, in your main table you can extract the outer part of the postcode - everything up to the space. (This assumes that all of your postcodes have the space!).

    The final step is to link it to the postcode table so that you can lookup the salesperson. There’s various ways to do this - copy and paste into a linked field, a script, Zapier for example.


    FYI, you can download a list of the outer postcodes from here:

    https://www.freemaptools.com/download-uk-postcode-lat-lng.htm

    Terms & ConditionsAccessibility statement