Prefix auto-number with date

[Rescue]

I searched and didn't find any threads trying to do what I am trying to accomplish. I have a table that gets it's order data from a form page. I'd like to auto number each order, prefixed with the date. Currently each order has an order number like this: 00001, 00002, etc. I'd like the table to auto number the orders like such: 2019031001, 2019031002, 2019031003, etc. 

Any help or suggestions is much appreciated.

 

Joey 

[Alison]

Hello @Rescue,

You can achieve the desired workflow by creating a unique Formula field in the table and put the following formula there:

 Convert(nvarchar, [@field:Order_Date], 112)+Convert(nvarchar, [@field:Order_Number]),

where you should change the fields [@field:Order_Date] and [@field:Order_Number] to corresponding ones in your table.
The 112 number is a type of the date format and how the date will be displayed. For example, 112 is yyyymmdd, 111  is yyyy/mm/dd. The full list of the formats you can find here: https://www.w3schools.com/sql/func_sqlserver_convert.asp

 

 

[RuisiHansamu]

Hello! i would like to share my formula that worked on my end. In my Table, I have an AutoNumber field and Date field, and the goal is to concatenate the value from the ID and the date in one field. 

So, I learned to add a formula field in the Table level. I used the CAST to translate the ID to text and CONVERT to convert the Date to Text so that the formula will be valid. 

Here is the formula below: 

CAST([@field:ID] as VARCHAR)+' '+ CONVERT(VARCHAR(10), [@field:Today], 101)

ID: 

Date & Formula field results: 

I hope this helps!