Friday, October 6, 2017

Auto Number attributes in Microsoft Dynamics 365

With the release of Microsoft Dynamics 365 Customer Engagement October service update (v9.0), two of the most requested customization features were finally implemented in the platform:

  • Multi-select optionsets
  • Custom auto numbered attributes
The first one has full customization features available in the UI out of the box, but to create and manage auto numbering attributes you have to write code utilizing SDK-functions.

This article will describe how to use the Auto Number Manager tool for XrmToolBox giving an intuitive UI to access SDK-only features for auto numbering.

image

Auto Number Features

The Auto Number attributes support three different types of placeholders – sequential numbers, random text, and date/time in different formats.

Syntax

Sequential number:

  {SEQNUM:n} where n is the minimum number of digits.

Random text:

  {RANDSTRING:n} where n is the number of characters in the random string. Maximum value for n is 6.

Date/time:

  {DATETIMEUTC:fff} where fff is a standard datetime format string. See documentation.

Other text:

    Any other text may be included in the number format. This text will be used as is.

Sample auto number formats:

  Number Format                                Example value
  CAR-{SEQNUM:3}-{RANDSTRING:6}                CAR-123-AB7LSF
  CNR-{RANDSTRING:4}-{SEQNUM:4}                CNR-WXYZ-1000
  {SEQNUM:6}-#-{RANDSTRING:3}                  123456-#-R3V
  KA-{SEQNUM:4}                                KA-0001
  {SEQNUM:10}                                  1234567890
  QUO-{SEQNUM:3}#{RANDSTRING:3}#{RANDSTRING:5} QUO-123#ABC#PQ2ST
  QUO-{SEQNUM:7}{RANDSTRING:5}                 QUO-0001000P9G3R
  CAS-{SEQNUM:6}-{DATETIMEUTC:yyyyMMddhhmmss}  CAS-002000-20170913091544
  CAS-{SEQNUM:6}-{DATETIMEUTC:yyyyMMddhh}      CAS-002002-2017091309


Installation

Update! XrmToolBox now supports SDK v9.0, so installation of Auto Number Manager is done through the Plugins Store in XrmToolBox.

image

The Auto Number Manager will be available in the Plugins Store of XrmToolBox once the SDK for Dynamics 365 v9.0 is released and XrmToolBox delivered using that version.

Until then:

  1. Go to the Releases page on the GitHub repository: https://github.com/rappen/AutoNumManager/releases
  2. Download the ANM zip file (not the source code)
  3. Right-click the downloaded file and select Properties
  4. Tick the “Unblock” checkbox and click OK
  5. Open the zip file and copy all files and folders
  6. Open the folder where XrmToolBox is installed
  7. Paste the files from the zip
  8. Start XrmToolBox

The release package contains two SDK assemblies for v9.0, and a folder Plugins that contains the ANM plugin assembly.

Note: Information on how to install and run XrmToolBox, read the wiki: https://github.com/MscrmTools/XrmToolBox/wiki


Running ANM

Make sure you are connected to an organization with version 9.0 or above. Starting ANM connected to older versions will display an error message.

image

Opening ANM will load existing unmanaged solutions. Select the solution where you want to manage your auto number attributes.

Selecting the solution will populate a list of entities included in the solution.

image

Select an entity, and any existing auto number attributes will be show in the table below.

To create a new auto number attribute, click button New Attribute.

image

  • The prefix of the Logical Name is taken from the publisher of the selected solution, and cannot be changed.
  • Enter Logical Name, Display Name and Description for the attribute.
  • Make sure the Maximum Length is large enough to hold the generated auto number value. If the resulting string is too long, users will not be able to create new records.
  • The Language Id is retrieved from the current user, and is used to define for which language the Display Name and Description shall be created.
  • Number Format is the property where the magic is defined. See syntax and samples above. At the time of writing this article, it is somewhat unclear whether the SEQNUM placeholder should be required, but as this is the most probable definition, this is also implemented in ANM. Clicking the blue shortcuts below the number format will help composing the format.
  • The Seed property can be used to define the next value to be assigned to a new record for the SEQNUM placeholder of the auto number format. The seed can be set when creating a new auto number attribute, and when updating existing attributes.
  • To make it easier to create the Number Format, a Sample Number of the resulting values is displayed on the form.
  • Below the sample number a help text is displayed indicating if the Number Format is correctly formatted, or if there are any errors. This help text will let you know if the Maximum Length property is too small, as well as format errors.


The Techy Stuff

Metadata

