Dynamic text to Collection name

[curroeva]

Hi!

I need some help with a collection with more than 1 dimension. I need to sort into that collection, but the values I need to get are dynamically obtained by a JSON (I used the JSON for collection action).

The result is a collection that has the next columns:

I need to set the dynamic name in the collection to get the elements of every item of the JSON file.

For example: [L_Coll_DatosInvgate.requests.<DYNAMIC VALUE>.custom_fields.1234]

Another idea is to get the collection [L_Coll_DatosInvgate.requests] and transpose it, but the problem when I try to transpose that collection is that the vbo of collection manipulation throws the error message: 

ERROR: Internal : Could not execute code stage because exception thrown by code stage: Conversion from type 'DataTable' to type 'String' is not valid.

Thanks for your help!

[devneetmohanty07]

Hi @curroeva ,

We faced a similar issue quite a while back when we had to dynamically pass a column name into a collection. Firstly, we cannot set any column name dynamically within the expression interfaces of Blue Prism studio, be it any calculation, decision or properties of action stages which means you need to know this value during the design time itself and it cannot be set dynamically during run time.

Also, we do have the Read Collection Field and Read Collection Field By Indices actions within Collection Manipulation business object which do work for scenarios involved with dynamic columns during runtime but they are limited to columns with type as Text, Number, Date, Boolean not collection.

For this reason, we created a custom action called Read Nested Collection Field that would work in similar fashion to that of the Read Collection Field action but works typically with collection-based field types and returns us the nested collection in a separate collection stage.

This action was created within Collection Manipulation business object and takes in three parameters namely,

1. Parent Collection (Collection Type)
2. Field Name (Text Type)
3. Row Index (Number Type)

and it will return us the Nested Collection as an output of Collection Type.

Once we have setup the Start and End stages with the relevant Input and Output arguments, we place these values within the code stage as well:

Now we can add the following code in the Code tab of the Code Stage:

Dim tempCollection As DataTable = Nothing

If RowIndex >=0 And RowIndex < ParentCollection.Rows.Count Then
	If ParentCollection.Columns.Contains(FieldName) Then
		If (TypeOf ParentCollection.Rows(RowIndex).Item(FieldName) Is DataTable) Then
			tempCollection = ParentCollection.Rows(RowIndex).Item(FieldName)
		Else
			Throw New Exception("Field name is not of collection type")
		End If
	Else
		Throw New Exception("Invalid field name has been provided")
	End If
Else
	Throw New Exception("Invalid row index has been provided")	
End If

NestedCollection = tempCollection

With this if we supply any field name and the row index we can get the collection type value from a parent collection as an output collection data item.

Remember to publish the action and save the business object.

Now for your use case, in the Process Studio, I took a Test Collection data item which has somewhat similar structure as what you have provided:

Now I can first have the Requests collection setup in a separate collection data item as part of a simple calculation stage:

Once the Requests collection has been setup, we will use Get Collection Fields action to retrieve all the collection field names from Requests collection that we just setup:

Now, since we have all the dynamic collection field values, we can loop through the Collection Fields collection data item which got populated in the earlier step and use our newly created Read Nested Collection Field action while passing the Parent Collection parameter as Requests collection, Row Index parameter as 0 since we always have a single row in Requests collection and Field Name parameter as the dynamic field name value within Collection Fields data item as part of the current iteration:

The value of the nested collection can then be stored within the Current Dynamic Collection data item which will have the values for the current dynamic value:

Now you can repeat the step for static values as part of setting them using a calculation stage and if you again have any dynamic structure in between just use a combination of Get Collection Fields -> Loop stage -> Read Nested Collection Field action together as I showed above. This way you can iterate through the entire nested structure.