Solved

How to populate multiple values from a single Data Lookup control in NWC


Userlevel 2
Badge +5

I was recently asked if there is a way to use a single Data Lookup control to populate multiple controls on a NWC form with data from a SharePoint Online list item. I did some research on the Community and quickly found a post by @EuanGamble demonstrating how to show a value from a Data Lookup control as a label. Inspired by Euan's suggestion, I came up with a workaround to accomplish populating multiple controls.  

 

Note: Support for objects is on the roadmap for NWC. Once Data Lookups can retrieve whole objects that can be stored in variables to be sliced and diced as needed, this workaround will not be necessary. But if you need a solution today, this might work for you.

 

The Scenario:
Let’s say you have a list of office locations in SharePoint Online and you would like to allow your users to select an office from a Data Lookup on a NWC form. When they select an office, you would like to display the associated address information for that office (address, address2, city, state, zip) in separate controls on the form. This is just a basic example, but this could be any scenario where you have a list and you would like to display more than one additional value for a selected item on your form.

 

The List:
I created a SharePoint Online list called Office Locations with the following columns:

Column Name

Type

Title

Single line of text

Address

Single line of text

Address2

Single line of text

City

Single line of text

State

Choice

Zip

Single line of text

AddressCalc

Calculated

In the calculated column, we are going to create a concatenated string consisting of all the values we would like to use in our form. Each value will also be preceded by a 3-digit number representing the length of the value. We’ll use this 3-digit number in our variable formulas in the form.

 

AddresCalc formula: =TEXT(LEN([Address]),"000")&[Address]&TEXT(LEN([Address2]),"000")&[Address2]&TEXT(LEN([City]),"000")&[City]&TEXT(LEN([State]),"000")&[State]&TEXT(LEN([Zip]),"000")&[Zip]


Don’t forget to add some sample data to your list!

 

The Data Source:
In NWC, click on Dashboard at the top and then Data Sources on the left. Create a new Data Source using your SharePoint Online connection. Select the list you just created in SharePoint Online. In the Columns field, select the Title column and the AddressCalc column from the list. For more detailed instructions refer to the help documentation.

 

Design the Form:
Create a new workflow with a Nintex Form start event. Drag a Data lookup control onto the form and select the Data source you created in the previous step. Configure the control with the following settings:

  • Name: Office Location
  • Option label: Title
  • Option value: AddressCalc

Add 5 Text – Short controls to the form and name them the following:

  • Address
  • Address2
  • City
  • State
  • Zip

 

Form Variables:
Create the following form variables in the order listed:

Name

Type

Value

intAddressIndex

Integer

3

intAddressLength

Integer

convertToNumber(substring([Form].[Office Location],0,3))

txtAddress

Text

substring([Form].[Office Location],[Form].[intAddressIndex],[Form].[intAddressLength])

intAddress2Index

Integer

sum(3,[Form].[intAddressIndex],[Form].[intAddressLength])

intAddress2Length

Integer

convertToNumber(substring([Form].[Office Location],[Form].[intAddress2Index]-3,3))

txtAddress2

Text

substring([Form].[Office Location],[Form].[intAddress2Index],[Form].[intAddress2Length])

intCityIndex

Integer

sum(3,[Form].[intAddress2Index],[Form].[intAddress2Length])

intCityLength

Integer

convertToNumber(substring([Form].[Office Location],[Form].[intCityIndex]-3,3))

txtCity

Text

substring([Form].[Office Location],[Form].[intCityIndex],[Form].[intCityLength])

intStateIndex

Integer

sum(3,[Form].[intCityIndex],[Form].[intCityLength])

intStateLength

Integer

convertToNumber(substring([Form].[Office Location],[Form].[intStateIndex]-3,3))

txtState

Text

substring([Form].[Office Location],[Form].[intStateIndex],[Form].[intStateLength])

intZipIndex

Integer

sum(3,[Form].[intStateIndex],[Form].[intStateLength])

intZipLength

Integer

convertToNumber(substring([Form].[Office Location],[Form].[intZipIndex]-3,3))

txtZip

Text

substring([Form].[Office Location],[Form].[intZipIndex],[Form].[intZipLength])

 

Form Rule:
Create a form rule to populate the 5 Text-Short control values with the variables you just created, when the Data Lookup control is selected. Something like this:

If:

Office Location

Is selected

Then:

Address

Value

txtAddress

Address2

Value

txtAddress2

City

Value

txtCity

State

Value

txtState

Zip

Value

txtZip

 

That’s it! Preview your form and test different office locations. Don’t forget to save your workflow!

 

Keep in mind that the text results in the calculated column are limited to 255 characters. So, this solution may not work for every situation. You could try using a multiple lines of text column and populate the concatenated value using a workflow on the list. I haven’t tried that, but I think it would work.

icon

Best answer by butlerj 27 July 2020, 15:52

View original

16 replies

Userlevel 5
Badge +19

This is awesome! Strong work @Nintex_Andrea!

Userlevel 4
Badge +12
Very impressive @Nintex_Andrea.
Cant wait for objects to be available in NWC but really clever use of a calculated column.

Very smart!


 


Thanks, that really helped me 🙂

Badge +9
This is great work 🙂 Thanks for sharing, it helped a lot.
Badge +1

@EuanGamble sent me a link to this solution and it was exactly what I was looking for and works really well.

Userlevel 5
Badge +13

Not a problem, @RichR. Glad I could assist.


 


Cheers,


Euan

Badge +4

Is this still on Nintex's radar to improve this function to make it easier to pull in multiple columns of data information. 

Userlevel 2
Badge +5

Hi @Andrew87 ,


 


Yes! Data Source Variables are on the roadmap and currently slated for initial release in Q3 of 2021. Check out the roadmap to learn more and see what other features are planned for the near future.

Badge +4
Thank you
Badge +4

Has this been released?

Userlevel 5
Badge +13

Hi @Andrew87,


 


This is in active development. If you would like to receive updates, I would recommend voting on the feature below on User Voice.

improve data lookup control with multi-value fields and more – Customer Feedback for Nintex (uservoice.com)


 


Cheers,


Euan

Badge +3

Any updates on the External Data. I can actually see the external data in the form but have no way to get the other values I need out of the collection? No formulas are available. 

Userlevel 6
Badge +12

Hey @jschmidt7 


 


What are you trying to achieve?


You should be able to select the different data points within the object like below:



 


In the forms designer, I am using a label to display the retrieved data based on the dropdown that is filtering the lookup:


 



 


You can also set a control's value to the external data by using a rule to set the value:



 


 


Hope this helps!


 


 

Badge +3

I have multiple records in the datasource. The user selects a value from the dropdown. I can fill one field with the value of the dropdown, no problem. I want to select 2 other values based on the selection. It may not be the First Record in the datasource.  I try your method again and see if I can get to work.

Badge +3
Yeah just tried it and it only brings back the value of the first record.
Badge +3
@jesse_mchargue I was not able to access any data beyond the first record.

Reply