Multiline record in Queue Item

Help Activities
, , , , ,
1

Multiline record in Queue Item

Use Case Description

Multiline record into queue. I have invoice data in the excel. single invoice data is in multiple lines. Header row has details about customer, multiple lines items for service and parts. I want to put all these rows for single invoice in Queue item for multi bot execution. Screen of the data is attached and color coded to show the same record.

AS-IS WORKFLOW, TO-BE WORKFLOW

InvoiceData.png
InvoiceData.png1838×485 34.6 KB

Other information about the use case

Industry categories for this use case: Finance, Marketing Sales

Skill level required: Advanced

UiPath Products that were used: UiPath Studio

Other applications that were used: -

Other resources: -

What is the top ROI driver for this use case?: Accelerate growth and operational efficiency

2

@NG_123

You cannot add complex types like datatable directly…for that we serialize them as json array and can add the data to wueue as a string and then deserialize again to datatable in performer

Cheerd

3

Hi @NG_123,

Queue stores the data in the database in the background. So, it is best to structure your data as a JSON object and then convert it to a string and store it in the queue (specific data).

While in the performer, de-serialize the string to a JSON object and use it in your performer wherever data is required.

4

Thanks Anil_G for the suggestion of Json. Can you please give me sample code to create a Json.

5

Here is how to read an Excel file and group rows together into one queue item:

Use your typical methods to read the Excel file into a datatable.

In my example I have an Excel file that contains CSV definitions with one row per CSV column, so I need to group them so I have one row for the definition and a column that contains JSON for all the other rows for the same definition.

Add a column to store the JSON:

image

Then loop through the datatable:

image

Now we are in the loop and for the CurrentRow of data will filter into a new datatable to get all matching rows for CurrentRow(“MAPPING_NAME”) which I think in your case would be Customer Number. I actually need to group them by MAPPING_NAME and BUSINESS_ID so you’ll see that in my filter:

image
image774×572 21.5 KB

Now you have a datatable for the first group of data, so convert that to JSON and add it to the column that was created earlier:

image
Newtonsoft.Json.JsonConvert.SerializeObject(DT_Fields)

Now every row in the datatable has a column (in my example, FIELDS) that has the JSON for the grouped data, and we don’t need those columns we grouped, so remove them with Filter Data Table:

image
image569×557 21.8 KB

Then remove duplicates from the datatable so we have only one row for each MAPPING_NAME/BUSINESS_ID which in your case is Customer ID.

image

(From row In DT_Main
Group row By
k1 = row("BUSINESS_NAME").ToString,
k2 = row("MAPPING_NAME").ToString
Into grp = Group
Select grp(0)).CopyToDataTable

Now that the data is properly grouped in the datatable, use Bulk Add Queue Items to add them to the queue:

image