Creating a Job Number Based on Date

[stephensigma]

Hello!

I'm looking for some help creating a flow that would create a job number on the opportunity object that would be in the format of YYMM### where the ### would be sequential. So the first job of April 2024 would be 2304001 and the next 2304002, etc.

Any help would be appreciated!

[abm]

Hi @stephensigma 

Which database you are using? SharePoint or Dataverse?

Thanks

[stephensigma]

Hello!

I'm in Dynamics 365 Field Service - so Dataverse I believe!

[abm]

Hi @stephensigma 

Thanks for your reply. I believe you want to generate this number once the opportunity record is created.

Thanks

[abm]

Hi @stephensigma 

Try the below. Below my custom table is called Sports. I have added a column called Job Number which is a whole number.

Above under the trigger settings do the following:

Set the concurrency control limit on and set to 1. This will make sure we always have one run at a time. Otherwise if two people creates the record at similar time we won't be able to get the very latest Job Number.

Next add the below steps

Expression I used above is : 

outputs('List_rows')?['body/value']?[1]?['ajb_JobNumber']

Here I am taking the latest value.  See List Rows 'Row Count' I set to 2. So that means I am ignoring the first record which is the latest record we created and taking the next record.

Then add the IF condition to check null

Then under the Yes condition set the initial value and update the current record

The expression under the compose 2 is as follows:

formatDateTime(utcNow(),'yyMM')

Next under the No condition add the below steps

 Above Compose 4 use the following expression for getting the next number by incrementing to 1.

add(outputs('Compose'),1)

Finally you have the IF condition looks like this:

[stephensigma]

Hello! This makes sense and works well to increment. I very much appreciate your help!

However, how will it reset to 001 again next month? Right now it will increment through all of April, but won't it continue to increment when we switch to May? I don't understand where it resets to 001 again when the month changes.

[abm]

Hi @stephensigma 

You didn't said in your initial requirement. 'So the first job of April 2024 would be 2304001 and the next 2304002, etc.' There are quite a few changes to do now.

formatDateTime(utcNow(),'yyMM') and construct with 001. Then compare with the existing value before the current increment step and if its greater then use the reset value using formatDateTime(utcNow(),'yyMM') with 001.

Thanks

[stephensigma]

Hello - thank you for the response. I apologize, I wasn't very clear in my prompt on the reset.

I'm confused on how you're comparing the current step against the month format. Is is possible for you to show an example? I appreciate your help so far very much!

[eliotcole]

Have you considered turning the dates into a ticks() value, @stephensigma, by using said function in an expression?

If you married that, with any given item number from the source, it would surely ensure a unique job number.

Or if you wished to ensure uniqueness without that you could filter existing records by the trim() result and if there are any matches increment a '-0' at the end of the string. 🙂

[abm]

Hi @stephensigma 

Please follow the below steps: I did modified the steps which I provided earlier. Have a look and comment.

Above compose expression is:

outputs('List_rows')?['body/value']?[1]?['ajb_JobNumber']

This way we are getting the latest job number

Above we are checking if the value is not null then increment or set to 001

Compose2 expression. Once you added the below expression append with 001. See Compose 2 above.

formatDateTime(utcNow(),'yyMM')

Compose 4 expression.

add(outputs('Compose'),1)

Next we need to add another IF condition to determine whether it's a new month by checking the current value which we generated and extract the month using substring() and comparing with todays Date month value

Above expressions under the IF condition is

substring(variables('JobNumber'),2,2)

formatDateTime(utcNow(),'MM')

Compose 3. Add the below expression and at the end append with 001

formatDateTime(utcNow(),'yyMM')

Finally you can use the variable job number to update the row to set the newly generated job number.

Remember to set the concurrency control to 1.

Let me know how it goes.

Thanks