cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Gaëlle
Level 8

Generate record number based on Last record.. Depending of a form field

Hello there !

I am struggling with a bit of logic here..

 

WHat I have :

I have a "Cases" SP List (turning soon in a SQL Server data table, but that is another story).

In this list, every item has a Title, a description, a closing date, a Production Line and a record Number.

The record number made like that : LL_XXXX

LL being the Production Line (for example, L1 or L2)

XXXX being the historical number, increasing of 1 each time a new Case is opened.

You can have L1_0001 and L2_0001 : they are unique, they are not treating the same subject, because they are the 0001st case opened for L1 and for L2.

At the moment for exemple, my last Case opened for L1 is L1_1532 and for L2 it is L2_1131.

 

What I want :

Here is for the shape of my data.

I am making a simple app which will allow user to create new Cases.

It has Title field ... and Production Line field.

I want to generate my Case number correctly, concatening Production Line (picked up by user) and "_" with the last record number for that particular Production Line.

 

What I did :

So, I have written in the "OnChange" property of the field DataCardValue19 (which is the DataCard Value for Production Line) the following code :

 

UpdateContext({defaultCaseNum:First(SortByColumns(Filter(Cases,StartsWith(Concatenate(DataCardValue19.Text,"_"),recordNumber)), "recordNumber", Descending)).recordNumber})

 

In my DataCardValue17 (which is recordNumber datacard value), I wrote this in the Text property :

If(isNewItem,defaultCaseNum,Parent.Default)

(the isNewItem is set to true and sent when clicking on "Add a Case")

 

I know I may should substring my recordNumber to get only the digits, But I thought that only getting L1_xxxx will be enough.

 

What is happening :

WHen submitting my form, no error is displayed, but nothing happens either. No item is created.

So, i go back to PowerApps, and it is saying "The formula or expression expects a valid operand" on my submit button, plus I have a delegation warning for my StartsWith on my Production Line Datacard...

I am really wondering if the OnChange property of a text field is well taken into account ...

 

Do you have any idea on how I could make it work ?

Maybe @tchin-nin or @v-monli-msft?

 

Thanks a lot !

Have a nice day !

1 ACCEPTED SOLUTION

Accepted Solutions
Gaëlle
Level 8

Re: Generate record number based on Last record.. Depending of a form field

So, I found the solution, thanks to @CarlosFigueira and his awesome post really well explained.

Here is the adapted generation formula :

On my Production Line value (DataCardValue19), i've set the OnChange property like the following :

 

 

Concatenate(DataCardValue19.Text,"_",Text(Value(Mid(First(SortByColumns(
Filter(Cases,StartsWith(recordNumber,Concatenate(DataCardValue19.Text,"_"))),
"recordNumber",
Descending
)).recordNumber,Len(Concatenate(DataCardValue19.Text,"_"))+1,100))+1,"000"))

Did it !

 

3 REPLIES 3
Gaëlle
Level 8

Re: Generate record number based on Last record.. Depending of a form field

So, I found the solution, thanks to @CarlosFigueira and his awesome post really well explained.

Here is the adapted generation formula :

On my Production Line value (DataCardValue19), i've set the OnChange property like the following :

 

 

Concatenate(DataCardValue19.Text,"_",Text(Value(Mid(First(SortByColumns(
Filter(Cases,StartsWith(recordNumber,Concatenate(DataCardValue19.Text,"_"))),
"recordNumber",
Descending
)).recordNumber,Len(Concatenate(DataCardValue19.Text,"_"))+1,100))+1,"000"))

Did it !

 

tchin-nin
Level 10

Re: Generate record number based on Last record.. Depending of a form field

Hi @Gaëlle

 

I would suggestions few things :

 

  • Do not preset your Record number. If 2 users open the form at the same time, you don't want them to have the same record number. I would rather search for the last production lane case at the submit and increment the number at this moment .
  • Do not filter your list with a StartsWith(recordNumber), you'll face delegation issue. You can go for a comparison on the production line instead.

 

I would have a recordNumberDataCard

RecordNumberDataCard.Update : if(EditForm.Mode=New,recordNumberVar,ThisItem.recordNumber)

SubmitButton.OnSelect :
If(EditForm.Mode=New,
UpdateContext({lastRecordNumber:First(Sort(Filter(Cases,ProductionLine=ProductionLineTextBox.Text),ID, Descending)).RecordNumber});
Set(recordNumberVar,Left(lastRecordNumber,2)&"-"&Text(Value(Right(lastRecoreNumber,4))+1,0000)));
SubmitForm(EditForm1)

 

If it's a new form, get the last record number for the selected production line : stored in lastRecoreNumber

Then set the variable recordNumberVar to : the same 2 first char of the last record number (L1 or L2) concatenate with "-" and an incrementation +1 of the four last digits.

Then submit your form, so the recordNumber datacard will push either the existing value for an edition or the variable we defined previously if it's a new item .

 

** Be careful, you have to add extra logic for the very first item created, when the lastRecordNumber will be blank, cause 0 items retrieved . 

 

Something bothering me, the production line seems to be a TextInput, but this should rather be a dropdown list if you want a predifined value ? 

 

I have not tried my formula since I can't access a computer rightnow, just tell me if my thoughts look good to you and how it goes with the formula.

 

Theo

Gaëlle
Level 8

Re: Generate record number based on Last record.. Depending of a form field

Once again, thanks a lot for your time @tchin-nin !

 

For the carefullness about the 1st item ever created : it won't happen, because we are working on a data history.

Yes, you are right for the comparison, I should totally do that instead of StartsWith.

Yes, It will be put into a choice field later (I have to see how i manage the datas, because it should be a dependant dropdown)

 

I will try your formula, but it seems quite fine for me.

Thanks a lot for coming to my rescue !

 

 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 43 members 4,060 guests
Please welcome our newest community members: