Skip to main content

Generate Text Based on Rules and Parts of Post Code

  • July 26, 2019
  • 3 replies
  • 35 views
L

Hey Everyone, I’m pretty new with Airtable and really enjoyed the freatures that it gives the user. I’m creating a Data-Sheet that basically manages Leads and also outputs them to Asana, Now my question was if there is a possibilty to generate a Text (Name of an Employee to Handle the Case) in a seperate cell based on a Number in a cell (Number of Teams - Prioritized) and a Postcode in another cell (The Data in the Sheet Currently has 5-6 Characters, all numbers) The thing is we seperate Tasks based on the first 2 characters of the postcode to our Employees. Do you see any Possibility to get this to work?

Thank you in Advance :grinning_face_with_big_eyes:

3 replies

Justin_Barrett
Forum|alt.badge.img+21

Welcome to the community, Leon! :grinning_face_with_big_eyes: Part of what you want to do makes sense, but part is a little unclear. You say there are two criteria that drive the assignment:

  1. “a Number in a cell (Number of Teams - Prioritized)”
  2. “the first 2 characters of the postcode”

Let’s address #2 first. Using LEFT will let you pull off the first two characters from the postcode, assuming it’s a text field. If it’s a numeric field, the value will have to be concatenated with a string before LEFT can operate on it, so it’s going to be either:

LEFT({Postcode}, 2)

…or…

LEFT({Postcode} & "", 2)

SWITCH will let you control the output based on what’s found in those two characters. Here’s an example using a text-based postcode field to assign to one of three people. Just plop in the other version if it’s numeric.

SWITCH(
    LEFT({Postcode}, 2),
    "01", "Person 1",
    "02", "Person 2",
    "03", "Person 3"
)

Where I’m a little lost is with your first requirement, “a Number in a cell (Number of Teams - Prioritized)”. How exactly does that play into the rest of this setup? Are there different teams, and that number determines which team is assigned, with the postcode then corresponding to different people on different teams? If you can share a more detailed example, it’ll help us figure out what can be done.

L
  • Leon_Wetterich
  • Author
  • New Participant
  • July 27, 2019

Hey Justin. Thank you really much for the detailed Response. So i hope to clear things up on what is meant by the second part. The Number in a Cell is the Amount of Teams in a society. This should be prioritized based on the amount/number of members that are in those societies.

Example https://airtable.com/shrFItHa4d005KZUK/tblrr5PgZakhxIupN?blocks=hide

The “Assessed to” Field is a Formula, the Postcode is a Numeric Field. In the ‘Amount of Teams/Number of Members’ there either is the Number of Teams or the Number of Members so maybe it would be smarter to put that into two separate Columns.

I hope that makes things clearer. Either Way im very grateful for the help i received so far.

Thank you again.

Justin_Barrett
Forum|alt.badge.img+21

Hey Justin. Thank you really much for the detailed Response. So i hope to clear things up on what is meant by the second part. The Number in a Cell is the Amount of Teams in a society. This should be prioritized based on the amount/number of members that are in those societies.

Example https://airtable.com/shrFItHa4d005KZUK/tblrr5PgZakhxIupN?blocks=hide

The “Assessed to” Field is a Formula, the Postcode is a Numeric Field. In the ‘Amount of Teams/Number of Members’ there either is the Number of Teams or the Number of Members so maybe it would be smarter to put that into two separate Columns.

I hope that makes things clearer. Either Way im very grateful for the help i received so far.

Thank you again.


That definitely clears things up. Based on that info, and your example, I definitely recommend using separate fields for members vs teams, not so much for the sake of the formula, but just for clarity for anyone else who happens to use this base/table.

Here’s a sample that I put together using your data:

The formula for {Assessed to} is:

IF(
    OR(
        Teams > 23,
        Members > 600
    ),
    "Luis",
    SWITCH(
        LEFT(Postcode & "", 2),
        "81", "Mandy",
        "84", "Josh"
    )
)
Terms & ConditionsAccessibility statement