How to build a string from the corresponding fields of a lookup column (with multiple values enabled)

Hello @isabellamai 

Which platform are you using?
SharePoint Platform →
Forms → 
Workflow → 

Just to confirm, your Form is saved to List A. 
Is the the division field on the form a multiple choice that refer to List 1 Division?

List 1 and List 2 are supporting Lookups List to your primary form list which is List A.

List 2
→ “Division” single Line of Text column 
→ “Email Reps” people column (is multiple?)

It would be great if you could provide some examples of both List. a simple table would suffice

Sorry...I am using Sharepoint 2019., nintex form/workflow 2019.

My form is in List A and it has these fields:

• name (people picker)
• division (look up field to List B division field)
• question

List B has these fields:

• division (text) e.g., marketing, communications, 
• email address (text) - one or more email addresses (if more than one, they are separated by semicolons) 

the form allows the requestor to send their questions to multiple divisions versus submitting a different one for each division

Scenario,

• So if two are selected e.g., marketing, communications in List A
• it will look up List B and pull the email address fields
  • communications;  John@company.com; Jack@company.com
  • marketing: mandy@company.com; mack@company.com; jill@company.com
• Email notification has to be sent to:  John@company.com; Jack@company.com; mandy@company.com; mack@company.com; jill@company.com

currently using the responsive form - but can change it to the classical if needed. Do you have some screen prints of either option or direct me to a conversation where this was already described in detail as I wasn’t able to find anything yet. 

Can you explain the workflow for me...when querying the List B for the email addresses based on the division field, the issue is that when using the query list action, the filter section is the issue when a multi-selection is made...and thus, the query action doesn’t pull data due to it not being a one-to-one match.

Hi @isabellamai 

Form Route

List B → Division (Text), EmailAddress2 (Text). Make sure email address ends with semicolon 

Responsive Form

Division is a List Lookup to ListB - Division. Multiple values.

Emails is a calculated value field.

Formula (partial) for Emails

If(contains(Division, "Marketing"), lookup("ListB", "Division", "Marketing", "EmailAddress2"), "")
→ When Marketing is selected, perform a lookup to ListB, otherwise return empty string “” 

Output

When EmailAddress is a People column,
it return as Account type eg “1;#UserA,2;#User2”

Hi @isabellamai 

Workflow Route

1. Create txt_EmailAddress variable.
2. Retrieve the value of Division. This should be in as a Collection Type.
   Use this value in a For-Each Loop
3. LOOP: Query List for single Division value (eg Marketing)
   Append result to txt_EmailAddress variable.
4. Loop until all the Division collection value has been process.
   The result is a long list of email address

“Can you explain the workflow for me...when querying the List B for the email addresses based on the division field, the issue is that when using the query list action, the filter section is the issue when a multi-selection is made...and thus, the query action doesn’t pull data due to it not being a one-to-one match.”

Nest the query List within a For-Each loop - as you need to perform one Query List for each value in Division.

Thank you Garrett,

List 2 has 62 divisions currently and it may/will continue to grow - and I just realized it is not a people picker field, but a text field with several email addresses separated by semicolons when more than one is referenced. Thinking the form may not be the practical approach as I would have to make sure the customer informs me every time their list is modified.

Hey @isabellamai 

In your case, since there are many divisions and the divisions may grow, you should go with the Workflow Route as it requires little or no maintenance.

The Form Route may be faster to develop and test when the number of divisions is small. but care is need to ensure that all the spelling are correct. 

I also realized - I can modify List A and pull in the additional lookup columns from List B and when doing so, all the email addresses are also pulled in for the selected Divisions:

• name
• division
• division: email address

The division: email address field will show the email address from multiple divisions; however, it also captures the item ID with the email addresses “#number;” e.g., #43;#mandy@company.com; #23;#mark@company.com, etc.  If I can remove those prefixes, I could use this instead. Do you know how I can remove those prefixes? regular expression and split or extract, but it would take several expressions?

Regex

Search for (#\d+;#)

Replace with “” empty string

Explanation

#43;#mandy@company.com; #23;#mark@company.com

Search for → #, zero or more digits, ;, # → #43;# → #23;#

Result → mandy@company.com; mark@company.com

okay - so close...the only list ID I could not get rid of was the single digit list ID - or the first one on the field as it dos not start with a #

4;#mike@company.com...can we alter the search to do #\d+;# || \d;#

Far simpler to add # to the first item in the field

then run the Regex Search and Replace

the list ID is automatic - and so is the # in that lookup field for Division: Email Address field. 

I thought perhaps I added a second regular expression - but I don’t think I have the pattern correct.- can you tell me what is for 4;# - I used d+;# and it didn’t work.

Thank you thank you for your patience.

Its \d+;#

okay, by running two expressions it worked:

• the first one was using this pattern:  #\d*;# and I used a “space” key for the replacement text
• the second one was using this pattern:  d;*# and it removed the initial ID

This gives me my email text field...and thus should work.  Thank you again for your help on this..