Loop Through Columns in Data Collection (without using column name)

[smithmrk]

I'm new to Blue Prism and I'm curious about something...
Is there a way to loop through each row in a Data Collection and then loop through each of the columns in that row without using the column name?

For example:
You have loops for going through the data collection, but then you have to look at each field using the field name.
Like [Data.ProductCode]<>"" where you would hard code the Product Code in a decision box to determine if the column ProductCode was empty or not.
In this case you would need a decision box for each column and if you have a data collection with a lot of columns this could be come quite large adding all those decision boxes.

Can you loop through the columns using like an index: Data.Item(index)<>""
That way you can loop through each column to determine if the data is missing or not without having to add a decision box for each column.

Thanks,
Mark

 

[VJR]

Hi smithmrk,

There may not be a direct way to use indexes for collection fields. But you can use a workaround to loop through the field names by using the 'Get Collection Fields' action.

This diagram has 3 columns in Coll1 and so the Collection Fields has 3 rows.

Initial Counter is 1 and at the end of process is 3 which is used to iterate through each row of the main collection (which has 3 rows).







The 'Value Read' data item will return the value of the Main collection for that row which can then be used in the Decision stage.

 

[smithmrk]

Thanks VJR for the screen shots!
I'm still having a hard time understanding...so I attached my own set of screen shots in a PDF for you attached.

I only have one row in my collection...because what I'm trying to do is check that every column in that row as a value and if not throw an exception. So I want the Queue Item with the one row of data to get past to a page that will do the column validation to ensure every column in that row as a value if not error. So I want to be able to just loop through the columns in that row checking to see if one and which one is empty.

It is errors out at "Read Collection Field"
Error = Internal: Field "Field Name" is not defined in the collection.

If you need further screen shots, please let me know.

Thanks,
Mark

 

[smithmrk]

UPDATE
OK, I figured out how to loop through the columns...now I have another question / issue.

Once I found the field that is missing data (empty field)...how can I get the Column Name for that field?
The "Value Read" shows me the value that is empty / missing, but now I want the Column Name for that field...so I can put that in the exception message telling the operator (queue) that there was an exception with Column "XYZ" missing data.

Thanks,
Mark

 

[VJR]

Hi smithmrk,

Once your diagram is in the 2nd loop (Loop Collection Fields in the first diagram I posted above), anywhere you mention in the format as [CollectionName.FieldName] it will give you the column name which you can also store in a data item or directly use in your Exception message.
Here CollectionName is the name of your collection containing the column names and FieldName is the column name of that collection.
In this diagram it will be [Collection Fields.Field Name] as you can see in the 2nd parameter of the last screenshot.
Does that make it clear?

 

[smithmrk]

Thanks VJR!!!
I figured it out and I appreciate your help.
I attached a few screen shots for you to see what I finally ended up doing with your advice.
Works great and I'm hoping I can use this page for other field validations which is why I tried to make it as generic as possible.

Mark

 

[VJR]

Hi Mark,

I have gone through the PDF. I am trying to figure where is the original collection that contains all the data (the Coll1 in my diagram). Is it on some other page and you have made it global in order to make the page generic? If everything is working fine for you then no need to make any changes.

 

[smithmrk]

Yes, the data is "Global" from another page.
Thanks again for the advice and tips.

Mark

 

[PramilaEmani]

Hi smithmrk,

There may not be a direct way to use indexes for collection fields. But you can use a workaround to loop through the field names by using the 'Get Collection Fields' action.

This diagram has 3 columns in Coll1 and so the Collection Fields has 3 rows.
View attachment 822
Initial Counter is 1 and at the end of process is 3 which is used to iterate through each row of the main collection (which has 3 rows).

View attachment 823

View attachment 824

View attachment 821

The 'Value Read' data item will return the value of the Main collection for that row which can then be used in the Decision stage.

Thank you so much for the solution you provided. I have been struggling to get this part of my automation fixed for almost 10 hrs and now I implemented yours and it works like a charm.

 

[bharath009]

Hi smithmrk,

There may not be a direct way to use indexes for collection fields. But you can use a workaround to loop through the field names by using the 'Get Collection Fields' action.

This diagram has 3 columns in Coll1 and so the Collection Fields has 3 rows.
View attachment 822
Initial Counter is 1 and at the end of process is 3 which is used to iterate through each row of the main collection (which has 3 rows).

View attachment 823

View attachment 824

View attachment 821

The 'Value Read' data item will return the value of the Main collection for that row which can then be used in the Decision stage.

Hi smithmrk
For 'Get Collection Fields' action. are you using any existing collection inputs. Can you explain this?

 

[cyrilgebara]

Thanks VJR!!!
I figured it out and I appreciate your help.
I attached a few screen shots for you to see what I finally ended up doing with your advice.
Works great and I'm hoping I can use this page for other field validations which is why I tried to make it as generic as possible.

Mark

Hello smithmrk,
Is it possible to forward your BluePrism process for the missing values please?

 

[smithmrk]

Hi smithmrk
For 'Get Collection Fields' action. are you using any existing collection inputs. Can you explain this?

Yes the collection input is from a Global Collection.

 

[smithmrk]

Hello smithmrk,
Is it possible to forward your BluePrism process for the missing values please?

Not sure what you are asking here...if the column field has missing data I'm just reporting it as an exception.

 

[ShadowChaser]

Elevate your dating game with the top site for hassle-free connections.
Legitimate Girls
Optimal Сasual Dating