How to use For Each Loop against a specific value, not each row of a datatable?

[JayBee]

Hope someone can help. I’m trying to run a For Each Loop against Column N but only start a new For Each Loop when the value changes. how do I achieve this? Image below for example table.

Thank you for your help.

[supermanPunch (Arpan)]

@JayBee Not sure as to How you would want the output to be, Can you explain a bit more in detail ?

[JayBee]

So the objective is for each row with the same value from column N will go into a sales order system. so first ‘order’ will be 1 item(row). second ‘order’ will be 2 items(rows) and third ‘order’ will be 3 items(rows).

But i need to create a loop that only repeats when the value in column N changes. I’ll create a nested For Each Loop that’ll input each line item into the order system.

but its the first issue that i’m struggling to get around.

or am i doing/thinking this all wrong?

Thanks

[supermanPunch (Arpan)]

@JayBee Maybe an Analysis on How you would want the Output can be a start for us to help you solve the problem. So are you updating the values of Column N in a website? If so, How would you want it to be updated, each time? Is it on the same field that you need to update or different fields?

[ppr (Peter Preuss)]

@JayBee
it looks like you would like to form groups by Column N on the same Column Value.

Have a look on this GroupBy Demo, grouping by 1 column key and then process all group members group wise.

find demo xaml here:
GroupBy_1Col_BasicDemo.xaml (10.2 KB)

[JayBee]

I’ll try to give some context.

Column N contains the order reference number (a unique value for each order). On that example I’ve given above there are 3 unique orders. what I need is something to monitor the N Column as it works through each row until the N column value changes from one value to another. once it changes to a new value I want it to create a new sales order on our ERP system using the value in F Column, which is a customer account code, and input this value into our Customer Account Code field and create a new sales orders and input the line items under the current N Column value, etc etc until it reaches the end of the datatable. I’ve supplied some images below of our ERP to hopefully help make it clearer.

@supermanPunch I hope this helps somewhat for you?

@ppr thank you for this, i’ll test this Group By demo you’ve provided in the morning.

Thank you both for your help so far.

Josh

[JayBee]

[JayBee]

Hi ppr,

This is definitely very close to what I was aiming for.

I have a question. inside the For Each loop before the For Each Row loop. I want to use a Type Into activity that inputs the value from Column F from each ‘grp’ into a field.

When I try to do it currently, it comes up with;

Can you help?

Thank you.

[ppr (Peter Preuss)]

it looks like that is coulumn F from your excel that is:

• same value in grp
• also retrievable from row for all group members

gave a look on the demo log from demo xaml:

• common group values are retrieved with: grp.First()(RowNameOrIndex).toString() ( col value as string)
• row(ColNameOrIndex) (col value as string)

in grp all group members are contained and it can be multiple rows. That’s why use first() as we do get it frm a single row (The first group member row)

About the exception:
in general there is datatype issue: string is giving and integer is expected
from the screenshot there is on first look no hint except the missing first()

give a try on grp.First()(“Invoice_Lay…”).toString

[JayBee]

ppr thank you so much my friend.

Just tested the grp.First()(XXXX).ToString and its worked just as I had hoped.

Again, thank you.

you have saved me a great deal of headache and I can now move onto the next step of the process.

I will test the for each row function now but i’m confident I can get that part working fine. was just the getting the lines grouped that was my biggest problem!

and thank you @supermanPunch for trying to help in the first instance.

I will try my best to learn and improve my explanations.

All the best.