Question

Extract Data from Collections in Form Designer

  • 1 February 2024
  • 6 replies
  • 89 views
D
Badge +1

Hi, I am trying to extract specific data from a collection that I have returned from an External Data source but I am struggling to see anyway to see any way of doing this. I can extract the first row object in the collection but the rest is proving tricky.

I am guessing there is no way to use index values in the formula to extract the data from the collection? Unless I am missing something?

i have also tried “Data Sources” on the form, which does populate the data in a dropdown, but again I have now way to add any of the individual values as a variable.

Has anyone come across this before and might have a solution?

Thanks!

1 Attachment

6 replies

C
Badge +3

Can you explain what more about what you are trying to achieve on your form?  I have used the data lookup control on the form and then displayed other data from the same record selected in data look up control in other controls on the form.  It requires two different data source variables for the same external data source; one with the unfiltered data and a second for the record selected in the data lookup control.  I can provide a more detailed example if that is what you are looking to do.

D
Badge +1

Can you explain what more about what you are trying to achieve on your form?  I have used the data lookup control on the form and then displayed other data from the same record selected in data look up control in other controls on the form.  It requires two different data source variables for the same external data source; one with the unfiltered data and a second for the record selected in the data lookup control.  I can provide a more detailed example if that is what you are looking to do.

 

Thanks! I am making a call to smartsheet and returned row data as an external data variable. See below:

Variable Created from External Data

My aim is to display some specific data from the row on the form. I can do this with the First Record pretty easily but can’t figure out way to get any of the other records (cell values). I have tried the formula builder when creating a form variable but it doesn’t seem to have the functionality to select an index from the collection.

The data lookup was another option I tried and I can see all of the values appear in the dropdown - however I don’t want this to be “selectable” and thought I could have it hidden and have some rules to select the value - however, rules seem limited for selecting values in the data look.

Any help or advice is greatly appreciated!

 

Lookup Data on Form

 

C
Badge +3

You need to have some way to trigger the filtering of data from your external data source on the form.  Then you use the value of that filter to create a second data variable that will only bring back a single record that you can use for the other values.

Here's an example using the data lookup control to choose a "Component" from the rows returned from the external data set. The Points/Unit value is returned and used to calculate the Total Points using a rule.

In this form I have two data source variables that point at the same SharePoint list. The first data variable is not filtered and is used to display the options in the data lookup control.  The second data source variable is configured to filter the data based on the value selected in the data lookup.

The configuration for the data lookup control is shown below.  I will use the ID column from the SharePoint list to identify the row I want from the list so that is assigned to the option value and the option label will show the Consumable name value.

In the configuration for the data variable for the record selected, I use the option selected to filter to a single record as shown below.

Because the values in a data lookup control are all "text" datatype, I also had to create a form variable to convert the ID text value to the integer datatype when it was selected.  My form variable is shown below:
ifElse(not(isNullOrEmpty([Form].[Repeating section 1].[Current row].[Component])),convertToNumber([Form].[Repeating section 1].[Current row].[Component]),0)

I hope this provides more explanation.  Rather than using the row index, you create a data variable based on the index column from your external data set. 

B
Userlevel 4
Badge +10

We’ve gone down this road with Smartsheet, and there’s two different solutions we’ve found - neither of which is simple.

 

  1. Build your own custom Xtension. The Smartsheet API let’s you get a specific row and columns from a sheet, so you can make a custom Xtension to accomplish this. Unfortunately, I don’t think this is possible with the built in Smartsheet connect from within a forum. This provides the most flexibility, but also requires some depth of API knowledge and OpenAPI formatting. 
  2. Using the data that your getting in a collection from outside the form, you can do a complicated formula in the form to extract the information you need. In form variable, you should be able to use the action to convert the collection to a string, which will then let you use that string in a Replace action. Since the Replace action uses regular expressions, there’s a lot that you can do with it - you just need to know what you’re doing.  

 

It would be really helpful if NAC had some additional built in Collection operations that could be used within Forms. I believe you can get the first and last item from a collection with the built in actions, but those rarely are what I need. 

 

If you want to give it a vote, this idea may also solve your issue if it’s ever implemented: https://ideas.nintex.com/ideas/CNV-I-104

 

Edit- Forgot to mention, if your just wanting to display the associated values, you may be able to use the built in Smartsheet form controls, if you have the data at that point:

 

D
Badge +1

Thanks @clrowe1124, @bsikes, for the suggestions. I will look to implement the suggestions you provided - I think it is evident that none of these are the most straightforward and there really should be some sort of functionality on the forms to get and use data from a collection - I have upvoted your idea 🤞

I will keep you updated on how this progresses.

C
Badge +3

I totally agree with you.  The older SharePoint version had a “lookup” function did all this before, but due to a lack of external database features in the SharePoint Online version of Nintex Forms, I had to go this route which was way more complicated. I would have never figured it out on my own without assistance from the Nintex Upgrade team, so I have to suggest that Nintex Support might also be able to advise on some options to get what you are looking for if you are still stuck.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings