When you set up a sequence number, it will go on indefinitely. If you want your sequence numbers to reset or be restricted to a particular year, you need to follow this guide.

Resetting a Sequence Number for the Calendar Year

Start a new section in your form. You can title it Sequence Number Logic or anything else you'd like. 

1. Create a number field called PreviousYear and enter this formula:

Year(Requested_Date)-1


2. Create a number field called CurrentYear and enter this formula: 

Year(Requested-date)

3. Create a text field called Previous_Sequence_Num with this formula:

DMAX(self.SequenceNo,self.CurrentYear=PreviousYear)

4. Create a text field called Current_Sequence_Num with this formula:

SequenceNo


5. Create a number field called Final_Sequence_Num with this formula:

IF(Previous_Sequence_Num>0,Current_Sequence_Num-Previous_Sequence_Num,Current_Sequence_Num)

Here's what that whole section looks like:


Now, back in your main section, you can create the text field that will display the actual sequence number. Enter this formula:

CONCATENATE("PO/",YEAR(CreatedAt),"/",TEXT(Final_Sequence_No,"00000"))

The PO here stands for Purchase Request as an example. The output of this will be something like: PO/2017/00001. This sequence number will reset at the start of every year. 

Before you publish, go to Change Permissions, click on All Tasks on the left menu, scroll down to the section called Sequence Number Logic, and mark it as Hidden.

Resetting a Sequence Number for a Fiscal Year

Here are some instructions for if you want the sequence number to reset on a day that is not Jan 1 to align with your fiscal year. This example will be for a fiscal year from April 1 - March 31.

1. Create a new section, the same as the previous example.
2. Create a number field called Requested_Month. Enter this formula:

MONTH(Requested_Date)

3. Create a date field called Start_Date and enter this formula:

IF(Requested_Month>=4,DATE(YEAR(Requested_Date),4,1)) 

This shows the start of our financial year is April 1st. If you wanted the fiscal year to start in July, enter this formula:

IF(Requested_Month>=7,DATE(YEAR(Requested_Date),7,1)) 


4. Create a date field called End_Date with this formula:

DATE(YEAR(Start_Date)+1,3,31)  

This shows that the financial year ends on March 31st. Adjust the 3 and the 31 to suit your needs.

5. Create a text field called Previous_Fiscal_Year with this formula:

CONCATENATE(YEAR(Start_Date)-1,"-",YEAR(End_Date)-1)

6. Create a text field called Current_Fiscal_Year with this formula: 

CONCATENATE(YEAR(Start_Date),"-",YEAR(End_Date))


7. Create a text field called Previous_Seq_Num with this formula:

DMAX(self.SequenceNo,self.Current_Fiscal_Year=Previous_Fisical_year)


8. Create a text field called Current_Seq_Num with this formula:

SequenceNo


9. Create a text field called Final_Seq_Num with this formula: 

IF(Previous_Seq_Num>0,Current_Seq_Num-Previous_Seq_Num,Current_Seq_Num)


Here's what that whole section will look like:


Now, go back to your main section and create a text field for your displayed sequence number (just like the previous example). Enter this formula:

CONCATENATE("PO/",YEAR(CreatedAt),"/",TEXT(Final_Sequence_No,"00000"))

Again, the PO prefix is for a Purchase Order. You can structure this any way you'd like. 

Remember to go to the Change Permissions tab to hide your logic fields.

Did this answer your question?