Adding incremental column to collection

[chrisog]

Hi,

I have a collection, colHirelist, in which I need to add a single column, "idhirelist", with an incremental value equal to the row number.

For example, I need row 1 to have an idhirelist value of 1, row 2 idhirelist value of 2 etc.

I have tried using various methods to solve this but I cannot figure it out.

[jhall]

First thing I'd be trying would be an Addcolumn() then a ForAll() w/ an incremented value done within it.  Just a guess, but maybe you'll test that before I do.

[chrisog]

How exactly do you mean?

I'm not sure exactly how to structure this as I don't know how to get the values to increment.

I feel like I should know this and it should be simple but for some reason I just cannot figure it out.

[jhall]

You have a curious problem here.  Since PowerApps doesn't really do "looping" (in the traditional sense), it's tough to iterate a value and perform an action using it. 

I just tried to just drop a DataTable and set the Items property to do:  AddColumns(myIDList,"ID",myInt+1).

I didn't think it would work but it was worth a shot.

Absolutely worst case, you could do a timer to update values and iterate through the list to add them to a column.  But let me ponder this for a moment and see if there's some function I've overlooked.

[chrisog · Accepted solution]

I think I just found the solution:

ForAll(
colHirelist,
Collect(
colMergeHirelist,
Last(
FirstN(
AddColumns(
colHirelist,
"idhirelist",
CountRows(colMergeHirelist) + 1
),
CountRows(colMergeHirelist) + 1
)
)
)
)

My first collection, colHirelist, holds the initial values. The second collection, colMergeHirelist, has the additional column.

The ForAll command loops through each item in colHirelist and adds the idhirelist value. The Last/FirstN portion makes sure to grab the next row of the initial collection and then use the same "row number" value as is in the idhirelist to determine how far down the collection to go.

Does that make sense?

I think i had an epiphany.

[jhall]

That is hilarious since I was just building this out.  Create new list, count new list to calculate the value for the item, copy record to new list while adding column using count results, all in the middle of a ForAll() calculation.  I think that's the right approach.

[HarjitSamra]

I'm looking at doing this also.

@jhall  - did this work for you?  

[J_Anderson]

@HarjitSamra , I realize this is a few yrs old, but following this concept appears to have worked perfectly for me. I didn't entirely get the use of Last and FirstN, but I kept them in there and the collection is going 1-5 in the collection viewer, so seems to have worked perfectly for me. 


ForAll(
colSubmissionFinal,
Collect(
colAddAutoNum,
Last(
FirstN(
AddColumns(
colSubmissionFinal,
"AutoNumAdd",
CountRows(colAddAutoNum) + 1
),
CountRows(colAddAutoNum) + 1
)
)
)
)