Adding Row(Data value) to specific column in DataTable, Column names are dynamic

Help Activities
, ,
1

Hi,

AddQueueItems
AddQueueItems1459×5625 315 KB

attaching workflow screenshot for reference.
i have a excel with read range, Sheet1-(SOData) with columns - SO no, Status, Tax, Bill Schedule count

If Billing Schedule count(from above) is greater than zero(for eg count= 2) it reads Sheet2(Bill ScheduleData) - having 2 schedule details for respective SO -
SO no(fix col same number from sheet1), Date1, Amt1, Date2, Amt2 —>Schedule 1
SO no(fix col same number from sheet1), Date1, Amt1, Date2, Amt2 —> Schedule 2
here in above date and amt columns pair count is not defined it can have 2 pair of date or n number of pairs, and both these schedules will be for same SO (as Billing Schedule count was 2)

So my query is - i want to add these Date and Amt columns dynamically and there respective data row values dynamically. So as to add all these items (including Billing schedule(Sheet 2) for respective SO Details (Sheet1)) in single Queue

Short overview of what i did till now -
For each to loop sheet 1(SO)
created temp file (Temp data) to add SOdata (and later to append Billing ScheduleData)
getting billing count for respective SO (from sheet 1)
If count > 0 looping for each to sheet 2
verifying if sheet 2 has number of rows as per billing count in sheet 1
if yes - created 2 counters for rows and columns (as column numbers are like Date1, Amt1)
used add new columns - to tempdata
Add new Row - to temp data

bulk adding to queue - Temp data

2

Hi @Veeha ,

Could you maybe provide us with a Sample Input data to work on and also provide it’s Expected Output, So that we could map the conditions specified and understand the logic to be used.

Noted that you are using Nested For Each Row Activity, we could check also if that could be avoided since there is no dependency of UI operations within it, maybe after a complete understanding by checking the input and it’s required output.

3

Sure, Attaching the source sheet with Dummy data. along with the temp file that needs to be uploaded to queue as a bulk upload for single SO.

so as a part of dispatcher i want to add all the details of single Sales Order (including a billing schedule if it has any in count) to the queue.

for eg in sheet - SO333 has 3 schedule count in “SO details sheet”. so bot should pick up details of billing schedule with all 3 counts in Temp file which then can be bulk uploaded to queue.

Also i observed Bulk upload will not allow null cell to upload, PFA temp file any suggestions on how to cover this yellow highlighted cells, or any other way to do this whole thing (uploading to queue
SourceSheet.xlsx (34.2 KB)
Temp.xlsx (9.9 KB)
)