cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TorreyFalconer
Level: Powered On

Unique ID (sharepoint list) BEFORE submit

Hey all, 

 

Super frustrated here. Basically I need a unique ID number to be created when someone starts a new form. My data source is a sharepoint list and so I was hoping to use the "ID" column for this feature as it does give you a unique ID for every new entry. The problem is that it only generates the ID after you click submit and I need it to display the ID beforehand. 

 

Currently I have the text property of the datacard "ID" set to If('Form3-table2'=New, Last('SaskEnergy- Incident Reports').ID+1, Parent.Default), however that doesn't seem to be working, even though I do have previous entries in that list so it should be able to calculate the next ID. 

 

I also tried making a second column in the Sharepoint List with a calculated column to copy the ID column with the formula [ID], but as soon as an entry it completed then that column changes to =ID and stops copying the ID column. 

 

Basically all I require is upon opening a new form, that form to auto-calculate a unique number that is from a Sharepoint list. Someone please help me! Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Unique ID (sharepoint list) BEFORE submit

Hiya!

 

You can't set the ID column, it's auto-generated, that's why you're having issues trying to calculate it and set it! Cat Surprised

 

Two ways around this (sort of)

  1. Submit to create the row on screenload, then use lastsubmit fucntion to determine it's ID, navigate to a new page where another form patches that existing record that was just created.
    • problem with this is if they cancel there will be a blank row unless you delete it
    • If they dont cancel and just close out of the app then it wont do the delete function
  2. Create a new number field, have it increment by one each time the New Thing screen loads, that way the likelihood of getting a duplicate is very unlikely.

 

What I'd recommend instead is to have a follow-up screen after you've submitted that uses the form's LastSubmit function to get the ID, and then have it in large "Please take note of your item's ID" with a tickbox to confirm they have taken note of the ID.

 

If the above doesn't help you at all could you share a bit more about your intended implementation?

 

Thanks!

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

Super User
Super User

Re: Unique ID (sharepoint list) BEFORE submit

@TorreyFalconer are you using FormXYZ.LastSubmit.ID? I've not tried it with a calculated column, and would normally just reference the ID directly as it will always be unique

 

Just did a quick test using ID of Last submitted item:

image.png




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

16 REPLIES 16
Super User
Super User

Re: Unique ID (sharepoint list) BEFORE submit

Hiya!

 

You can't set the ID column, it's auto-generated, that's why you're having issues trying to calculate it and set it! Cat Surprised

 

Two ways around this (sort of)

  1. Submit to create the row on screenload, then use lastsubmit fucntion to determine it's ID, navigate to a new page where another form patches that existing record that was just created.
    • problem with this is if they cancel there will be a blank row unless you delete it
    • If they dont cancel and just close out of the app then it wont do the delete function
  2. Create a new number field, have it increment by one each time the New Thing screen loads, that way the likelihood of getting a duplicate is very unlikely.

 

What I'd recommend instead is to have a follow-up screen after you've submitted that uses the form's LastSubmit function to get the ID, and then have it in large "Please take note of your item's ID" with a tickbox to confirm they have taken note of the ID.

 

If the above doesn't help you at all could you share a bit more about your intended implementation?

 

Thanks!

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

geneZebra
Level 8

Re: Unique ID (sharepoint list) BEFORE submit

I did this with an excel table but it should be the same. Make sure the "Default" and "Update" properties are set correctly on the dataCard. Default sets up the property value for display but Update is what commits it to the source 

 

Also it looks like your if statement may be missing the ".Mode" property for the conditon.

 If('Form3-table2'.Mode=New, Last('SaskEnergy- Incident Reports').ID+1, Parent.Default)

 

BTW, if Sharepoint supports AutoID, you may not want to update ID and treat last()+1 as a refernce for display purposes then get/use the acutal ID value after the save is done. I am using excel so I had to do set the ID manually and really had now choice. Another idea could be to always append a blank row, use a context variable to hold the "new record" that the form shows and use patch instead of submit. That way their would be no contention over the ID number.

