IF(FIND) Formula Help

[Lane_Scheiblaue]

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!

[Jeremy_Oglesby]

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"
   )
)

[Lane_Scheiblaue]

That did it! Thanks!!

[Jeremy_Oglesby]

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.

[Lane_Scheiblaue]

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

[Shaun_Parfett1]

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

[Tami_Mize]

Shaun -- Thank you for sharing your solution -- You saved the day for me with this! I was able to turn multiple select choices into tags to include within another field, depending on whether they were selected or not. I worked on this for hours and read about every help page on here twice. Granted I really don't know what I'm doing but am always determined to figure it out.

[Helpdesk_softwa]

The gift that keeps giving! Many thanks Jeremy. I needed this for a Linked Records formula.