If And Or Function

Do you have your contractors listed in a separate table from the “Issues”?

Hi,

Yes, I have a separate contractor table and then a location table.

Ok – so by default in Airtable, there is not a way to automate the linking of records, and linking is the way you would have to connect a contractor with an issue at a location, since they are in separate tables.

I assume what you are wanting to do is this:

• When an issue is linked to location A, B, or C, automatically assign it to contractor A
• When an issue is linked to location D, E, or F, automatically assign it to contractor B
• etc…

I can see 3 options for you to do what you’re wanting:

1. Use an external automation service, such as Zapier (it’s possible to set up actions in Zapier that will perform this linking automatically for you, but Zapier will require a subscription outside of, and in addition to Airtable)
2. Ditch the Contractors table, and write the contractors into a formula in your “Issues” table. It would look something like this:

SWITCH(
   Location,
   "A", "Contractor A",
   "B", "Contractor A",
   "C", "Contractor A",
   "D", "Contractor B",
   "E", "Contractor B",
   "F", "Contractor B"
)

3. This last option requires the most setup work, but also yields some benefits and flexibility that other options lack – Make your contractor table into a “Schedule” of contractors, rather than a “Table” of contractors. This entails swapping it from listing the contractors vertically, each one as a new record, to listing the contracting horizontally, each one in a new field. The primary field of this table would be the name of your “Schedule”, and then you’d make a “Field” for each contractor. For example, the field name is “Location A”, and then in the cell for that “Schedule” record, you put the name of the contractor you use for Location A, and so on, with a field for each Location (this means your contractor names will be repeated across multiple fields for as many locations as a single contractor serves).
   After your schedule is made, you can simply link each new “Issue” record you made to your schedule (linked record field always links to the same schedule record). Next, you have to pull the value from each field of your schedule into the “Issues” table with Lookup fields - a new “Lookup” field for each Location field in your Schedule table (this is tedious, but again… benefits and flexibility). You can hide all of these lookup fields so they aren’t littering your visual space in the table. Lastly, you can now create your SWITCH() formula to pull its contractor from the appropriate lookup field given the matching condition, rather than using straight text as I did above:

SWITCH(
   Location,
   "A", {Lookup Location A},
   "B", {Lookup Location B},
   "D", {Lookup Location D},
   etc...
)

So what are the benefits of Option 3? What is the flexibility it provides? Well, supposing in the future you change contractors… with this “Schedule” table, you can create a New schedule, placing the new contractor in the appropriate fields, and begin linking all future records to this new schedule, rather than the old one. All future records will now pull the new contractor name in for the appropriate locations, rather than the old on, but importantly, all OLD records will remain unchanged, still showing the work that was done by the previous contractor, since they are still linked to the old schedule.

If that’s not clear, I’ve explained this “Schedule” concept in the context of a “Pay Schedule” elsewhere:

As I said, the main advantage is the avoidance of mutating historical data, which you do not get if you use option 2 and then change the contractors I the formula down the road.

Hope that helps!

Hi Jeremy,

Thank you so much for your in depth reply/solution. I will look into this.

I assume that I would just need to repeat for each issue at each site as each issue is assigned to different contractor at each site.

Site A - Oven Issue (Contractor 1)
Site A - Door Issue (Contractor 2)
Site B - Oven Issue (Contractor 3)
Site B - Door Issue (Contractor 2)

Kind Regards.

C