Ref number with variables

[Lefty]

Hi all,

I am stuck with the following if someone could help:

I have a canvas app using SP list as its data source.

I have previously auto populated my ref number using the default ID field of a SP list, but now I need to increment the number from a choice column found in the form.

In a choice column the user has the following values:

TeamA

TeamB

TeamC

TeamD

When a user select TeamA I want the ref number to be 0001 and increment each time any user then submits a record, 
and when the user selects TeamB I want the ref number to also be 0001 and increment and so on for the remainder

Many months ago I learnt (Thanks PA community) the best way to increment ref numbers was using the SP ID  field and in other methods I found there was a chance of 1 record having the same ref number, maybe when 2 users were submitting at similar times.

If any one has any suggestions please let me know

Thanks

[poweractivate]

@Lefty 

Just keep the ID field, other solutions that comes to mind are much harder really.

If you want to display it somewhere as zero padded, try this Formula:

Text(Value(10),"0000")

In above replace 10 with even a direct reference to the ID column and it should work.

It is basically a trickery but if you use that formula, the ref number is still actually the non zero padded one, but just use the Formula to make an illusion that it is the zero-padded one.

So as for this:

---

@Lefty wrote:

the best way to increment ref numbers was using the SP ID  field and in other methods I found there was a chance of 1 record having the same ref number, maybe when 2 users were submitting at similar times.

---

Well maybe you are right that "other methods" might have that problem. I believe SP ID field probably doesn't have that problem. Even if it is at same time, I believe the system generated SharePoint ID will be unique anyway. So probably this is likely simplest and best solution for you.

As for this:

---

@Lefty wrote:

When a user select TeamA I want the ref number to be 0001 and increment each time any user then submits a record, 
and when the user selects TeamB I want the ref number to also be 0001 and increment and so on for the remainder

....

If any one has any suggestions please let me know

Thanks

---

Well if you need to do it based on Choice selection, this may make the solution need a bit more - you cannot just simply increment based on the Choice if using ID field. So instead if you still want to keep the easy ID method, you may need to have a Master SharePoint List. Then a Choices field in that Master List. Then you may need to have a separate SharePoint List just dedicated for each Team. When the Choice is made, the Record is created in that specific List with the specific details about the Team. The ID of that Record in that List is then fetched and placed in the Master SP List for easy cross reference later in case. It would also probably help if the Team cannot be changed at any time after that, if it does have to be changed after that for same record, this could make the solution more complicated. Once ID is generated, it will keep incrementing up forever, if previous ID's for a Team need to be reused, the solution will be more complicated - presumption is previous ID's do not need to be reused. 

In case the above creates an undesirable arrangement where data is siloed onto separate Lists, then you can invert the above idea and instead re-purpose the separate Lists solely for the generation of the ID's but not to store important data - so you create a new record for that Team - just ID and nothing else - then you just take the ID of that specific record from PowerApps, place it in Master List, and keep all the data in the Master List to avoid the siloed problem.

If you are ever concerned about concurrency by the way, you can also have a Power Automate Flow where the Concurrency is set to 1, and have Power App go call that Flow every time to generate the ID, because only one execution at a time will be done in Truly Atomic way. I think it is probably not needed - because even if it is at exact same time, the record generated by Power Apps Canvas App on the Team List can be the same one the ID is fetched from anyway even without going through any Atomic Flow - so probably this is not even needed to go this far honestly.

Check if above ideas help you @Lefty .

[Lefty]

@poweractivate 

Thanks for your detailed response.

I'm unsure how this will help or where I use it:

Text(Value(10),"0000")

It may help if I provide my code which doesn't achieve my goal:

Concatenate("AB-BS-" & Year(Now()),"-" & vGroupName & "-", Text(First(Sort(ListName,ID,Descending)).ID +1, "[$-en-GB]0000")) 

This only increments my ref numbers by 1 using the ID field and is referencing the variable to distinguish the TeamA, TeamB etc.

I don't want to have to store the data in multiple lists, I feel this will have too much of an overhead in managing the app and list in the long term. But I do not expect the team name to change, if it does it will be treated as a new team, with no changed required to be made to the existing data and ref number.

I had thought some sort of count function may help look at the 1 SP list and count how many values from the choice column exists and it would then increment the number, is that not an option?
If not if you could help with this suggestion and detail a bit more steps, or if there is some examples online I could look at please:

In case the above creates an undesirable arrangement where data is siloed onto separate Lists, then you can invert the above idea and instead re-purpose the separate Lists solely for the generation of the ID's but not to store important data - so you create a new record for that Team - just ID and nothing else - then you just take the ID of that specific record from PowerApps, place it in Master List, and keep all the data in the Master List to avoid the siloed problem. 

