Using an increment within ForALL

[altafr]

Seemingly simple requirement - want to go through a table looking for  a certain ID and then tag a field within the table with values "01" ," 02", "03" based on how many rows. 

So first i filtered all rows required in a collection 

ClearCollect(myrows, Filter (mytable, Field = mycondition));

The used a forall 

ForAll (

myrows,

Patch(

mytable,

mytable.ID = myrows.ID,

{myfield: Text(Index,"00")},

))

Now how do i increment the Index so the next record gets patched with 02 in the forall? 

alternatively any other way to approach this problem. any advice appreciated. 

[RandyHayes]

@altafr 

Give this a try...

Clear(myrows);
ForAll(Filter(mytable, Field = mycondition),
    Collect(myrows,
            {myID: ID,
             myIndex: CountRows(myrows)
            }
    )
);

Then for your patch:

ForAll (myrows,
   UpdateIf(mytable,
            ID = myID,
           {myfield: Text(myIndex,"00")}
   )
)

I hope this is helpful for you.

[v-xida-msft · Accepted solution]

Hi @altafr ,

Do you want to auto-increment the Index value within your ForAll formula?

Based on the needs that you mentioned, if you want to auto-increment the Index value within your ForAll formula, I afraid that there is no direct way to achieve your needs.

As an solution, I think a collection could achieve your needs. Please consider modify your formula as below:

Clear(TempCollection);
ForAll (
        myrows,
        Collect(TempCollection, 1);
        Patch(
              mytable,
              LookUp(mytable, ID = myrows[@ID]),
              {
                myfield: Text(CountRows(TempCollection),"00")
              }
         )
)

Please consider take a try with above solution, then check if the issue is solved.

Best regards,