My apologies in advance for the formatting... I can't keep the line breaks in...
I need a codeless solution (i.e., rules-based, tho they can be complex) to the problem I'm having.
As a very simplified example of my challenge, suppose I have two lists that contain a CSV (comma separated values) plain text field, and the first table also has a name field. e.g.:
LIST1
**Name1: Steve
**Field1: apples, oranges, pickles, tomatoes
LIST2
**Field2: oranges, lettuce
My InfoPath form is created for List2 and has a data connection to List1 to bring in the needed fields. I need to return Name1 (“Steve”) if Field1 contains any of the values in Field2.
If Field2 only had one value, then it would be a simple query like “Set Distro to Name1", using a filter like "Field1 CONTAINS Field2”.
The problem is that Field2 contains multiple values and some of those values may not be in Field1, or may be in a different order.
I need to return the Name1 field any time at least one of the values in Field2 is found in Field1.
Bonus information:
* The items in both Field1 and Field2 may change frequently
* Field1 could contain 15 or more values, and Field2 could easily contain 5 or more values
* We can add new fields to either/both LIST1 or LIST2 if needed
* We can use SharePoint designer if needed, but I have found InfoPath processes code much faster than the workflows (which are already rather large)
* Field1 and Field2 are actually populated from a multi-select text box using "substring(eval(eval(Value[. != ""], 'concat(",", .)'), ".."), 2)"
* I have tried using something like the nested evals formulas (above) to check each value in Field2. e.g., Field1 CONTAINS substring(eval(eval(Value[. != ""], 'concat(",", .)'), ".."), 2) but that didn't work
* I have thought of other possible approaches, but they all hinge on being able to walk thru Field2 one value at a time... which is my core problem