Solved

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

I
Userlevel 1
Badge +6

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.

icon

Best answer by Garrett 9 May 2023, 18:56

View original

16 replies

Garrett
Userlevel 6
Badge +16

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

I
Userlevel 1
Badge +6

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
  • Email notification has to be sent to:  John@company.com; Jack@company.com; mandy@company.com; mack@company.com; jill@company.com
Garrett
Userlevel 6
Badge +16

Hi @isabellamai 

 

Form Route

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 “mandy@company.com; mack@company.com; jill@company.com”

https://help.nintex.com/en-US/nintex2019/current/sp2019/Forms/Designer/LookupFunction.htm

Then concatenate all lookup results into a final string. Use semicolons the separator.
PS: Which form designer?

 

Workflow Route

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.   

I
Userlevel 1
Badge +6

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. 

 

I
Userlevel 1
Badge +6

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.

 

Garrett
Userlevel 6
Badge +16

 

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”

Garrett
Userlevel 6
Badge +16

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.

I
Userlevel 1
Badge +6

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.

Garrett
Userlevel 6
Badge +16

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
Userlevel 1
Badge +6

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?

Garrett
Userlevel 6
Badge +16

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

I
Userlevel 1
Badge +6

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;#

Garrett
Userlevel 6
Badge +16

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

then run the Regex Search and Replace

I
Userlevel 1
Badge +6

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.

Garrett
Userlevel 6
Badge +16

Its \d+;#

I
Userlevel 1
Badge +6

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..

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings