InfoPath Dev

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

The formatting issue is not your fault - our forum software is quite old and only behaves itself on Internet Explorer these days. I'm wondering if instead of populating these promoted fields from a mulitselect using double eval, it might not be better to just promote the repeating field with the merge option - then in a data connection to the library, you'd have a repeating node instead of comma delimited text - and you could check for the existence of a match more easily.... Or, if you need this double eval version for something else in your process, perhaps also promote the selected values set to merge so that you can leverage that for this logic?

Thank you for the reply, Hilary. I have both the comma separated text field and the multi-select field promoted to SharePoint, so we can choose either as the source for the lookups. The question is how to process each value, in turn. So if the multi-select list box (or the comma separated text field version of it) has more than 1 value selected, how to we return the people who like EACH option. EXAMPLE: The multi-select field contains 20 different fruits as options, but only 3 are selected: APPLE, ORANGES, PEARS. How do I set a field in the CURRENT List to all "shopper names" where a field on a DIFFERENT List's shopper record contains APPLES. Then concatenate to that, the shoppers that like ORANGES. Then concatenate to that, the shoppers that like PEARS. The number of fruit selected could be as high as 20 (in theory), so I can't use a different variable for each fruit, as that would be 20 different fields... and next year it might be 30 fruits in the multi-select. It is the sequential processing of the selected values that poses the problem. It's the opposite of CONCAT... I need to parse or decompose a field's values to process each individually. Thanks!!

You don't have to process each value, just check for the presence of it. Here's a sample that may help. Save the file locally, right click to open in design and preview. Select some items in the multiselect, then select something in the dropdown & the cacl'd value will display true or false if you selected a value in the dropdown that you also selected in the multiselect. Edited to add - I'm a little confused by the description with shoppers - maybe you can modify this sample to show me where the issue is.

Great idea. Please see attached. I have inserted a repeating table to simulate the external list (and that needs to be filled-in before you can start testing). I included some commentary at the bottom of the attached form, along with a screen-shot of an example of it not doing what I need. FYI... the attached form in preview mode doesn't function as smoothly as my form in prod (e.g., I had to UNcheck a checkbox for some of the rules to run), but I hope it works well enough to get the point across. Thanks for the help!

See attached. I added a button and some helper fields - multiselects don't always behave with defaults (as you've seen, needing to select & then deselect an option to get your defaults to re-calc) but maybe this will get you a little further. :)

Hilary, you are AMAZING! That was just the nudge I needed to get it! I was able to streamline it once I realized you could change a select "VALUE" (when selecting a multi-select group) to selecting ANY OCCURRENCE OF VALUE or selecting ALL OCCURRENCES OF VALUE... not to mention the "sum" and "count" options! Once I had that piece, I was able to set up a rule to run whenever the multi-select field changed, and update the distribution list accordingly. No button or SetMatch/Match fields needed on the linked list. THANK YOU SO MUCH!

I am so happy to have been able to help - I can't tell you how long it took me to find the "Any" and "All" occurrence dropdowns - that is one well hidden feature (I think a colleague actually had to show me those....). Have a great day!