Highlighted
TorreyFalconer
Level: Powered On

Re: Unique ID (sharepoint list) BEFORE submit

@iAm_ManCat 

Thanks for this. Though I wasn't trying to calculate the ID column, I was trying to copy the already auto-generated one with a calculated column.

 

I like your idea of changing it so that after they hit submit, they are presented with their ID and have to take note of it. I am trying to do this with the auto-generated ID column once it is submitted but am having trouble getting the number to display. Do you have any thoughts on this? Once they submit the form, I have the ID portion on the success screen, it is in display mode but still not showing. Any thoughts?

Super User
Super User

Re: Unique ID (sharepoint list) BEFORE submit

Heya @geneZebra,

 

Just a quick one on this - if you use Last(something).ID+1 for anything and let's say five people open the form but don't save it immediately, and then all go to submit, they will all have the same Last(something).ID+1 Cat Surprised as you can imagine this will end up in chaos when someone says "but I have ID 5; no I do; so do I; and me!" Cat Happy

 

SharePoint generates unique ID's with every row submission, but @TorreyFalconer is correct in that it generates them once submitted only, and you can't create them manually (except using methods I outlined above) - Excel does allow us to do this manually as you stated, however we need to be careful of timing to avoid getting into the situation I mentioned 🙂

 

Cheers,

ManCat




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
geneZebra
Level 8

Re: Unique ID (sharepoint list) BEFORE submit

If you mean displaying as per your original post, not the missing "mode" or "displaymode" property missing in the if statement. This may explain why the conditon is hitting the false case which shows the default/blank value.

 

If you mean in the new approach of creating an update in an existing record, an approach to try would be to

1) patch in a blank record,

2)set a context to the return value of the patch,

3) fill in the variable based on the values you want in there (or copied off another record),

4)have the form's "item" property point to the context varible. Just make sure you use patch instead of sumbit. and delete the record if form is cancelled.

This could probably be done better but wanted to share the logic in case you decide to go that route.

Super User
Super User

Re: Unique ID (sharepoint list) BEFORE submit

@TorreyFalconer are you using FormXYZ.LastSubmit.ID? I've not tried it with a calculated column, and would normally just reference the ID directly as it will always be unique

 

Just did a quick test using ID of Last submitted item:

image.png




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

View solution in original post

geneZebra
Level 8

Re: Unique ID (sharepoint list) BEFORE submit

I agree with you. I really want to port my app over to sharepoint jsut for that auto-id feature but it seems problemmatic and I started to get all sorts of new "delagation warnings" , and have to go through all my uses of ID, etc. I may opt to rewrite from scrarch.

 

Super User
Super User

Re: Unique ID (sharepoint list) BEFORE submit

Happy to help with any delegation issues you may have, just flag me directly @iAm_ManCat when you raise the forum post about it 🙂

I've had my fair share of delegation issues as we have our entire backend data stored in SharePoint Lists and Libraries 😄




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
TorreyFalconer
Level: Powered On

Re: Unique ID (sharepoint list) BEFORE submit

@iAm_ManCat 

 

Tailing off of this last issue, I may need some additional help related to this (it just gets more complicated).

 

Some background to help you understand: Basically I have two separate lists created on Sharepoint. One list is an "Incident Report" list and the other is a "Witness Statement" list. When I launch the powerapp, you will have the option to begin a new "Incident Report". That is where they fill out the form, and that unique ID is provided to them at the end. *After this, they are given the option to fill out a Witness Statement pertaining to that Incident Report*

 

Now here is my next problem: I need that Unique ID to auto fill into the "Witness Statement" form. I have a number column created in the Witness Report form titled "Incident Report ID". So far I have managed to get that to auto-fill in the app from the previous screen using Form1'.LastSubmit.ID . The thing is, once that witness statement is submitted, the ID doesn't seem to appear in the list. 

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 174 members 5,100 guests
Recent signins:
Please welcome our newest community members: