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 !