Run excel (not orchestrator) queue with multiple robots

[oliviamga2]

Hey guys, I have been reading up on the forum about how you can use Orchestrator queues to assign the work queue to multiple bots without causing any duplication to speed up the processing time.

What I want to confirm is whether it’s true that we can only use the Orchestrator queue to do this. Meaning, if we were to run the automation without Orchestrator queues, we would be unable to assign multiple bots to the automation run without causing duplication.

Asking because my project has a requirement to use only Excel queues (no Orchestrator queues). It’s built in REFramework.

Are there any creative solutions/ideas anyone can share?

[Nithinkrishna]

Hey @oliviamga2,

You need to just have a status field in excel to update a row item with inprogess, completed or failed as soon as the item is picked or processed.

This is how even in orchestrator it works. So it should do the trick for you as well.

Hope this helps.

Thanks

[NIVED_NAMBIAR (NIVED N)]

Hi @oliviamga2

In addition to what @Nithinkrishna said

Try this too

Avoid duplication too in excel file.by removing duplicate rows before moving for transactional processing ( ie in init state
duplication removal would be good option)

Regards

Nived N

Happy Automation

[oliviamga2]

Thank you! This is great advice.

I am thinking it would make the most sense to write this value “in process” to the Excel queue row in the GetTransactionData workflow. Do you agree?

Where would I add in the condition to check for whether a row has already been “claimed” by a bot? Would that be also in the GetTransactionData workflow?

[Nithinkrishna]

Yep @oliviamga2,

Exactly the same.

Cheers

[oliviamga2]

Awesome!!

Do I need to include instructions for if the row has already been claimed for processing by a bot (i.e. get the next queue item)? Or will the automation know to proceed to check the next queue item if the one it’s on has been marked as in process?

Right now, my If condition checks whether the value of the queue status column is blank or contains “In Progress” status. If the column is blank (not in progress), then it instructs the automation to assign that row as the transaction item and then sets the status to “In Progress”. But it does not contain any instructions in the else branch. Will this be an issue?

I realize I might have to just play around with it myself, but figured it was worth an ask for learning’s sake.

[Nithinkrishna]

Yes, you need to build logic in such a way that the bot needs to pick the latest new item to be processed. If there are no new items, it should stop the process.

So breaking down,

1. First read excel in the DataTable
2. Find the count of items which has status empty
3. Pick the first from that and mark status as InProgress
4. Write the status back to Excel based on the row index & pass the item to process
5. Repeat

If step 2 has no items, Stop the process else repeat

Just aligning to the Re-Framework flow will help.

Thanks

[oliviamga2]

Many thanks for confirming that!

Playing around with some ideas to accomplish what you describe now. It sounds to me that my best bet might be to extract a list of all data rows that contain “new” status; change the condition to count the number of rows returned and if that count is greater than zero to assign the first row in the list as my transaction item.

The part I am still figuring out and would welcome guidance on is how to reference the correct row index.

I am to detect and count the number of rows set to “New” status using io_TransactionData.AsEnumerable().Where(Function(row) row("Status").ToString.Trim = "New").ToList

But is there a way to return the row index for the first item without redefining the whole data table? I have tried a couple approaches and so far none of them have worked

[Nithinkrishna]

Hey @oliviamga2,

That’s actually a tricky part

Let’s assume you are finding the first element as below.

io_TransactionData.AsEnumerable().Where(Function(row) row(“Status”).ToString.Trim = “New”).ToList().First

Now to find the row index

io_TransactionData.Rows.IndexOf(io_TransactionData.AsEnumerable().Where(Function(row) row(“Status”).ToString.Trim = “New”).ToList().First)

Hope this helps!

You can even pass the variable after saving the first row in it instead of query directly.

Thanks

[oliviamga2]

This is fantastic! Can’t say thank you enough. I have learned so much and these formulas work perfectly. They were the missing link.

In case anyone else needs to reference what I did:

1. I had to ultimately move the step of reading the input Excel file from the Init stage (first run) to the start of each iteration of the GetTransactionData.xaml so that it is refreshed with the most recent status before getting the next transaction. I found that if I left it in the Init stage, the automation did not correctly read once the excel file was updated with “in progress” statuses. (This “refresh” of the data table does slow the overall workflow down a little, but I feel once multiple bots are working on it, it will be worthwhile.)

Next, in the GetTransactionData.xaml, I made the following changes:

2. I have a count of all the data table rows that are marked “New” status. If this count is greater than zero, get the next transaction item; else, set transaction item to nothing and exit the process.

3. Provided at least one data table row is marked with “New” status, use the above formulas to assign the first data table row to the Transaction Item, and write the “in progress” status to the Excel file. Note: I added 2 to the returned row index.

4. Assign the Row index retrieved through the formula in step 3 to an argument that is passed to the Process State and used to update the Transaction Item’s status in the SetTransactionStatus.xaml once it has been processed.

[Nithinkrishna]

Perfect !