@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 .