Technically this is not a new attribute type. It is the same old “Single Line of Text” data type, with format “Text”. What has been added is a new property AutoNumberFormat on the StringAttributeMetadata class. This would indicate that existing attributes, both custom and standard, could be decorated with this new property to activate auto numbering for existing attributes. Initial tests succeed in doing that, but this is most probably a missing constraint in this version of the SDK, and should not be possible. ANM does therefore not support this action.

Stage

The platform core assigns the automatic numbers by an internal plugin at stage Initial Pre-operation (5). This means that custom plugins of all stages can read the assigned number. Another result of this is that custom plugins have no way of really knowing how the value was assigned – by the internal numbering or by programmatic creation of records.

Sequence counter

The technology behind the actual “counting” is based on the SQL Server SEQUENCE feature. There is currently no way of reading the current or last assigned value of the sequence, other than reading the most recently created record of the entity, and parsing the value of the numbered attribute.

Seed

The seed can be set to update the current value of the sequence number. This will, naturally, only affect records created after the seed has been updated. It MAY result in duplicated numbers, if the seed is set to a lower value than current sequence position.

Random characters

At this point it is not possible to define which character set the random string is generated from. This is entirely controlled by the core plugin assigning the auto numbers. But we sure do hope the SDK will be updated to allow us to define exactly which characters that can be included in the random strings!


Read more

After investigating the Auto Number features extensively, I wrote about some More Auto Numbering features.


Links

