cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Map multiple columns from sql server to a list

Jump to solution

Hi,

I am trying to find the best way to map a multi column set of records from sql server to a list as par of my workflow. I can return the the record set as a single column of concatenated records and then split and map to the Item List but is this the best way to solve this problem? It seems the collection type will not store more than a single column of data.

Thanks,

John

Labels: (1)
0 Kudos
Reply
5 Replies
Highlighted
Nintex Newbie

Re: Map multiple columns from sql server to a list

Jump to solution

Well, you can switch from "store default result" to "specify column names". This way you could specify which column populates which collection.

execute_sql.png

Reply
Highlighted
Not applicable

Re: Map multiple columns from sql server to a list

Jump to solution

thanks Henning. I have used that property but my problem is that when I want to create a new list item based on these columns. Ideally I would have expected that I could use a For Each block and create a list item for each row returned from the database but I don't see how I can do this in a tidy way. The only way seems to be to concatenate the result set in the my intial query then split each row and create the item in a For Each block. Using your example I need to combine Company A from the CompanyName collection with its corresponding Contact Name for A and use the Create Item block.

0 Kudos
Reply
Highlighted
Not applicable

Re: Map multiple columns from sql server to a list

Jump to solution

I'm guessing that I could use two collection operations for each column returned inside a For Each block.

0 Kudos
Reply
Highlighted
Nintex Newbie

Re: Map multiple columns from sql server to a list

Jump to solution

Kinda - I would create a for-each loop to iterate over one of the properties. In this loop you have to keep track of the current index. Then use the collection-operator to get an item of the other collection using this index. so now you would have two variables holding the current item.

View solution in original post

Reply
Highlighted
Not applicable

Re: Map multiple columns from sql server to a list

Jump to solution

Ok that's great thanks. I have tried that. To summarise what you have said: I used the "Store In" property of the For each loop to hold the values in the one column, while also setting the "Index" property that I use later in the Collection Item block to track the associated value in the other data source column.

0 Kudos
Reply