Assign Column Content from 'Dataset Loop' in separate variables

Discuss RoboTask here
Post Reply
sarnusch
Posts: 35
Joined: Thu Apr 28, 2022 9:05 am

Assign Column Content from 'Dataset Loop' in separate variables

Post by sarnusch »

Hello,

I am using the step 'Dataset Loop' to query data from a view in a MS SQL Database.
The view has the following structure:

Company - Contact - Country
A - Mr. A - Germany
B - Mr. B - France
C - Mrs. C - Belgium

At the moment I can assign the field values into one variable.
So the first loop will assign 'A,Mr. A,Germany' to my variable when I use query 'select * from view'.

I need the values of each column in a separate variable because I need to calculate with values or compare them.

So today I am using separators between each column and my query looks like 'select A1, Company, A2, Contact, A3, Country, A4 from view'. That makes it possible to get the values I need with the step 'STR Between'.
I think I could also use conversion from comma-text to multi line text and assign each line to a varible but that also seems very cumbersome.

I would like to be able to assign the columns to a variable in the 'Dataset Loop' step.

For example:

Column - Variable
Column 1 - COMPANY
Column 2 - CONTACT
Column 3 - COUNTRY
Column n - ...

Is there currently such a function or is it possible to create it?
Oleg
Site Admin
Posts: 3000
Joined: Thu Jan 01, 1970 1:00 am
Contact:

Re: Assign Column Content from 'Dataset Loop' in separate variables

Post by Oleg »

Suppose you use the SQL expression select * from view
Suppose you set row values to variable ROW
Dataset loop set field values as comma-separated string
so you can use the expressions
{ROW(0)} - 1-st field value of row fields
{ROW(1)} - 2-nd field value
{ROW(2)} - 3-d field value
and so on

Also you can use Set variable action to set each value to separate variable
use {ROW(N)} as variable value and switch ON the option Expand variable value
In this case the action calculates the result and then assigns value to variable
Oleg Yershov
sarnusch
Posts: 35
Joined: Thu Apr 28, 2022 9:05 am

Re: Assign Column Content from 'Dataset Loop' in separate variables

Post by sarnusch »

Hello,
with the solution described, I was able to implement my requirement very well.
Tanks!
Post Reply