[Lefty]

Hello

Is anyone able to further advise if this is correct, sort of is working, this may be a dead end or not a robust option, but I have been trying options without having to create separate lists, so I did the following on the choice field value, created a label with the following giving me the total times this choice value is currently selected in my SP list:

CountRows(
           Filter(SPList, Group.Value = GroupDD.Selected.Value)
)

I changed my code on the ref number to this and with no errors and it increments :

Concatenate("AB-BS-" & Year(Now()),"-" & vGroupName & "-",Text(CountRows(
           Filter(SPList, ABGroup.Value = GroupDD.Selected.Value).ABGroup)+1))

What do you all think, it seems to do what I need it to, but I don't know if it will go pear shaped later

[poweractivate]

@Lefty Maybe @timl or someone else have some different idea for you if you want to avoid to use the multiple lists and still stay on SharePoint has well.

[Lefty]

Thanks @poweractivate 

I've just edited my posts, got further with it, see update, want to know if anyone can advise if this will not work long term or in fact its flawed

[timl]

@poweractivate - thanks for tagging me!

@Lefty - this looks good. The only concern I would have here is that CountRows is not delegable. Therefore, if the number of records for a given team exceed the maximum row limit of 2000, the allocated number won't exceed 2001.

Therefore, you might consider storing just the numeric reference in a reference column. You can then look up the next number using this type of syntax, which is delegable.

First(
      Sort(
           Filter(SPList, Group.Value = GroupDD.Selected.Value),
           ReferenceNum,
           Descending
       )
).ReferenceNum + 1

Also, if you call Refresh on SPList and calculate the next reference number just before you call Patch or SubmitForm, that should minimise the possibility of you generating a conflicting number.

[Lefty]

Hello @timl 
Sorry for the delayed response.

I don't have any non delegable warnings in my code, I recall seeing in other parts of my app but not the above, is it still not delegable but not displaying on my code? I highly doubt the records for each section to go over 2000, i guess I'll change if your suggesting its better to do so.

I do not follow what you mean by storing the numeric reference in a reference column? Correct me if I am wrong in my understanding, I would need lots of these columns for each choice value I have in field?
Curious about the refresh, I haven't used it in any of my apps.

If I do Refresh(ListName); before my patch, it will obtain the latest data from SP and automatically update my ref to the next value, or do I need to add some code to increment the refnumber after the refresh function. i.e. do I need to add the code:

Concatenate("AB-BS-" & Year(Now()),"-" & vGroupName & "-", Text(First(Sort(ListName,ID,Descending)).ID +1, "[$-en-GB]0000")) 

after the refresh but before the patch function on the submit buttons OnSelect?

Thanks

[Lefty]

Hi @timl 

I've run in to the issue of 2 ref numbers being the same if both forms are submitted by different users at similar times, without a refresh.

I have added in Refresh(LIstName);

before my patch, and this oddly updates my PDF but and the email, but re-visiting SP list or the record in Power Apps, i find that the 2 records contain the same ref number.

So I gathered I needed to Concatenate after the refresh, again what you suggested, but I cannot get this to work - when i go back to my list or PowerApps record, the ref number field is now left empty as I am not longer setting the value in the form, rather trying to set it just before patch.

Refresh(List);
Concatenate("ABC-" & Year(Now()),"-" & vGroup & "-",Text(CountRows(
           Filter(List, vPAGroup.Value = vGroupDD.Selected.Value).vPAGroup)+1, "[$-en-GB]0000"));
UpdateContext(
	{
	vPatchedRecord:
	Patch(
		List, Defaults(List),
        InfoForm.Updates,
	PIForm.Updates,
        PIForm2.Updates
		
	)
	}

I can see I am not pointing the concatenate function to my field, but unsure how I do this, I tried to ref the text box, but this did not work either

[Lefty]

update:

I have I think  managed to make further progress by doing this:

Refresh(List);
UpdateContext(
	{
	vPatchedRecord:
	Patch(
		List, Defaults(List),
        InfoForm.Updates,
	PIForm.Updates,
        PIForm2.Updates,
	{SerialNo:Concatenate("ABC-" & Year(Now()),"-" & vGroup & "-",Text(CountRows(
           Filter(List, vPAGroup.Value = vGroupDD.Selected.Value).vPAGroup)+1, "[$-en-GB]0000"))}
	)
	}

But the issue now seems to be getting my html box, which picks up the data for Flow to PDF, no longer can read the serial number field as its empty and I have a local variable vPatchedRecord which of course cannot be recognised in my html control - not sure how i overcome this?

Other than that it is functioning, (i think).