Power Apps – How to do reference numbers with dates and serial number

Introduction:

We will create a set up to demonstrate a working reference number where the serial number will reset based on change of either the day, month, or year. This tutorial uses a collection for demonstration purposes only.

Step 1:

Insert a dropdown named Dropdown1. Change the following properties:

Items property

["Daily","Monthly","Yearly"]

Step 2:

Insert a date picker named DatePicker1 beside Dropdown1.

Step 3:

Insert a button named Button1 beside DatePicker1. Change the following properties:

Text property

"Submit"

OnSelect property

With({lastRefNumber:First(Sort(Filter(collRefNumbers,
StartsWith(Value,Text(DatePicker1.SelectedDate,

Switch(Dropdown1.Selected.Value,
"Yearly","yyyy",
"Monthly","yyyymm",
"Daily","yyyymmdd"
)

))),Value,SortOrder.Descending)).Value},

If(
    IsBlank(lastRefNumber),
    //if this is the first ref number, create the first ref number by default
        Collect(collRefNumbers,{Value:Text(DatePicker1.SelectedDate,"yyyymmdd") 
        & "0001"}),
    //otherwise, add the last ref number by 1 to create the new one
        Collect(collRefNumbers,{Value:Text(DatePicker1.SelectedDate,"yyyymmdd") 
        & Text(Value(Right(lastRefNumber,4))+1,"0000") })
));

Step 4:

Insert a button named Button2 beside Button1. Change the following properties:

Text property

"Clear"

OnSelect property

Clear(collRefNumbers)

Step 5:

Insert a blank vertical gallery named Gallery1 below the controls that you have inserted. Change the following properties:

Items property

collRefNumbers

Default property

Last(collRefNumbers)

TemplateSize property

100

Step 6:

Insert a label inside Gallery1 named Label1. Change the following properties:

Text property

With({
    refYear: Left(ThisItem.Value,4),
    refMonth: Mid(ThisItem.Value,5,2),
    refDay:Mid(ThisItem.Value,7,2),
    refSerial:Right(ThisItem.Value,4)
},
//Format the reference number here
refSerial & "-" & refDay& "-" & refMonth & "-" & refYear
)

Conclusion:

To test it out, select “Daily” from Dropdown1. Click the submit button 3 times. Then change the date in DatePicker1. Click the submit button again 3 times. You will notice the serial number resets to 1 when the date changes.

To change how the format of the reference number looks like, modify the Text property of Label1.

If you want to set when the serial number will reset, change the selection of Dropdown1 to either Monthly or Yearly.

This is just to demonstrate how incrementing reference numbers work. You may apply this to your data source and replace the DatePicker1 date with the date today.