For Each in Data Table & For Each

Help Studio
, ,
1

Hello all,

I have been developing a bot to collect the raw material code from an Excel File and go through SAP system to check which homologated vendors we have. So on this FOR EACH ROW I will go through all raw materials code and extract the homologated vendors.
Let’s suppose I have ONE spreadsheet with 10 raw materials. Then on this looping I will do code by code and extracting the homologated vendors!

image

Therefore let’s suppose my Row(0) is the raw material code 100. After running 10 times, thw Row(0) is 1000 now.

Then after having 10 TXT FILES with homologated vendors, I created a FOR EACH looping to go through each TXT file and store it on the data table…

image
image1085×632 18.7 KB

So I will access EACH TXT FILE and stored it momentarily on dtInforecordVendors. After the looping, I need to connect the RM CODE on Row(0) on the database … How I can do that? …

2

@Ruy_Codo

So are you talking about connecting to a database?

What you did looks good…but can you explain what you need

Cheers

3

@Anil_G Hi,

  1. On my first FOR EACH in Data Table, I will go through the database1.
    On this database, I will collect all vendors linked with the Raw Material. So let’s suppose I am talking about the Raw Material Code 100.

Raw Material: 100
Vendors: Supply A, Supply B, Supply C, Supply D.

Raw Material: 200
Vendors: Supply E, Supply F, Supply G.

So for the code 100 I have 04 suppliers and for the code 200 I have 03 suppliers. I stored this information on TXT format due to transaction on SAP not being compatible to work in another way.
Therefore my first looping gathers ALL suppliers linked with the Raw Material code and each code will be stored in one TXT file. Hence I have on this case TWO TXT files.

  1. Then, on my second looping I will open EACH TXT file and work with this data.

My doubt: How I will consolidate both data table into only one? Because for example, if the data table is: RAW MATERIAL CODE, VENDOR CODE

Rows(0) = Raw Material from the first FOR EACH;
strVendorCode = Vendor Code from the second FOR EACH;

I can’t write Rows(0), strVendorCode because otherwise my Rows(0) will always store the Raw Material Code 200… As it was the last one to go through the looping. How can I link correctly RAW MATERIAL CODE and SUPPLIERS?

4

I would recommend to avoid the intermediate TXT files and use following approach:

table_material_vendor = new datatable
for_each MATERIAL
  collect all vendors
  for_each collected VENDOR 
    add row to table_material_vendor
  end for_each
end for_each

Cheers

5

@Ruy_Codo

Instead of two loop to make it…do it like this…this will group the data based on vendor and you can loop through each group so that when you work you know which group it is and the corresponding data all together

Dt.AsEnumerable.GroupBy(function(x) x("Raw code").ToString).ToDictionary(function(x) x.Key,function(y) y.CopyToDataTable)

Use this in the in argument of loop and if you have type argument change it to keyvaluepair(of string,datatable)

The above statement will geoup the datatable in multiple datatable each having a key ehich is the raw code and the value datatabe which contains only data related to that raw code

So inside loop currentitem.Key will give the raw code

And thisString.Join(",",currentItem.Value.AsEnumerable.Select(function(x) x("Vendor").ToString)) will give the comma separated vendor details as a string to use in the loop

Or

In the current setup save the text file with the raw code you are using…so that when you get the data you xan use the filename as the rawcode to identify the relation and extract the code from the name

6

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