Auto-Populate Request Number in a Power Apps Form

[tagustin2020]

@RandyHayes 

Hello Randy,

I am working on a Shipping Request Power App. I have received a request to auto-populate a sequential req number in the form when the New Request Button is clicked. I have two data sources. The primary list that all form data is written to and a separate list for Shipping Request Number. How do I do a lookup on this list when the New Request button is clicked?

Screenshots are attached in the Word doc. Thank you in advance for your help. Teresa

[RandyHayes]

@tagustin2020 

A word of caution on this...you can quickly run into issues with concurrency when you try to pre-assign a number that needs to be sequential and unique.

If one user starts to create a new request and gets 10001 and then another user at the same time attempts to create a new request, they will both get 10001.  In fact, without refreshing your sequential list all the time - if you don't then everyone will get 10001.  

This is one (among other) reason that you cannot assign an ID to a new record.  You will get one when you create a record, but not before.

So, you have a couple of choices:

1) Skip providing the request number at the time of entry and rely on the ID and a combination of some logic to create the request number you really want.

2) Employ a flow to assign the number after submitted. 

3) Employ some "locking" logic to the sequential number in your list so that it is not used by others.  This will still involve a lot of refreshing of the datasource - very expensive.

So, getting the number is relatively easy to do with just a Lookup on the datasource, but given the above...is this a route that you want to take?

[tagustin2020]

@RandyHayes 

Hello Randy,

Thank you for your advice. I found a video tutorial on how to create a custom ID number in Power Automate that is based off of the internal SharePoint ID. It is working, but the number does not immediately populate in the gallery (the rest of the record based on SubmitForm does). I think maybe if I called it from Power Apps and fed the answer back to Power Apps that it would appear more quickly, but I'm not sure how to do that. Can you help me with what steps to add before and after to get it to update real time? Thank you, Teresa

Here is the Flow. The full expression is: add(triggerBody()?[‘ID’],10000). Company is what I renamed the Title column. Let me know if you need any additional info.

[RandyHayes · Accepted solution]

@tagustin2020 

So you're going with a combination of #2 and #3.  The first part is to assign the number in flow, but in order to then get that updated in your datasource, you'll need the expensive refresh on the record...and you'll need to get that after the flow completes.

<I'm going to ramble here for just a bit...>

I will throw one other thing out there...sequential numbering is a thing of the past!  It is based on the old paper system where each sheet of a form was numbered sequentially.  Somehow that ancient principal has perpetuated even into today, but, and I only mention this to say...stop!  Do you really need a sequential number?  If there is some essential business process that requires it, then you have no choice.  But, if there is not, then why try to continue it?

We abandoned sequential numbering in all our systems and apps over a decade ago because it really had not informational value.  What we went with was more of a date-based numbering such as yymmddhhmmss.  Why this? Because it actually provides value.  Unlike a sequential number such as 10030 that provides no real information, this at least provides an exact time of when the artifact was created. If I pick up an item that 10030 on it, I have no idea if this is old or new or what unless I look it up.  If I pick up an item that has 1902071233 on it, I immediately know that this is two years old.

In most cases we only trail down to the seconds in high load apps.  If there is very minimal risk of a person creating two within the same minute, we skip the seconds.  Likewise if there is not chance of two records being created in the same hour, se skip the minutes...and so on.

This provides not only valuable information from the unique ID, but it also plays VERY nicely with PowerApps as there is no flow needed nor and issues with refreshing, writing back, etc.  Just submit and the value exists.  Done.

Okay...</end rambling here>

Now, back to the story...I believe you might have misunderstood my options (and I might have mislead in number 2).  I gave those for the purpose of specifically meeting the requirement of displaying the Sequential ID AT the time the user is creating the item - that seemed to be what you wanted.  And looking back, #2 would not technically have done that (although for some reason that was in my mind).

So, that brings me to the conclusion that since you chose flow,  that showing the Sequential ID at the time the user enters the new record is NOT that important.  

In that case I would very much discontinue the use of flow for this.  You will cost your app more performance issues and complexity than is necessary.

Instead, simply change your OnSuccess action of your form to the following:

   UpdateIf(yourList, ID=Self.LastSubmit.ID, {'Request Number': Self.LastSubmit.ID + 10000})

That will do the equivalent of the flow without the cost.  And your Gallery will immediately reflect it without any further action.

Keeping in mind, that this is based on what you are currently trying, which implies that displaying the Request number to the user before the new record is created is no longer necessary.

Beyond that...if you want the option to display the number at creation time, I'd think through my rambling above a bit...it's a real clean and easy solution.  I will add to it, that doing searching on that scheme play really well in PowerApps - ex.  Filter(data, RequestNumber>=2000000000 && RequestNumber<=2012312359) will give me all the records in the year 2000.  Or, Filter(data, RequestNumber>=2006000000 && RequestNumber<=2006302359) would give me all records in June 2020.  etc...

Two birds, one stone!

[tagustin2020]

@RandyHayes 

Hi Randy,

Thank you for helping me out. I love how you are always able to come up with simple, smart solutions. That's why I tag you on everything. I deleted my flow and entered the formula you suggested in the OnSuccess property and it worked great. Since Logistics won't see the request until it is created, I really don't think it is necessary to create a number from the very beginning. I have a meeting with them Tuesday so I'll ask again just to verify there isn't some key concept I'm missing. I really like your suggestion to come up with a more meaningful string of numbers that can be filtered by year or month year as I want to build in some reports for them so I would like to learn more about how to do that. What formula(s) would I have to enter to achieve this?

Kind regards,

Teresa 

[RandyHayes]

@tagustin2020 

Great!  Always here to spark new ways of doing things for the better.  Sometimes we all get stuck in the weeds of the projects we're on and the phrase "it's hard to see the forest from the trees" really applies.

So, for the making of a unique number, I like to just make one up at the time of the New form.  Essentially for the column that will be that value, and usually in a label (or Textinput in view mode - you don't want people to change the number, but you do want it displayed), I would put a formula such as this:

    Coalesce(Parent.Default, Text(Now(), "yymmddhhmm")  (add ss if you expect more than one item to be created in a minute by multiple people)

That should give the unique number.

[tagustin2020]

@RandyHayes 

Hi Randy,

I created a new number type column called AltReqNum and pasted it into the Default property of the Value field:

Coalesce(Parent.Default, Text(Now(), "yymmddhhmm")

It is showing errors. Did I add the formula in the wrong place? What do I enter in the OnSuccess of the form, anything?

Teresa

[RandyHayes]

@tagustin2020 

What is the error message??  If what you just posted is the exact formula that you have in your app, then there is a syntax error - it needs a closing paren.

[tagustin2020]

@RandyHayes 

Hi Randy,

The error is "The function 'Coalesce' has some invalid arguments." I should have mentioned that I tried adding another closing paren before raising the alert.  Thanks, Teresa

[RandyHayes · Accepted solution]

@tagustin2020 

Ah, your column is numeric!  

This will do the trick:

    Coalesce(Parent.Default, Value(Text(Now(), "yymmddhhmm"))) 

I see it was me who forgot the closing paren in the original formula...🤣

With Coalesce, all of the parameters must be the same type.  Since your Parent.Default is numeric, we have to convert the Text to numeric for the generated number.