Two For each row in data table activities

[Sonalk (Sonal)]

There are two for each row in data tables activities: Table 1 (main schema) and Table 2.

Inside For each row in Data table 1, Table 2 contains multiple records of same unique id as mentioned in screen shot.

Note : Table 1 contains single unique records but table 2 records can be multiple.

These are process steps in workflow

1. Using for each row in data table 1, extract unique id

2. Using Filter data table activity (pass unique id) for table 2 and use for each row in table 2 filtered records to make one record out of multiple records based on business rules.

3. Outside loop of table 2. Add variables in data row of table 1 columns

4. Add transaction item to queue at end of both loops

Issue : Sometimes getting unwanted values of different unique id and add to another unique id columns in Table 1

[raja.arslankhan (Raja Muhammad Arslan khan)]

Hi @Sonalk,
please give me code file and excel file as well

[ppr (Peter Preuss)]

@Sonalk

Using for each row in data table 1, extract unique id

you can check if the first loop can be avoided when using following statement:
dtUniqueIDs = dtOrig.DefaultView.ToTable(True, {"YourIDColName"})

So the second loop can iterate over dtUniqueIDs

@Sonalk

Using Filter data table activity (pass unique id) for table 2 and use for each row in table 2 filtered records to make one record out of multiple records based on business rules.

sounds like a data grouping on ID

@Sonalk

Outside loop of table 2. Add variables in data row of table 1 columns

here the other statement should be cross-checked on possible side effects

@Sonalk

for each row in data table 1, extract unique id

when the update is placed on wrong dt1 rows, then maybe the dt1 unique ID logic is confused. We would recommend doing the first analysis by debugging and tracing the flow.

As mentioned by Raja, if possible share also some more details on sample data and implementation details with us.

[Sonalk (Sonal)]

Table 2.xlsx (9.0 KB)
Table 1.xlsx (8.6 KB)
Output Data table.xlsx (9.1 KB)
Find the attached tables and expected output.
Table A contains unique values
Table B contains multiple records of same unique id

[Sonalk (Sonal)]

Hi @ppr : First loop is having unique records.

It seems issue appeared when processing second loop inside first loop to consolidate values.

I have tried both methods : 1. Join different data tables with unique id 2. Pass required values from second loop and update into first loop as row items during processing of first loop.

Table 1.xlsx (8.6 KB)
Table 2.xlsx (9.0 KB)
Output Data table.xlsx (9.1 KB)

[Sonalk (Sonal)]

Hi @ppr : As you suggested, I took different approach this time.

I have used inner join activity (1 to n mapping) and create one table which contains multiple records of unique id.

Now i have to create multiple rows of unique id to single row in different table after applying business rules during processing.

Apply add data rows is not solving this problem statement.

Id Price Discount Allowance1 Allowance2 Status

1 12 5 0 1.1 successful

1 12 5 1.3 0 successful

Expected Output

Id Price Discount Allowance1 Allowance2 Status

1 12 5 1.3 1.1 successful

Note : There are around 50 columns in PROD data table

[Sonalk (Sonal)]

Hi @raja.arslankhan : Find the attachments for your reference.