topic RE: read cell from collection-column as index in Product Forum

read cell from collection-column as index

AviadBen_Zaquen — Thu, 18 Nov 2021 20:00:00 GMT

I'm trying to read cell value from a collection. My code runs on Agent field loop, and all I need is to extract horizontally fields. So if the loop is now on Agent A I need to extract the department field. Seems like utility collection manipulation has only Read Collection Field action which works vertically (with row index)...which doesn't really help as I need an action that feeds column name (department) as an index and field name as Agent (just sort of Vlookup)

	ID	Age	department
Agent A	11	30	sales
Agent B	22	25	analytics
Agent C	33	50	marketing
Agent D	44	45	back office

many thanks

------------------------------
Aviad Ben Zaquen
------------------------------

RE: read cell from collection-column as index

AvinashRamkumar — Thu, 18 Nov 2021 20:57:00 GMT

Hi Aviad,

If you are looping through the collection, use [Collection.ColumnName] to get the corresponding value.
In your example when the the loop is in Agent A, use [Agent.department] in your expression/decision stage to get the corresponding department value.

Example:

Collection:

------------------------------
Avinash Ramkumar
Intelligent Automation Tech Lead
ASB Bank Limited
Auckland
------------------------------

RE: read cell from collection-column as index

devneetmohanty07 — Thu, 18 Nov 2021 22:59:00 GMT

Hi Aviad,

If you do not have the field name available with you, you can perhaps extend the Collection Manipulation utility VBO and add a new action there. Set three input parameters along with the associated data items to hold the values, namely Input_Collection, RowIndex and ColumnIndex. Also, use assign a data item as "Value Read" and associate it with a data item and set it as Output Parameter.

Use a code stage with the inputs as Coll. RowIndex and ColumnIndex and map the data items. Also, set the output as Value_Read and map the data item as well with the below code in place:

If RowIndex < 0 OrElse Coll.Rows.Count <= RowIndex Then
Throw New ApplicationException("The supplied row index falls outside of the valid range.")
End If

If ColumnIndex < 0 OrElse Coll.Columns.Count <= RowIndex Then
Throw New ApplicationException("The supplied column index falls outside of the valid range.")
End If

Dim col As DataColumn = Coll.Columns(CInt(ColumnIndex))
Dim val As String = Coll.Rows(RowIndex)(CInt(ColumnIndex))

Select Case True
Case IsDateColumn(col)
Value_Read = CDate(val).ToString("d")
Case IsTimeColumn(col)
Value_Read = CDate(val).ToString("T")
Case IsDateTimeColumn(col)
Value_Read = CDate(val).ToString("G")
Case Else
Value_Read = CStr(val)
End Select
Workflow:

Code Stage Properties:

Testing Results:

Once tested do clear any initial values set and publish the action and save the VBO to use it from you process studio.

------------------------------
Regards,
Devneet Mohanty
Intelligent Automation Consultant
Blueprism 6x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com
------------------------------