Excel Automation Scenario

I have one excel sheet. Which have multiple columns. I have to read the sheet and put all the details in the web application(D365).
It has one column “Order Identifier” from which you have to create the record. It may have multiple records so you have to put these all data in the application but it will create only one Record Number.
like this:

those all have Order Identifier value 1 so it will create only one order.

Another scenario is like you can’t put these store numbers quantity in the table you have to go one by one using search option and put these quantity in the particular text box. For Example: first, it will search 1010 and put 1 in the text box then it will go to 1030 and put the value 4 in the text box.

Any help how can I do that?

Hi @kalpitmantri !
Sorry I am not sure to understand your question.
→ Are you looking for a way to read the range ? If yes, you should use start cell precising A3 in your case and check add headers.
→ Are you looking for a way to concatenate in one row all the data that have a common order identifier ?
→ Are you looking for a way to type into the elements in your webapp ?

No, I Can read the sheet using the read range with the cell range.

I am looking for how can I crate order who have common order identifier and how can I add the store quantity one by one.

Alright, so you are facing an issue on the webapp D365 to match the robot logic through excel. Do you mind sharing with us a screenshot of an order where you have at least added 2 D365 items with at least 2 store numbers with their stocks so we understand the structure of what you type into ?

I can not share the D365 screenshot as it’s restricted. I am struggling with the logic of how can I filter those record that has common order identifier. I know I can use a filter data table but every time I have to iterate for this to get the common order identifier.

the scenario is like I have added the one D365 Item now I have to add another D365 item as per common order identifier. I am not able to find the proper logic for this and to add the store quantity as well because it goes one by one.

Okay so if my understanding is correct, you want to filter each time for each order identifier without writing 1,2,3 ?
If yes, if you send me the excel file I would be able to suggest a xaml file.
Otherwise, once you have used read range and obtain a DT, create a list (that will contain new order identifiers)
Then use a foreach row, and use an IF statement. The condition would be not list.Contains(row("Order Identifier").toString):

  • In then you use add to collection with collection as list and item as row(“Order Identifier”).toString, followed by an assign activity that will have a copy of DT (by using DT.Copy) then the filter datatable activity with the copy of the DT

If I did not understand, then what about you reproduce a final datatable (in excel) that you want so we understand how you imagine the final DT to not have to loop through it?

I sent you an excel file on messages.

Here is a suggestion of workflow: Iteration_throught_filtered_order_numbers.xaml (15.2 KB)
→ temporary_DT is the datatable that will stock the filtered data for each order identifier
→ At this level, you will be able to catch the wanted elements at each column:

Is it what you are looking for ? Does it work as expected ?

I downloaded the xaml file but some activities are missing. I am only able to see read range activity after that it’s showing missing or invalid activity. I checked the dependencies all are okay. Can you send me that xaml in zip format.
It showing below errors :

It repaired the System.Activities package and able to see xaml file. but it always going in the else part.

Strange, I will come back to you shortly