Views:

Scenario Overview

In this business scenario the requirement is to generate an Auto-Number on the Account entity. The specific requirement is that the first 3 characters of the account name is used as a prefix and that when generating the AutoNumber we need to count the number of existing prefix records & increment by one. 

Below is an example of what we require,   

                                                   Account Names
                                                   Microsoft - Ireland
                                                   Microsoft - UK
                                                   Microsoft - USA
                                                   Microsoft - Canada

                                                   Account Numbers
                                                   MIC-0001 
                                                   MIC-0002
                                                   MIC-0003
                                                   MIC-0004

Note1:
As always whenever we are using the North52 Auto-Number formula type we are guaranteed consistency when generating Auto-Numbers so no duplicates will be created even with multiple application servers for any of CRM Online, OnPremise or Partner Hosted.

Note2:
It is assumed that the 'Account Number' field is read-only, accounts are never deleted (only deactivated)  & users cannot change the field.

N52 Formula Manager Solution

The Formula Manager solution works like this,

  • Execute on the create of Accounts within the system
  • Have a formula that generates the auto number requirements
  • This formula executes a fetch-xml query passing in the prefix to get the existing count & adds one
  • Have the formula place the result of the formula into the Account Number field of the Account entity 

N52 Formula Manager Steps

Formula Steps

  • Create a new formula of type 'AutoNumber'
  • Set the Source & Target entities to 'Account'
  • Set the Source & Target Property fields to 'Account Number'
  • Copy & paste the formula description below into the formula description field & click save 

Formula Detail Steps

  • Scroll half way down the formula & click to add a new formula detail record (i.e. add the fetch-xml query)
  • Set the Name to 'GetCurrentAutoValue'
  • Copy & paste the fetchxml below into the query field & click save
  • You are now ready to test

Formula

 Upper(Left([account.name] ,3)) + '-' +

 PadLeft(FindCountFD('GetCurrentAutoValue',
                                     'accountnumber',
                                     '0',
                                      true,
                                      SetParams( Upper(Left([account.name] ,3)) + '%' ))
                                      +1,
               4,'0') 

Fetch-Xml Description

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
      <attribute name="accountid" />
      <attribute name="accountnumber" />
        <filter type="and">
            <condition attribute="accountnumber" operator="like" value="{0}" />
    </filter>
  </entity>
</fetch>