Use a field value to search in another string and output an inclusion

[Bigmonki]

Hi All,

I am seeking assistance in string search, I found a post on here that used 'Getword' but I couldn't make it work. My requirement:

I want to search the column 'Include entries' for the value that is in the 'User ID' field, when it finds a matching string, I would like to add a column then simply says "INC" so I can carry this particular record forward in the workflow.

User ID	Include entries
ims	abc321,BBB321
ims	abc321,BBB321
ims	ims,abc321,BBB321
ims	ims,abc321,BBB321

I did try the following formula but it only seemed to work if I have an actual matching value, as soon as I expanded the string to include more than one entry in the Include entries field, I got empty responses.

IF Getword([Include entries],0) = [User ID] THEN "INC" ELSEIF
GetWord([Include entries], CountWords([Include entries])-1) = [User ID] THEN "INC"
ELSE "" endif

Can anyone offer a solution, I would like to try and avoid breaking the 'Include entries' down in to separate columns as there can be up to 100 records entered!

[Luke_C]

Hi @Bigmonki 

The contains function should do the trick for this unless I'm misunderstanding

if contains([Include entries],[User ID]) then 'INC' else null() endif

[L_T]

Hi, have you tried to use the contains function?

IF contains([Include entries], [User ID]) THEN "INC" ELSE Null() ENDIF

It seems to work with your examples.

[apathetichell · Accepted solution]

Your issue with the getword strategy is that your [Includes fields] isn't broken up into separate words. You can match via a left([include entries],3) - if it's always the first 3 characters.

If you want to use getword you can add a formula like this to the formula tool and include it prior to (higher up) than your current formula:

replace([Include items],",",", ")

This will allow your getwords formula to work.  note - you could put use blankspace in lieu of comma.

[Bigmonki]

Hi,

Didn't realize I could use field as a target, thanks for that. It has kind of worked, but as it is using contain, I am getting false positives where there are User ID's that have ims as part of the string

[Bigmonki]

@Luke_C  @L_T  many thanks for the advice, at least now I understand I can use a field as a target for the data. Should have been obvious, but sometimes you need that outside view.

@apathetichell as soon as I removed the comma it worked perfectly, the comma space didn't provide me with any response, as soon as I dispensed with the comma it worked.

Problem solved and I can't thank you all enough for your assistance.