Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

IF(FIND) Formula Help

Topic Labels: Formulas
5625 5
cancel
Showing results for 
Search instead for 
Did you mean: 

I am looking for help with an IF(FIND) formula (I think that’s what I need it to be). Here’s the result I’m looking for.

If {Order Line Items} contains “8009586” display 2.5 FT Core Display, If {Order Line Items} contains “8009587” display 5 FT Core Display, if neither display None.

Here’s what I have written but it’s not working: IF(FIND(“8009586”, {Order Line Items}), ‘2.5 FT Core Display’, IF(FIND(“8009567”, {Order Line Items}), ‘5 FT Core Display’), ‘None’))

I’ve created a work-around by creating this formula: IF(FIND(“8009586”, {Order Line Items}), ‘2.5 FT Core Display’, ‘5 FT Core Display’). This will show the first part and defaults to the 5 FT Core Display if the first part number is not there. It works but could have holes if neither parts are actually listed.

Any help on refining this would be appreciated!

5 Replies 5

Try adding &"" to the end of each reference to {Order Line Items}:

IF(
   FIND(
      "8009586",
      {Order Line Items}&""
   ),
   "2.5 FT Core Display",
   IF(
      FIND(
         "8009567",
         {Order Line Items}&""
      ),
      "5 FT Core Display",
      "None"
   )
)

I think the issue was just that your {Order Line Items} field contains linked records (I’m assuming), and so it’s outputting an array, rather than a string. The FIND() function doesn’t know how to find a string in an array, so it was failing to return anything. By adding &"" to the end of your references, you are coercing the array that is output by {Order Line Items} into behaving as a string instead of an array, because you are concatenating an empty string onto the end of it.

Thanks for the explanation Jeremy, the field does contain linked records in order to create a multi-line, multi-product packing list.

Jeremy, you have know idea how much this post of yours helped me! It’s the gift that keeps giving. I am trying to combine multiple true IF statement results in a formula output - basically auto-creating an email to staff with Powershell formulas included, based on User names and Group adds/deletions. I have literally spent many hours trying to get a solution and then came upon yours. My code is rough and ready and has not been formatted yet but it is only working thanks to you! Many, many thanks.

IF(FIND("ADD 1 user to 1 or more AD groups",{What do you want to do?}&""),"Add-ADPrincipalGroupMembership -Identity"&" "&{Initials}&" "&"-MemberOf"&" "&{AD Groups ADD - Quotes},'')
& "\n" 
&IF(FIND("ADD 1 or more users to 1 AD group",{What do you want to do?}&""),
"Add-ADGroupMember -Identity"&" "&{AD Groups ADD - Quotes}&" "&"-MemberOf"&" "&{Initials},'')
& "\n" 
&IF(FIND("REMOVE 1 user from 1 or more AD groups",{What do you want to do?}&""),"Remove-ADPrincipalGroupMembership -Identity"&" "&{Initials}&" "&"-MemberOf"&" "&{AD Groups REMOVE - Quotes},'')
& "\n" 
&IF(FIND("REMOVE user(s) from 1 AD group",{What do you want to do?}&""),
"Remove-ADGroupMember -Identity"&" "&{AD Groups REMOVE - Quotes}&" "&"-Member"&" "&{Initials},'')

Cheers
Shaun