Tip #1127: Don’t update auto number field

Locked boxes with the engraved numbersWe did mentioned in the past that most of the good inventions are driven by the laziness. Auto numbering the new records is one of those. Instead of manually looking up the next available number, calling other users to see if they have any intention of creating a new record in the next few minutes, and placing dibs on that number, users wanted things to be numbered automatically. So deep was the desire that Dynamics 365 developers went a long way to add this feature. Over the years we’ve had our share of on-premises hacks, both free and commercial solutions that used variety of techniques: slap-slap-slap™, plugins, workflows, external locking services, to name but a few. Finally, the giant woke up and gave us the built-in auto number feature.

The pressure to release was high and some sacrifices were made:

Currently, you can add the attribute programmatically. There is no user interface to add this type of attribute.

Thanks to XrmToolBox Autonum plugin, we can live with that because it’s out of the box, fast and, unlike homegrown brews, has neither deadlocks nor duplicate numbers:

The sequential segment is generated by SQL and hence uniqueness is guaranteed by SQL.

All good? Can we go home now? Well, yes, but then you’re going to miss out on the explanation why Elaiza “Mother of Filipinos” Benitez was having an issue with auto number field not generated for records created via portals.

The most important thing to remember about the auto number field is that it is not new type of a field, it’s simply an additional AutoNumberFormat property that can be defined for strings with the format Text. In fact, as per documentation

You can modify an existing format text attribute to be an auto-number format.

What that means is that all other features that have nothing to do with auto numbering, still apply. The attribute can be optional, and the value can be inserted and updated manually. The only feature that AutoNumberFormat property adds to the attribute is that, when the new record is created and the value for the attribute is not specified, system will generate the next unique value using the defined pattern and the next sequence number. That’s it. For example, if the next sequence number is supposed to generate 00042, it will do so even if you already have a record with that value (say, created manually beforehand).

What about the portals? Portals, by design, will submit any attribute that is present on the form, unless it is read-only. Even if it’s hidden. If attribute value is not provided, portal will set it to null. Autonumbering will respect the explicit value and won’t generate anything, leaving it null (blank). As Jonas “The Shuffler” Rapp has succinctly put it

The numbering feature is designed to respect explicitly given values, even when it is null.

The solution the the portal drama is to either make auto number read-only, or completely remove it from the form.

Here is some of the ideas you may want to consider when using an auto numbered field:

  • Decide what to do with the existing records where the value may exist or is null if attribute is new. If you want to use the generator to update the values, how about repeatedly creating new records, getting the attribute value back, deleting those records, and then using the generated values to update the existing entries.
  • Add an alternate key based on that attribute, if you want to guarantee the uniqueness
  • Set the field to read-only on all the forms
  • Add field-level security to the field and set it as read-only for
    everyone (note: won’t work with alternate keys)
  • Create plugin on pre-validation stage, verify if attribute is present and either stop the create or drop the attribute (which should kick in the generator) (NOT TESTED, just a thought!)

Thanks to Nick “Benchpress” Doelman for some of the ideas for the tip.

Facebook and Twitter cover photo by Tim Evans on Unsplash

Leave a Reply

Your email address will not be published. Required fields are marked *