Add Data Row with condition

Help StudioX
,
1

Hi Everyone, I am using Studio X.

For each row in Data Table
→ Add Data Row (new object() {strADB,strDate,strPOnumber,strLeadtime,strPIC,strCostcenter,strSupplier,CurrentRow(“ITEM DESCRIPTION”),CurrentRow(“QTY”),“”,CurrentRow(“UOM”),CurrentRow(“UNIT COST”),strDelivery,strCurrency,“”,strDiscount})

Then use Write DataTable to excel to display the output.
But now I want to make the strDiscount output 1 per strADB,
Before, every row is displaying the amount:

{B040E1C6-5FB4-4B8A-8813-166F9B9D0CAF}
{B040E1C6-5FB4-4B8A-8813-166F9B9D0CAF}230×298 1.68 KB

After, it should be like this:
{0F3AEFFD-504B-4ADC-A088-CE92F761C98C}
{0F3AEFFD-504B-4ADC-A088-CE92F761C98C}237×289 1.79 KB

2

Hi @flashdrive07,

Inside the For each row in DataTable, add a if condition to check LoopIndex=0 and implement the below in block:

Then - no action

Else → assign strDiscount = String.Empty

Thanks

3

hi
since you are adding rows in a datatable and write one by one, you cannot directly achieve what you want directly..
So this is what i think you are looking for, grouping items based on “StrADB” and then write the 1st cell of the filtered Datatable with the value “strDiscount”..

try this approach:

Build datatable with all fields including strDiscount
for each StrADB unique values
{

  1. add rows to the datatable with all the rest of the fields PER StrADB value excluding the discount amount(strDiscount).
  2. Now calculate the StrDiscount value and update the “1st row of column StrDiscount” with the value of strDiscount.
  3. now you have a table that has all the values you need plus the StrDiscount Value at row 1 and column “StrDiscount”.
  4. If you want them in excel, use append range action to write these values to excel, with add headers flag off.
    or if you want them in a datatable, you can use Dt_mainTable.Merge(Dt_temp, True, MissingSchemaAction.Add)
    or merge action. (Dt_temp being your datatable that has data specific to StrADB value)
  5. after writing, clear the data in the datatable.
    }
    this loop should continue for each and every StrADB.

hope this helps.

regards
SG.

4

Hi,

Simple steps:

  1. Create variable lastADB = “”
  2. In For Each Row:
  • If strADB = lastADB → set strDiscount = “”
  • Else → keep value and set lastADB = strADB
  1. Add Data Row as usual

This will show discount only once per ADB.

5

is this correct?

{ABC23C71-17AD-44B2-BAFE-E2F9733E5272}
{ABC23C71-17AD-44B2-BAFE-E2F9733E5272}536×548 13.3 KB

6

@flashdrive07

Follow below steps,

use linq to resolve this without any time delay,

image
image469×702 34.6 KB

Linq in 2nd assign:
(From r In dtInput.AsEnumerable() Let grpKey = Convert.ToString(r(“PO Number”)) Let firstRow = dtInput.AsEnumerable().Where(Function(x) Convert.ToString(x(“PO Number”)) = grpKey).First() Select dtInput.Clone().LoadDataRow({r(“PO Number”), If(Object.ReferenceEquals(r, firstRow), r(“Total Amount Discount”), DBNull.Value)}, False)).CopyToDataTable()

Input:

image
image358×304 2.8 KB

Output:

image
image348×321 3.7 KB

If you found this post helpful please mark it as solution

7

Hi,

Yes, this looks correct :+1:

If your output shows strDiscount only in the first row for each strADB and blank for the rest, then it’s working as expected.

Your approach with lastADB is the right and simple way to handle this.