Fill Reference filed of Orchestrator with same data from Object

[Latif (Robin)]

I have a DT which has different rows .. let assume 2 rows

ID, Name, DateOfBirth, Department
215, Michael, 210982, HR
218, Lianardo, 120475, Finance

Im adding this DT table as Bulkdata to Orchestrator queue and it’s creating 2 Queue Item for me with the details. which is correct.

But I can see my Reference is empty and when queue item failed or Retired than i have to open each item details to read the detail to clone again.

So I want to fill this reference with ID, Name, Department value. Can someone help?

[Parvathy (PS Parvathy)]

Hi @Latif

If you want to add reference to the queue, you have two ways:
WAY 1:
You can add Reference column in the excel like below

User Bulk Add queue Items and pass the datatable dt

WAY2:
Do the below steps
=> Read Range Workbook
Output → dt
=> Use For Each row in Datatable dt
=> Inside the loop, use Add Queue Items activity ad pass like below.

In Item Information pass like below:

In Reference, add the required reference column needed. For Example, in below pic I have added Name as Reference.

Output:

Hope it helps!!

[postwick (Paul Ostwick)]

Create a Reference column in your datatable using the Add Data Column activity.

For Each Row in Data Table

• Assign CurrentRow(“Reference”) = CurrentRow(“ID”).ToString + “,” + CurrentRow(“Name”).ToString + “,” + CurrentRow(“DateOfBirth”).ToString + “,” + CurrentRow(“Department”).ToString

Now when you do the Bulk Add it will automatically use the Reference column as the Reference for each queue item.

[Latif (Robin)]

I know I can add reference through ADD Queue Item But I use BULK ADD Queue Item because sometime it has more then 10 rows and Bulk Queue is easy to use.

[sonaliaggarwal47 (Sonali Aggarwal)]

Hi @Latif

@Parvathy has also shared a way to add reference via Bulk add queue items.

Kindly refer her post above, for bulk items reference, you will be required to create a column named “Reference” in your excel file.

[postwick (Paul Ostwick)]

You don’t add it in the Excel file. You add it in the datatable and then populate it with For Each Row in Data Table.

[Latif (Robin)]

That has helped.
I was not using ForEachRow and thats the reason it was not working for me.

Now using ForEachRow it has solved my issue.. Thanks a lot for the help.

[sonaliaggarwal47 (Sonali Aggarwal)]

Thank you @postwick for sharing your inputs.

Yes it needs to be added to the datatable, now to add to the datatable, there are 2 ways, either you add it directly in the datatable or you add it in the excel file itself which you are reading to save in a datatable as described by @Parvathy.

There can be multiple ways to solve a problem, now what solution one deems as most suited depends on various factors.

Regards
Sonali

[postwick (Paul Ostwick)]

Writing new data to the Excel file then reading it back in is redundant. You’re already reading the Excel file, just add the Reference to the datatable.

[Latif (Robin)]

Just one question more..
I user filter data to filter data from excel and add to datatable only those columns I need.
But one of those columns has a value which i want to modity.

let says it has.. IdentityNr 2578969 .. and I want to change it so that I only take first 4 digit.
How can i change the value of perticular row ?

[postwick (Paul Ostwick)]

For Each Row in Data Table

• Assign CurrentRow(“IdentityNr”) = Left(CurrentRow(“IdentityNr”).ToString,4)

You could also do that assign within the same For Each Row that you’re using to populate the reference field. Basically the idea is to manipulate the data in the datatable to what you want, before Bulk Add.

As far as filtering and removing columns, you can also do that in the datatable after reading the Excel file. Don’t bother filtering and removing columns in the Excel file itself, just read the whole Excel file then manipulate the data in the datatable, remove columns etc. A good way to remove columns is to use the Filter Data Table activity and on the Output Columns tab set it to Keep and then list the columns you want to keep: