Skip to main content

IF(FIND) Formula Help

  • May 3, 2019
  • 7 replies
  • 227 views
L
Forum|alt.badge.img+4

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!

7 replies

J
Forum|alt.badge.img+18

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"
   )
)
    L
    Forum|alt.badge.img+4
    • Lane_Scheiblaue
    • Author
    • Participating Frequently
    • May 3, 2019

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

    That did it! Thanks!!

      J
      Forum|alt.badge.img+18

      That did it! Thanks!!


      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.

        L
        Forum|alt.badge.img+4
        • Lane_Scheiblaue
        • Author
        • Participating Frequently
        • May 3, 2019

        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.

        S
        Forum|alt.badge.img
        • Shaun_Parfett1
        • New Participant
        • February 7, 2022

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

        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

          T
          Forum|alt.badge.img+2
          • Tami_Mize
          • New Participant
          • February 15, 2023

          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


          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.

          H
          Forum|alt.badge.img+3
          • Helpdesk_softwa
          • Participating Frequently
          • July 2, 2023

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

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

          Terms & ConditionsAccessibility statement