Splitting out values from multiple linked records

[olireynoldson]

Hi everyone,

I’m looking to incorporate the tracking of SLA’s into our base to measure performance against timestamps. Currently I have a table of all our SLA’s with descriptions and numeric values for the SLA which will be used in the calculation. We have other tables where we’d be linking the SLA against a record to pull through the numeric value to then compare against a timestamp in a formula.

However, we have some records where more than one SLA would apply to them for which we’d have multiple records linked. In this scenario I then get multiple numeric values for different SLA’s all in the same lookup column. The knock on is that I cant now use the numeric value of the SLA in the formula to calculate weather the SLA was met or not.

Current Ideas:

1. I could just set up columns for each of the SLA’s that apply to the records in the table I'm pulling them through to and hardcode the values in. However, if the SLA is changed then I’d be keen to avoid going through and updating all the rows.
2. I could add extra columns to the SLA table to split out the numeric values and lookup against multiple columns rather than just the one.

Any ideas on how best to structure this or how to split out the numeric values from the lookup into separate columns using formulas? Would this be an array and a formula looking at the nth value in the array? Or any advice of another workaround?

Cheers!

[Nathan_Gupta]

Hi there!

I might consider using a third table called "SLA comparison". Your two working ideas might suffice, however, the dynamicity of a new table might be preferred for extensibility.

Consider this example:

Now the formula I used to check if an SLA was met...

IF({Total Days (from Contract)}, IF({Total Days (from Contract)} <= {Completion Limit (# Days) (from SLA)}, "Meets SLA", "Doesn't Meet SLA") , "In Progress")

Now the key in making this work effectively is how you automatically generate the various permutations for Contract & SLA. In this example, I'm showing a "Generate Analysis" button on each Contract record. This button then connects to a custom script that runs a simple loop taking the contract record and generating a new record in the Contract-SLA Analysis table with one of the linked SLAs.

You could also achieve this exact same behavior using the Airtable automations and repeating actions functionality. Let's say you use a checkbox as the trigger, and this moves the record into a view, then you should be able to run an automation that takes all Contract record and all the linked SLAs and generates new records in the Contract-SLA Analysis Table.

Hope this helps! 🙂