Answered

Best way to create a sequentially numbered ID in a column

(0) Share

Report

Posted on by krickard

124

I have this code below that I've been working on that is copying some records from one place to another. The collection the data is coming from does not have a unique identifier, yet the table I'm patching needs a unique ID.

My current code for QID below only results in a value of 1 for all patched records. What I'd like to happen is if I patched 6 records I want the QID to be 1,2,3,4,5,6 respectively.

How do I get each loop of the ForAll to write a +1 QID?

ForAll(colAssessments, Patch(TempQuestions,Defaults(TempQuestions),{AssessType: colAssessments[@AssessType],Category: colAssessments[@Category],QuestionText: colAssessments[@Question],QID: Max(QID) + 1, A1Text: colAssessments[@A1],A2Text: colAssessments[@A2],A3Text: colAssessments[@A3],Timestamp: Text(Now()),User: User().FullName} ))

Btw... I've seen this video linked in another post, it didn't work for what I'm trying to do. https://www.youtube.com/watch?v=plKw_xfVfwY&feature=youtu.be

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (2)

Sort by

Verified answer

WarrenBelz 155,779 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @krickard ,
Please try the below - I hope I have the brackets and commas all in the right places.
Clear(colAssessNo); ForAll( colAssessments, Collect( colAssessNo, Last( FirstN( AddColumns( colAssessments, "IDNo", CountRows( colAssessNo )+1 ), CountRows(colAssessNo)+1 ) ) ) ); ForAll( colAssessNo, Patch( TempQuestions, Defaults(TempQuestions), { AssessType: colAssessNo[@AssessType], Category: colAssessNo[@Category], QuestionText: colAssessNo[@Question], QID: IDNo, A1Text: colAssessNo[@A1], A2Text: colAssessNo[@A2], A3Text: colAssessNo[@A3], Timestamp: Text(Now()), User: User().FullName } ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Verified answer

v-siky-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @krickard ,

First to define the initial value in the collection and plus 1 in each ForAll loop. Please try this:
ClearCollect(No,1);
ForAll(colAssessments, Patch(TempQuestions,Defaults(TempQuestions),{AssessType: colAssessments[@AssessType],Category: colAssessments[@Category],QuestionText: colAssessments[@Question],QID: Last(No).Value, A1Text: colAssessments[@A1],A2Text: colAssessments[@A2],A3Text: colAssessments[@A3],Timestamp: Text(Now()),User: User().FullName});Collect(No,1+Last(No).Value))

Hope this helps.
Sik

Was this reply helpful? Yes No
WarrenBelz 155,779 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @krickard ,
You might try mine as well - I spent some time testing it and it works.

Was this reply helpful? Yes No
krickard 124 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @WarrenBelz and @v-siky-msft

@WarrenBelz for some strange reason the code you provided gives me the same "expecting Text... got Table" error that we saw in the last thing you helped me with (link to that post). I tried the same trick that worked before, creating a new canvas app enough to support the code and copying the working button over to the existing app where it was receiving the error... but this time no luck, it still received the error in the new canvas app.

When trying @v-siky-msft's code it works and doesn't receive the error as I stated above. I'm not sure what would cause one to work and the other not work, especially when @WarrenBelz tested this to work on his end.

Was this reply helpful? Yes No
WarrenBelz 155,779 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

OK @krickard ,
I could only test the first bit to prove you got your original list with a sequential column added. I could not test the rest of your code as I have no vision of your data structure or control content.
Out of interest have a look at colAssessNo and see what types of fields (text etc are there).They need to line up with your data structure.

Was this reply helpful? Yes No
krickard 124 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz yes you are correct. It looks like the issue is that the column names for A1, A2, A3 in Saved Assessment are different than A1Text, A2Text, and A3Text in TempQuestions. Once I align the column names your code works.

Was this reply helpful? Yes No