27 comments:

  1. I tired installing this Auto Number Manager tool for XrmToolBox but I get the below warring when adding from the plugins store. I have installed the latest version of XrmToolBox (v1.2017.7.18).

    "This plugin implements features form latest version of XrmToolBox. Please update your XrmToolBox to latest version to be able to install the plugin version."

    Please advise on possible cause of this warning and ways to troubleshoot it.

    ReplyDelete
    Replies
    1. Hi Patel,
      The official SDK for v9.0 is not released yet, so installation through XrmToolBox (that still use SDK v8.2) is not possible, that is why the warning is displayed.

      This is not mentioned in this article, but on the ANM webpage http://anm.xrmtoolbox.com you can read the following on how to install it before SDK and XTB is updated:

      NOTE!
      Until SDK for v9.0 is released and XrmToolBox updated to use it,
      beta version of Auto Number Manager can be downloaded from https://github.com/rappen/AutoNumManager/releases

      Let me know if this works or not :)

      Delete
  2. Hi Jonas,

    I have installed ANM successfully and it works just fine.
    I'm using ANM with my Product entity and it will be used to automatically update the Product IDs when a record is created.
    I have installed the new attribute and ANM has created a new field and it is on my main form for the product entity. When I’m creating a new product, the new field works fine.
    So long, so good ��
    Inside the CRM there is the originally field for Product IDs on my existing products. The product IDs is filled with different values and there is no connection between the different product IDs. For example: one product ID is: Office 365 and next is PO-125499. Obviously, no cohesion between the existing product IDs.
    I’m using this number format: AA-{SEQNUM:5} with seed 1. So the structure are: AA-00001, AA-00002, AA-00003 and so on.
    So, my question is:
    How can I update all my existing products that already has the originally product ID into my new product ID structure on my new field?
    Best regards, Mathias

    ReplyDelete
    Replies
    1. Hi Mathias - so sorry about slow response time...
      The build-in feature for auto numbering only supports assigning numbers when records are created.
      So I guess you would have to write code to reassign numbers for existing records, and then reset the seed (current number) to make sure you do not get duplicates.

      Delete
  3. This comment has been removed by a blog administrator.

    ReplyDelete
  4. Hi,

    I created one autonumber field to a custom entity. But when I import the solution to other CRM instance (v 9.0) auto number field does not work on new instance. Am I missing something? Where does/how does CRM manages the auto-calculation/auto-numbering...

    Any suggestion ?

    ReplyDelete
    Replies
    1. Microsoft have inexplicably not included the auto number format property in the customizations file when exporting a solution.
      This has been reported as a bug, I currently don't have information on when it will be fixed. But please open a support ticket for this - the more tickets they get, the faster it might get prioritized.

      Workaround for now is to apply the same format in target environment after solution is imported.

      Delete
  5. HI Jon,Thanks for article and I tried through XRM-ANM created date field farmat and updated in my custom entity when I create on new record to create account .Its updating on save not on onload of record creation ..Any help much appreciated ....I working on crm365

    ReplyDelete
    Replies
    1. Hi,
      This is expected behavior, the Auto Numbering feature works server side, so it will generate the number according to the format when you save the record.
      /Jonas

      Delete
  6. Hi Jonas, the text field created is not searchable neither is the option to make it searchable editable, is this a bug?

    Paul

    ReplyDelete
    Replies
    1. Hi Paul,
      Yes there is a bug that Microsoft have identified and fixed, it is waiting for release and might actually have been released if you are running the latest version online.
      Note that the bug is not in or caused by Auto Number Manager, it is a bug in core D365.
      There is an issue in the ANM repo regarding this, please update there if you have anything to add! https://github.com/rappen/AutoNumManager/issues/6

      Delete
  7. Hi Jonas,
    I am a bit confused as to how is the seed property set. I have created an autonumber field with a {SEQNUM:10} format and it defaulted to a seed of 1000. Then I used your tool to set the seed to 0. I have noticed you are actually replacing the SEQNUM:10 to something like seed:000..00, but if I am trying to create the field directly with this format, it does not seem to work.
    Regards

    ReplyDelete
    Replies
    1. Hi Dark
      That sound strange... I just tried creating a new attribute with {SEQNUM:10} and no seed. Created a record, it got "0000001000". Updated the attribute with seed 0. Created a record, it got "0000000000". Created another, it got "0000000001".
      I don't quite get it what you mean that the number format is changed to "seed:000:00".
      Did you use this tool to create the number attribute from the beginning, or another tool?
      /Jonas

      Delete
    2. Well, I wanted to check how you are setting the seed property and checked your code on github. Just looking thru the code I reached the point where you're setting up the seed and saw that you're replacing the format with the seed from the user followed by 0 replicated n times (based on initial format).
      Basically my question is: how do you set up the seed when first creating the autonumber field so it doesn't default to 1000?

      Delete
    3. I think you are looking at the code where a sample result is composed.
      The setting of seed is done in this code.
      To set the seed to a specific value when creating a new attribute I just make sure to enter it in the Seed field here: see image.
      Hope this helps :)

      Delete
    4. So there was a specific request for this :). I did look through the classes in the sdk.messages namespace but couldn't find anything relevant.
      Thank you for your guidance!
      Cheers

      Delete
  8. Hi Jonas, great tool and thanks for creating it! I have it all working correctly on several entities, however the problem I face is every one of those attributes is now "hidden", in that I cannot add them to a form or a view? I found a workaround by creating the field, adding to a form, then enabling autonumbering. It doesnt look happy about being on the form afterwards but it does stay! Have you seen this behaviour before?

    Thanks,
    Nick

    ReplyDelete
    Replies
    1. Hi Nick - and thanks :)
      Which version (build) are you on?
      There was a bug (see issue #6) in the platform that was fixed with build 9.0.1.621 that caused this symptom.
      /Jonas

      Delete
    2. Hi Jonas, we are on version 9.0.1.621, yet I still cannot see my autonumber fields to add to a view/form? Are there any steps I have to carry out now I am on this version? Thanks, Nick

      Delete
  9. Hi Nick, you shouldn't have to do anything else to see the attribute...
    Have you tried just creating another one to see if that works?
    Also verify that both app and db versions are on build 621 or later, I have seen situations where only the app was updated.

    ReplyDelete
    Replies
    1. Hi Jonas, both the app and DB are on 621 (Version 1612 (9.0.1.621) (DB 9.0.1.621) - I will try creating another autonumber attribute and see if that now works. Thanks, Nick

      Delete
  10. Hi Jonas, thanks for your help. It appears because I created the field while the Microsoft bug was in existence, it effectively broke all of my auto number fields, and I couldn't reference them anywhere. If I recreate them now, they all show correctly! Thanks, Nick

    ReplyDelete
  11. Ha, I have not been quite so luck in implementing the fix, I cannot delete the attribute in Dynamics as I get a Generic SQL Error, I cannot remove autonumbering from the field in Auto Number Manager (it allows me and says successful, but when I go back to it it still has all the Autonumber seed properties.). When I try to delete the attribute through Auto Number Manager I get "System.Data.SqlClient.SqlException (0x80131904): The index 'ndx_QF_inc_referenceid' is dependent on column 'inc_referenceid'.

    Transaction (Process ID 169) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

    ReplyDelete
    Replies
    1. This calls for a support ticket with Microsoft, unfortunately... :(

      Delete
    2. Indeed, I am one step ahead on that one! Just thought i would mention here in case it helps anyone else in the same boat. Thanks once again for your help.

      Delete