Introduction

It is a common scenario to have an auto increment column for automation projects like generating unique id for projects, invoices, purchase order etc. In most cases, the requirement is to have an increment value based on fiscal year. The out of the box auto number data type in Dataverse does not give flexibility to increment number based on Fiscal year but it works keeping entire table sequence into consideration. Auto number data type also does not allow to reset the sequence number once it is created.

In this article, I cover the detailed steps to set up a custom auto number with Fiscal Year based incrementing using an automated cloud flow.

Scenario

Let’s take an example of a project table having 2 columns: Title (Primary name column) and Project# (single line of text). Column ‘Name’ contains the project title and ‘Project#’ will store the custom auto number value.

Solution

1. Create an automated flow and choose ‘When a row is added, modified or deleted‘ from Dataverse connector as the trigger:

Click ‘Create’ button to proceed.

2. Enter or select the input parameters in the trigger:

Change Type: Added
Table name: Projects
Scope: Organization

3. Add Dataverse “List rows” action to get the value project# of the last record submitted in Project table:

Table name: Projects
Filter rows: crfc6_project ne null
Sort by: createdon desc
Row count: 1

Note: You need to use logical name of the Project# in the filter query. To get the logical name, click ‘the ‘Project#’ column in columns tab. Scroll down in the right panel and get the logical name:

4. Add a condition to verify the length of output of ‘List rows’ value object. The length will be zero for first record in the Project table:

Expression used in the left side operand:

length(outputs('List_rows')?['body/value'])

In true branch (left side), add an action to update a row in Project table. Using expression, we will add the current fiscal year with 1 as the sequence number. I have used ‘PO’ as the prefix but you may take any prefix as per the requirement or skip adding prefix in the custom auto number column:

Table name: Projects

Pull Project id unique value dynamically from the trigger:

Use the below expression in the custom ‘Project#’ column:

concat('PO-',utcNow('yy'),'-',formatNumber(1,'000'))

Note: I have considered 3 digits for sequencing but you may change as per your requirement by changing the formatNumber second parameter. For example: if you want 4 digits number then use formatNumber(1,’0000‘)

5. In false branch (right side) of condition action, we have to add logic to verify the year value in last record with the fiscal year and accordingly change the sequence number. To add this logic, we will use expressions to split last project# by hyphen ‘-‘ and compare the middle element with the current fiscal year.

To achieve this, first add “Compose” action to split last project# value by hyphen and collect in an array:

Expression used in the compose action:

split(first(outputs('List_rows')?['body/value'])?['crfc6_project'],'-')

Note: In the above expression, crfc6_project is the logical name of the Project# column.

Now, add another “Compose” action and rename it as ‘Compose-Fiscal Year’. We will use expression to compare the fiscal year in the existing project# and the current year:

if(equals(outputs('Compose')[1],utcNow('yy')),outputs('Compose')[1],utcNow('yy'))

Post that, add another “Compose” action for the sequence number. Rename this compose action as ‘Compose-Sequence No’. In this action using expression, we will check if year in existing project# is same as fiscal year. If year is same then we will increment the number else we will reset it to 1.

Enter the below expression in the ‘Compose-Sequence No‘ action:

if(equals(outputs('Compose')[1],utcNow('yy')),formatNumber(add(int(last(outputs('Compose'))),1),'000'),formatNumber(1,'000'))

Now, we have everything to update the custom auto number ‘Project#’ column.

6. Lastly, add Dataverse ‘update a row’ action to set value in custom auto number column. We will use expression to join the values in compose actions:

Table name: Projects

Dynamically pass the value of project id column from the trigger. Enter the below expression in Project# column:

concat('PO-',outputs('Compose-Fiscal_Year'),'-',outputs('Compose-Sequence_No'))

The overall structure of the flow will look like:

Output

When there is no record in the Project table. For the first record, the flow executed successfully and set the auto number based on the fiscal year and set the sequence number to 1.

When a new record is added in the same fiscal year, the custom auto number column is incremented by 1:

I manually updated the Project# column value to have value of past year ’24’ and added a new record ‘Project 3’. The flow reset the sequence number and current fiscal year in the auto number column:

Conclusion

Following are the takeaways:

• Automated cloud flow can be used to set custom auto number column in Dataverse table
• Custom logic to generate auto increment column can be achieved using expressions
• The sample flow generates the auto number for a table in Dataverse with Fiscal Year based incrementing
• This technique can also be reused in generating pattern-based auto increment values for invoices, project, purchase order etc. and other automation scenarios