Queue Items Solution Design

Help Activities
, ,
1

Hey people,
Hope everyone is having a blast automating! :slight_smile:

I have a dilemma and have seen others struggling with this so I’m looking for some direction in this matter.
Lets say we are receiving multiple emails each containing an excel file with hundreds of rows of data. We check every day for new emails, if any are present, we need to download the excel, process the data and send back an updated excel file to the same email. Simple process, but how would it be best practice involving a dispatcher/performer solution in this sense?

My thoughts:
Idea 1: Dispatcher creates a queue item for each new email containing the email address and the path to where the dispatcher downloaded the excel. The performer will take each row from that excel and process it, and send it back to the email address captured in the queue item.
Problem: If processing one row takes 5 minutes, processing 100 rows will clog the robot for a long time and there is higher risk to break the process in the for each datarow loop, which goes against the dispatcher/performer model.

Idea 2: Dispatcher downloads the excel and for each datarow, creates a queue item. The dispatcher processes each transaction item.
Problem: How exactly do we handle sending the complete processed excel data to corresponding email, if we have more than one emails, each with it’s own excel data added as transaction items in the queue.

I know it’s a long read but I appreciate your input because it’s been bugging me for a few days now

2

you can use IDEA 2

in dispatcher process. apart from passing the datarow item & email address, also pass in

  1. row number of current datarow - use this xaml
    Append Row Number Column To DataTable.xaml (7.7 KB)

  2. total rows of the datatable in current excel - this would be the same across all queue items for the same excel

  3. excel path - in dispatcher process, excel should be copied to shared drive for performer to access, the excel path (in shared drive) will be added to queue item

performer process

  1. after processing current row, update excel (using excel shared point path)
  2. Then compare row number of current datarow against total Rows of datatable in current excel!
  • IF current row number < total Rows, means excel not completely processed yet
  • IF current row number = total Rows, means this excel is finished processing and we can send an email with new completed excel
2 Likes
3

or you can also do this (use dictionary to maintain # rows processed for each excel instead of passing row number to queue item)

  1. create string asset in orchestrator / shared folder called jsonString
  2. performer process:
  • after processing row
try to convert jsonString to dictionary, 
if it fails 
    create a dictionary 
    assign dictionary(excel file path) = 1 jsonString
if it succeeds, then  
    convert  this string to dictionary and 
    check if dictionary contains current excel path as key. 
        If contains
             assign dictionary(excel file path) = dictionary(excel file path) + 1
        else
             assign dictionary(excel file path) = 1
and write updated dictionary to jsonString and write jsonString to orchestrator asset / txt file in shared drive

Compare dictionary(excel file path) to totalRowsOfDatatable, if equal then it means excel is finished processing and we can send excel back to email

use this to convert dictionary to json string
jsonString = NewtonSoft.Json.JsonConvert.SerializeObject(dictionary)

use this to convert json string back to dictionary
dictionary = Newtonsoft.Json.JsonConvert.DeserializeObject(Of Dictionary(Of String, Integer))(jsonString)

1 Like
4

Thanks for your input, I marked first suggestion as a possible solution and will try to implement it in my next process.
The second one should work too but I was asked to use just activities as much as I can and not scripting. But I like the dictionary path too.
Again, thanks!

1 Like
5

thanks,

the reason i added a second solution with dictionary was in case the performer doesnt process the rows in the correct order for some reason. e.g. it processes the queue item where rowNumber = last row. But i dont think that will happen

2 Likes
6

In case if any exception occurred of any datarow how to overcome with is problem

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.