Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 03, 2019 12:14 PM
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!
May 03, 2019 12:21 PM
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"
)
)
May 03, 2019 12:27 PM
That did it! Thanks!!
May 03, 2019 12:29 PM
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.
May 03, 2019 12:34 PM
Thanks for the explanation Jeremy, the field does contain linked records in order to create a multi-line, multi-product packing list.
Feb 07, 2022 07:44 AM
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
Feb 14, 2023 10:40 PM
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.
Jul 02, 2023 04:45 AM
The gift that keeps giving! Many thanks Jeremy. I needed this for a Linked Records formula.