For a lot of projects i have been working on, there is always a request of generating an robust autonumber mechanism depending on specific conditions and guarantee a separate auto-increment to be applied on the same field of an entit. Out Of The Box autonumber capabilities offers a lot but there is no support for such requirements yet.
Real life example will be to generate separate Unique Account Number for Account entity records depending on the selected relationship type : Customer or Vendor.
Proposed Approach : No Code
The approach i am proposing in this blog requires no code and only relays on configuration.
We keep the Account Number in the Account entity as Text field and we define autonumber in the primary field on two new entities that represent each one a separate sequence for the two relationship types we need to cover Customers and Vendors.
When the autonumber is generated, we use it to populate the Account Number in the Account entity.
First, make sure that your account number in the account entity is still in his default data type Text.
Also make sure that the account number is part of your form and is optional and locked to prevent manual entries of users.
Create 2 additional entities called SeqCustomerAccount and SeqVendorAccount.
In Both entities, set the the primary field of the entity Name with the following configurations :
|Autonumber Type||String prefixed number|
|Prefix||C for SeqCustomerAccount entity |
V for SeqCustomerVendor entity
|Minimal number of digits||4|
By setting 2 entities each one with its own auto number we guarantee that the sequences will be incremented independenly when a customers or vendors is created in Dataverse.
Those two entities are for internal use only and should not be appearing in the sitemap of your the Model Driven or Canvas App.
Next create a synchronous workflow linked to the Account entity and triggered in the creation event.
The following screen describe the definition of the Workflow :
What this workflows does, it checks the relationship type and create a records on one of the Seq tables that generate the autonumbers, takes that autonumber and populates it in the account entity record being created.
Since those 2 entities we created are for internal use only, we dont need to persist data generated by the account creation actions. You can thinkg of creating a scheduled Cloud Flow that will be flushing records in both Seq tables generated after a period of time.
With this setup now, you will obtain the following result for the creation of the 4 accounts :
Just In Case
Also a common thing that i always forget, is to setup create priviliege for the 2 Seq tables we created in security roles that has permission to create accounts. This will prevent you from having a ugly permission denied errors.