I need some help in finding a solution to this challenging problem, I have to update some info in SAP from an excel file. The file has different columns which needs to be entered in different fields in the SAP GUI (Marked the headers of these fields in green background), except for few columns which needs to be updated in a table (Marked the headers of these fields with red background).
Another important thing to notice here is that the no of rows for each vendors differ like for the first vendor has 4 rows (stretches from row - 2:5).
Also for the excel sheet snip which i have shared has 5 vendors and so the SAP entry will be made in 5 iterations ( in first iteration the first vendor details will be entered and so on). I have marked all the vendors in 5 different colors to show that at a time in the SAP GUI only one vendor details will be entered.
Attaching these snips for reference -
1 - Read first column from read column activity.
2 - Remove empty rows and store the final result.
Now you have only vendors in collection.
3 - Iterate collection from for each loop
4 - Pass item in Filter Data Table to filter only rows of respective vendor (pass your âdtâ in input and create new data table name âdtFilterâ and pass it to output of filter activity).
5 - When you have new filtered rows in âdtFilterâ then use another foreachrow activity to put respective vendor row in SAP.
Let me know if you have any doubt.
If you donât want to use read column activity then another way is to get only column A in list and iterate one by one.
I have a doubt in the 4th step -
Firstly when i pass item(1st vendor name from collection) to filter data table activity the rows with no supplier name ( from excel sheet pic, row no - 3 to 5 for vendor abc) will be removed which should not happen.
Also if the second vendor also has the same name as the first one then in that case also the filter data table activity will keep 2 records whereas it should be only 1 per iteration.
Kindly suggest if there is some gap in my understanding if not how can we tackle these issues.
The above solution will break if we have 2 consecutive vendors with same name, in that case this solution will assume that both the records are same.
Before updating empty vendors
After updating empty vendors(at run time)
My line of thought was that out of one big data table we have in the beginning, we separate out the chunks and then at each iteration take the next chunk, but i donât know if it is possible.
Please let me know your views and how to solve the above issue.
Their is an another way you can create one array of string and store vendors on run time, if same vendor exist in array then add vendor name appending 1 to it ex - AA_1. In this way you will have different vendors data.
Now get all unique vendors from dt and iterate one by one on it.
Rest things will remain same, to filter data with vendor name and insert only that data to Application.
That is populating the empty Supplier Number column and add a numeric value if the item is repeating.
Can you help me with a workflow as to how now i can assign different fields to variables and a list in case of part number and quantity which can then be uploaded.
Which i donât want because i am missing out unique info from column âPart noâ and âNo of boxesâ.
What i want here after the first pic is to capture certain variables for each Supplier Name as below -
First supplier - abc
Invoice no - 789698
Invoice date - 31/08/2021
Purchase order no - 25878
Schedule agreement no - 98754
No of boxes - 69
Part no - [98758, 85698, 47896, 59321]
Quantity - 987
Vehicle Number - 789
LR Number - 8974
The above values should be captured in the first iteration and uploaded in SAP
subsequently in the second iteration i want the details of second Supplier info (abc_1)
to be uploaded in SAP. The main objective after reaching to the point of the first snip is to upload the data iteratively in the SAP.
I think you did not got the problem correctly, The supplier names are already unique, the issue is how to iterate and update the data into SAP because I have certain columns for multiple line items and some only single line items.
you can provide the workflow by Monday if you want I can attach the excel sheet as well, Till that time let me try from the snip that you have provided.