I have two lists:
- List 1: Help Sharepoint list with several fields, to include a lookup field, “Division” (with multiple values enabled)
- List 2: Division Listing with corresponding email addresses per division
If a form is submitted with more than one division, an email needs to go to all associated emails with the selected divisions
I know a query action, collection action, and build string action need to be added to my workflow but am at a loss. Any help would greatly be appreciated.
Best answer by GarrettView original
Which platform are you using?
SharePoint Platform →
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.
→ “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:
List B has these fields:
the form allows the requestor to send their questions to multiple divisions versus submitting a different one for each division
Depending on the number of division in List B,
You can retrieve the the Email addresses from List B from the Form using the Lookup function either through a Calculated Field control or a Form variable.
e.g. lookup(“listB”, “Division”, “marketing”, “Email_Address”)
→ Query List B, search column Division for the value “marketing” and return the value from the “Email_Address” field which is “email@example.com; firstname.lastname@example.org; email@example.com”
Then concatenate all lookup results into a final string. Use semicolons the separator.
PS: Which form designer?
Create a loop to retrieve the values of the selected division.
Use the Query List action to query List B, Division column for the value of the Loop
Append the resulting string into a Text variable. Use semicolons the separator.
My preference is the Form Route. Its faster to build and a lot quicker to test.
If done correctly, you shall see text/calculated field with all the emails.
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.
List B → Division (Text), EmailAddress2 (Text). Make sure email address ends with semicolon
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 “”
When EmailAddress is a People column,
it return as Account type eg “1;#UserA,2;#User2”
Use this value in a For-Each Loop
Append result to txt_EmailAddress variable.
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.
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:
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;#firstname.lastname@example.org; #23;#email@example.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?
Search for (#\d+;#)
Replace with “” empty string
Search for → #, zero or more digits, ;, # → #43;# → #23;#
Result → firstname.lastname@example.org; email@example.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;#firstname.lastname@example.org...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.
okay, by running two expressions it worked:
This gives me my email text field...and thus should work. Thank you again for your help on this..