Generate Unique ID For Next Time Form Is Opened

[JD2301]

Hi Guys,

Wondering if this is possible.. I have a built a timesheet app which patches the info in the app to a sharepoint list when submitted. It also sends out an email notification to an approver for them to approve or deny (Depeinding on the value of the timesheet).
I need a way that the approver can reference which requests they have approved or denied.. Is there was a way I could have a unique ID somewhere in the form that I could also add into the Email message subject? 
So the email would read something like "To: Joe.Bloggs@Gmail.com , Subject: Timesheet Submission #001, Body: Please approve or deny submission #001. 

The '#001' would be a hidden text input/label in the form somwhere, then when the next person uses the form, this changes to '#002' and so on?  I've read a few things about using a +1 but unsure if this would work in this case.

Not sure if this is possible or if there is an alternative work around but any help would be appreciated.

Thanks.
JD

[RandyHayes]

@JD2301 

Have you considered just using the ID of the item you are creating in the SharePoint list?  That would be unique.

You would not have this as a hidden text field, you would simply use the form.LastSubmit.ID after you submit the record and put that in your email.

I hope that provides some fuel for thought.

[JD2301]

Hi @RandyHayes 

Thanks for the reply. That sounds like it would work but how would I know what the SP ID would be before I press submit and patch there?

I have 50 different screens in the form so the user can submit up to 50 different time sheets.
At the end of each screen, I have a button that says  'Submit Time Sheet', then that patches the info to my SP list.
Then a radio button appears that says 'Add Another Time Sheet' - Yes - No.
If they tick yes, the form navigates to the next screen which is a duplicate of the first one but has 'TimeSheet2' shown at the top, isntead of 'TimeSheet1'.
I don't know how many TimeSheets the user will submit, it could be 1 it could be 50.

Would this still work?

Thanks.

[RandyHayes]

@JD2301 

I'm a little confused over the purpose of the unique ID that you first stated then.

In the original post you mentioned having a Unique ID for adding to the Email.  

To be clear, there is a Unique ID for every record in your SharePoint list.  You can submit your timesheet and then use the ID that is created for the purposes of uniquely identifying a timesheet entry.  You would not need to get that prior to submitting the information to SharePoint (nor could you).  You would get that after submission and could then use it for your email ID or any other purpose that would "bring" a person back to that exact timesheet.

Now, your current message almost sounds like you want to number the timesheets. i.e. Timesheet 001, TimeSheet 002, etc.  This would not uniquely identify a timesheet in SharePoint.  This would only be a numbering ID.

If that is the case, then you could easily create a variable - Set(sheetCounter, 1) - and then increment that on each submit - Set(sheetCounter, sheetCounter + 1).  Then you could use that for numbering each sheet.

So, the question would remain - are you trying to uniquely identify timesheets in your system or are you trying to number them?

[JD2301]

Hi @RandyHayes ,

Thanks for that. I guess using the term 'Unique ID' was incorrect then as really what I want to do would be number the timesheets and add those numbers into my email notification, so the approvers look back to see which timsheets they have approved.

If I submitted 5 time sheets on Monday, (#001, #002, #003, #004, #005), then submitted 3 on Tuesday, would they be #006, #007, #008 using that work around?

[RandyHayes · Accepted solution]

@JD2301 

Ah..the plot thickens!!

So, it's not just that you want to number them incrementally, you also want to do this ongoing (everyday).

The bottom-line with that is - you're going to have to store the last number used for that user somewhere in order to pick it up from the last number the next time they use the App.

Well, there are a couple ways to handle that.

I would suggest that you simply create another list that would have two columns - the UserEmail (as a text column) and the LastTimeSheetNumber (as a numeric column).    Let's (for this example) call the list UserTimesheets

In my last message I mentioned the variable, so we can modify that a little.

You would instead make your variable this:   

Set(sheetCounter, Coalesce(Lookup(UserTimesheets, StartsWith(UserEmail, User().Email)).LastTimeSheetNumber), 1))

This will lookup the last number used in the list and set the variable to it.  The Coalesce is there so that, if there is no record in the list, it will be 1.

Next, on your submit, you would put in this Formula:

Set(sheetCounter, sheetCounter+1);  //increment the sheet number
Patch(UserTimesheets, If(IsBlank(Lookup(UserTimesheets, StartsWith(UserEmail, User().Email)).ID), Defaults(UserTimesheets), Lookup(UserTimesheets, StartsWith(UserEmail, User().Email))), {LastTimeSheetNumber:sheetCounter})

This will increment your sheet counter, then patch that number back to the list for that user.

The if statement is in that formula so that if there is no record in the list for that user, it will create one, if there is, it will update it.

That should give you a running count of sheet numbers from that point forward.

I hope that is clear and helpful.

[JD2301]

Hi @RandyHayes ,

Amazing, thank you so much!!