Cross referencing rows written from one spreadsheet to another

[Tonygets]

Hi,

I am using AA 5.5.1 Enterprise.

Background to this automation:

My automation is designed to extract orders out of a Master Orders spreadsheet and create a new Purchase Orders spreadsheet with values from the Master spreadsheet written in the correct format required by our supplier. In the Purchase Order spreadsheet we are writing the ‘purchase order number’ value in Column A starting at row 2. We need to also write this ‘purchase order number’ value in Column C of the Master spreadsheet for each relevant row that was written across. (So that we can identify what has been ordered and cross reference to invoices).

The step I need help with is:

Once the data has been written from the Master Orders (session: Default) to the Purchase Order file (session: session1) I need to then write each purchase order number back from session 1 into the Default session for the rows that were written into the session 1 AND SKIP other rows that are not relevant.

This can be achieved by matching the common value contained in ‘OrderNumber’ and ‘ExtOrderId’ (Column E in session Default and Column D in session1) between the two sessions AND also ensuring the value under Column H ‘Vendor’ in session: Default (for the relevant row) contains a predefined value (e.g fra). This last step is because an OrderNumber in the session: Default may contain different products from different Vendors that are not relevant to the current Purchase Order.

I have attached a sample of the Master Orders spreadsheet and the Purchase Orders spreadsheet and the code that I have written so far.

Please can you make amendments to my code to achieve the above?

Much appreciated,

Tony

[forumsupport]

Hello,

We have amended the task as mentioned. Please copy it under location, 'C:\...\My Documents\Automation Anywhere\Automation Anywhere\My Tasks' folder.

Please let us know how it goes.

Hope that helps.

[Tonygets]

Thanks for that. It works perfectly.

Just a quick question. I notice that you used the $counter$ variable and then assigned it to $rowcount$. Can you please just explain a bit further about how $counter$ should be used and how it works?

Thanks and regards.

[forumsupport]

Hello,

$Counter$ returns the current count value in loop. For e.g. in first iteration of loop the $counter$ value would be 1, in second iteration it would be 2 and so on. You can directly use $Counter$ instead of $RowCount$. It is just to make the code understandable we assigned $Counter$ to user variable, $RowCount$, which indicated the $RowCount$ holds the current row number in loop.

Hope that helps.