2 Replies Latest reply on Jun 23, 2017 6:42 AM by Adam Rauh

    If Contains ref'ing data from another field

    Adam Rauh

      I had previously started a thread here w/ a similiar question (see: Countif Contains/Like Question ) but so far just haven't been able to get it to work the way I want in prod so I wanted to try something simpler -

       

      What's the easiest way to show an IF CONTAINS statement, where we use data from another field?

       

      Ex, see below kind of I was going for:

       

         

      UserFirst NameLast NameLaptop NameNamed OK?
      John DoeJohnDoeLat-JdoeOK
      Jane DoeJaneDoeLat-JadoeOK
      Abe LincolnAbeLincolnWLS-bcorkinMisnamed
      George WashingtonGeorgeWashingtonLAT-gwashingtonOK
      Willie MaysWillieMayse7340-3j3j5hMisnamed
      Ozzy OsbourneOzzyOsbournexps-ewrkj322Misnamed
      Jake LeeJakeLeelat-jleeOK
      Arsene WengerArseneWengerlls-awengerOK
      Donald TrumpDonaldTrumplat-dtrimpMisnamed

       

      Basically, the IF/CONTAINS question I would want to ask is if the laptop name contains any of the Last name (or, all of the last name, if easier and wildcards aren't possible....I know then this would cause false positives where say you had a really long last name and trimmed off some of the laptop name to assist...but willing to accept those name...we're really only caring about the blatant misnamed laptops).

       

      Any ideas?

        • 1. Re: If Contains ref'ing data from another field
          Deepak Rai

          Hi Adam,

          Please check the Screenshot and attached.

          Hope it Helps!!!

          Let me know.

          Thanks

          Deepak

           

          • 2. Re: If Contains ref'ing data from another field
            Adam Rauh

            This certainly seems to be what I'm looking for, however, in production everything is coming back 'False'. 


            Full disclosure, obviously w/ the test data the field names are different, but I cc'd all the code identically (so far that I can tell), and just replaced it w/ the actual prod fields:

             

            (So from the above example,

             

            Laptop Name ='s Name (Cmdb Ci Computer)

            Last Name ='s [Dv Assigned To (Cmdb Ci Computer) - Last Name]

             

            // Calculation 1

            IF CONTAINS([Name (Cmdb Ci Computer)], [Dv Assigned To (Cmdb Ci Computer) - Last Name]) THEN "Yes"

            ELSE "No"

            END

             

             

            // Calculation2

            LEN([Dv Assigned To (Cmdb Ci Computer) - Last Name])

             

             

            // Calculation3

            RIGHT([Name (Cmdb Ci Computer)], [Calculation2])

             

             

            // Calculation4

            LOWER([Dv Assigned To (Cmdb Ci Computer) - Last Name])=[Calculation3]

             

            Using the above, I get the below (I've hidden the first names and beginning DNS names, just for privacy, but you get the gist):

            The only difference that I can see (and maybe I'm missing something), is that in the prod example, I had to separate out the last name via a custom split, so it's actually a calculation:

            via:

            TRIM( SPLIT( [Dv Assigned To (Cmdb Ci Computer)], " ", 2) )

             

            Outside of that, everything else looks the same to me.  Any ideas?