Create a custom ID using sharepoint ID system

[rowlinx]

I am creating a database that needs an ID number called proposal number that continues from the already existing proposal numbers. 

I know that there are limitations to how ID numbers work so I have thought of a work around but need help putting it in place.

When a person enters a name into the new entry it automatically saves. Saving gives it an ID number in sharepoint,  so after this a label is shown for the proposal number that is calculated by ID number + 170008 (this is a constant number). 

Then that proposal number that is calculated from the ID number would get saved in a column.

I hope I articulated that properly, there are a couple places I'm having trouble with. The autosave after the name is entered, and the subsequent proposal ID popping up and updating. 

 Thank you

[Meneghino]

If you use a Patch function to write to SP then just use ClearCollect(MyPatchedItem, Patch(SP_source, Defaults(SP_source), {record data here}))

Then MyPatchedItem will contain the ID column with the attributed ID.

If you use PowerApps edit forms (ugh...) then I think there is functionality to return the latest submitted record.

[rowlinx]

I'm using the powerapps edit form. But the problem goes deeper than just using the last submitted record. Most of the time multiple people will be using this app. So I need to use the sharepoint ID number since that won't ever be duplicated. Or were you trying to mean something else?

[rlmckean]

I have got over this by using an update context variable as the first statement in the update string such as
Updatecontext({idno: last(sp_source).ID+170008}) then update and patch the ID field with the variable idno..
Eventually worked fine!!
Cheers
Richard

[Meneghino]

Hi @rowlinx

I am a bit lost on what you require.  SharePoint will by definition create a new unique sequential ID for every new record created by anyone.  The new ID is not created in PowerApps and it cannot be.

If you can be a little more specific then I can try to help.

In regards to the suggestion by @rlmckean, this can work but has a serious flaw.  If someone else creates a new record after you have created yours but before the UpdateContext is called, then you will get their ID and not yours.  That is why I suggested using the properties of the form, which avoids this pitfall.  Please see here for details:

https://powerapps.microsoft.com/en-us/tutorials/control-form-detail/

In particular, please read the piece in relation to the LastSubmit property:

LastSubmit – The last successfully submitted record, including any server generated fields.
This property applies only to the Edit form control.
If the data source automatically generates or calculates any fields, such as an ID field with a unique number, the LastSubmit property will have this new value after SubmitForm successfully runs.
The value of this property is available in the OnSuccess formula.

[rlmckean]

Hi meneghino

Indeed I am lucky in that I have been working on an app that wont have too many users - However thanks for your warning which others need to consider. 

The reason why I needed a work around was that I had imported data from webapps into sharepoint lists and I had a 'join' table that held IDs from the azure database. However when these were imported into sharepoint all the links broke as the IDs changed. I then had to create a table specific "oldid" column  to access the old data and then needed to increment this for the new data. 

@rwolinx would seem to want to create a unique  Id number for her quotes or whatever. 

Thanks for pointing us at the last submitted and onsuccess. - One really needs to dig deep to find this ! - I had read this tutorial but never got right to the bottom! - However no doubt there will be much trial and error as no example syntax is provided !!

Your solution using the clearcollect is good and demonstrates some coding I had not known about in the Patch routine

Catching the ID number is something that I have used for a long time in Access and WebApps - why has Microsoft hidden this away  in Power Apps so it is so difficult to find  ( like many other quirks it has ) !!

Cheers

Richard

[rowlinx]

Hi sorry that I wasn't clear originally. 

I understand that sharepoint is the one that creates the ID number.

This is the process that I think would work

Enter data into first box --> autosave happens

Now that sharepoint has a saved entry an ID will have been created

Display ID + 170008 

Essentially I want to use sharepoints ID system. But an ID is only created after a record is saved. I want the record to be auto saved right after someone enters data into the first box. This way I can show the proposal ID number that I calculated from the sharepoint ID number as the person continues creating the new entry. 

Does that make more sense?

[Meneghino · Accepted solution]

Hi @rowlinx, what you say is now clearer, thank you.

First of all a bit of background, apologies if this is clear to you already: SharePoint will only create a new ID when a new record is created.  There is no way to 'ask' SharePoint what the new ID will be for a future new record or to reserve one.

Having considered this, are you absolutely sure that you want to create the new ID only with partial data in the new record?
I am asking this because normally a database would have conditions on columns (such as columns which require data etc., conditions on dates etc.) which are very good practice to keep data integrity.  So if you create a partial record only to get its ID, then you need dummy data to make the partial record acceptable to the database.  Also, please consider that if the app or the device or the user stops what they are doing then you are left with an incomplete record in the database.

If you are still convinced that you need a new ID before the record is completely created, then the easiest way is to have an action linked to a data input in the first box that creates and retrieves a new record.  What shape this action takes depends on what the first control is (i.e. text input or drop down etc.) and if you are using a new item form.  For example you could use an OnUpdate property of a control with a submit form action.  The ID box could simply display ID+170008 by default.

Sorry I cannot be more specific, but hopefully I have given you some pointers.

[rowlinx]

Hi @Meneghino, thank you for your indepth reply. 
I assumed that the best place to put this addition function was before the final record was saved. Since the SubmitForm function exits if it's successful.

Is there a workaround to the SubmitForm function. From my understanding on the OnSuccess property of the SubmitForm the Back() function is ran. However when I search properties I can't access the OnSuccess. I think if I could find a way to save the form without exiting I could get what I want out of this.

Thanks again

[Meneghino]

Hi @rowlinx

You should look at the LastSubmit property if you have not done so already.  It may provide what you are looking for.