View only the longest turnaround time

[Brian_Barnes]

Good day to all

I am looking for some help for my problem after spending all morning on it. 

My table is an 'audit table' for blood tests. I have a colum that is a linked field to another table that allows me to select the blood test requested (Samples requested). With each Sample there is an associated turnaround time from the lab. I have set this up as a single select and it is either 24hrs, 48hrs, 72hrs or 5 Days. To view these TAT, I have a colum with 'expected TAT' and this is a lookup field. However, when I select several Samples, I can have several TAT. I only want to see the longest TAT ie if I had 24hrs, 24hrs, 48hrs - I would only see 48hrs. 

I have tried several different formulas and scripting and not wining at all. 

Any help is appreciated.

[Josh_Colina]

Hi Brian! One way you can solve this is by using a Rollup field with the MAX(values) aggregator to return the only the highest TAT of the linked samples. In order to do this, you'll first need to make the output of the samples field you are rolling up a number.

Single-select fields output strings, not numbers - your easiest method would be to change the field type for "Expected TAT" from a single-select field to a number field (maybe "Expected TAT in Hours"). If you want to limit options for users and retain the single-select, then you can also add a formula field in your Samples table to read the "Expected TAT" field and transform it into a number (more on that below) - use that formula field as the basis for your roll-up in your Audit table!

Some caveats here:

• This will generate an at-a-glance highest TAT value for your linked samples. It won't filter down your linked samples to only the one with the highest TAT (so that you could, for example, easily click on that sample record from your audit table)
• To transform your single-select from a string output to a number, I'd recommend changing your single-select options to numbers only (for example, "24" instead of "24 hrs"). You could use the formula VALUE() to complete this transformation without removing "hrs", or a simple formula like the one below will turn this value into a number. You may want to reconsider how you signify the "5 days +" status

{Expected TAT single select field} + 0​

Hope this helps!