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.
