Formula Help - Multiple Linked Records Providing Error Message

[Ashley_Hogrebe]

Hey Airtable Friends! :slightly_smiling_face:

I’m working on a project where we want to have an error message pop up if our team accidentally selects a billable item that is not authorized to bill for that specific product sold.

It works perfectly when there is only one linked record in the ‘Product Sold’ field, but if there are two linked records, it will always give a ‘ :stop_sign: This Service is NOT Authorized to use this Billable Item’ message, even if the linked records are included in the ‘What Products Can Use This Billable Item’ field.

This is Formula I’ve set up:

IF(FIND({Product Sold (from Project)},{What Products Can Use This Billable Item}) <= 0, '🛑 This Service is NOT Authorized to use this Billable Item', '✅ This service is authorized to using this Billable Item')

Is there a way to edit this formula to work even if there are multiple records in the Product Sold field?

Thanks so much!

[Jeremy_Oglesby]

Hi @Ashley_Hogrebe,

This is just an unconfirmed hunch, but I think that the fact that the lookup fields are actually holding arrays of record references, and not just a string of text, is what’s throwing off your formula. If the {Product Sold (from Project)} lookup only has a single record in it, I think Airtable is happy to force it into behaving like a string for your FIND() function, but if that field is holding reference to more than 1 record, then it’s coming through as an Array, and so FIND(), not knowing what to do with an Array, is just throwing 0 every time.

If my hunch is correct, I believe you can force both fields into behaving like strings for the FIND() function by concatenating them with an empty string when you reference them like this:

IF(
   FIND(
      {Product Sold (from Project)} & '',
      {What Products Can Use This Billable Item} & ''
   ) <= 0,
   ...,
   ...
)

Why don’t you give that a shot and see if it fixes your problem.

[Ashley_Hogrebe]

Hey Jeremy!

Thank you so much for the idea & your quick reply! I appreciate it greatly. I tried out your solution and unfortunately it didn’t change anything.

Just dropping exactly what I tried in case I’m missing something silly.

IF
(FIND(
{Product Sold (from Project)}&'',{What Products Can Use This Billable Item}&'') <= 0,
 '🛑 This Service is NOT Authorized to use this Billable Item', '✅ This service is authorized to using this Billable Item')

Any other ideas? :laughing:

[Jeremy_Oglesby]

Ok, my second (again, unverified…) hunch is that it has to do w/ your use of FIND() as opposed to SEARCH().

Why don’t you give this a try:

IF(
  SEARCH(
    {Product Sold (from Project)}&'',
    {What Products Can Use This Billable Item}&''
  ),
  '🛑 This Service is NOT Authorized to use this Billable Item', 
  '✅ This service is authorized to using this Billable Item'
)

[Jeremy_Oglesby]

Scratch that… I see the problem.

If you look at your record that is producing the stop sign, you’ll see that when treated as a string, it will be:
"Tier 2 Master Plan"

And the string it is being compared to is
"Tier 2 Tier 3 Tier 1 Master Plan"

The FIND() and SEARCH() functions are looking for an exact match of the first string inside the second string, which it will not find if the words in the second string are not present in the same order as they are in the first string. I think you’ll need to explore a different approach here, and I’m not sure what that might be off the top of my head.

[Jeremy_Oglesby]

I’m not seeing any clear way to accomplish what you want with formulas – even trying to combine Array functions with Regex functions, there’s just no way to iterate over values in your lookups, and that’s really what you need to do – you need to iterate over the values in the first lookup and check for their presence in the second lookup.

It seems to me like your only option here is going to be using Automations with a Script action – is that an option for you? Do you use a “Pro” or “Enterprise” subscription?

[Ashley_Hogrebe]

Thank you so much! Yep, that makes total sense.

And yes, we have Pro and super open to using scripting. Admittedly that’s a new venture to me, but if you could point me in the right direction that would be so appreciated.

[Jeremy_Oglesby]

Since the solution will go off the rails from the original question/post title, I’ll PM you to discuss further.