Good Idea? Auto-Numbering Import Set Runs

[JosephW1]

The sys_import_set_run table has a number field. What's more, its display field is its Number field. Now, what's curious is that it has auto-numbering disabled, apparently causing both A) its number field to be unmaintainted/empty, and B) all references to import set runs - like the references found on import_set_row records - to display as (empty) since this display column is intentionally left blank.

This makes a few tasks rather confounding.

1. Reading import set row queries.
   1. Have you ever wondered why that query claims the set is empty yet, if you do a "import set run > is empty" query you get nothing? Quite misleading, is it not? Sure, it's trivial, but a misleading design can cause issues.
      
2. Troubleshooting import sets that span multiple runs.
   1. This is an interesting vendor job. It processes a multiple-hundred-thousand-row import set with thousands of one-row transforms; one row per import set run. This related list is completely incapable of hinting at that, however, since the diverse runs are not revealing themselves with unique display values, as they all share a common display value: (empty). And we just learned better than to believe that field is actually empty, too.
      

I'm guessing the auto-numbering is turned off due to the table's highly volatile nature - enormous amounts of activity can go through it and daily the "Import Set Deleter" job deletes the last 7 days of activity - making numbering potentially chaotic and also not continuous since it would have no reference to the starting number. However, couldn't the numbering just be reset every so often? Sure, these numbers would have no long-term meaning outside of the 7-day window, but the current blank numbers have no long-term meaning either, so that's not necessarily a new symptom per-se, depending on how you look at it. At least this would allow the current data in the system to have legible/comprehensible values that could make cross-referencing an easier task during short-term troubleshooting.

I'm guessing this isn't considered a good idea, since this is all OOTB - number being sys_import_set_run's display field and having its auto-numbering turned off - but I have to wonder if there's a better way to handle this situation. Especially when going back and forth between related records, troubleshooting an issue, and being annoyed that the human-visible context of any field referencing that table is often nil.

Nothing I've touched on is critical, I admit. It just makes it hard to take notes and draw conclusions at times.

Just hoping to get some input from the community on this.

Does anyone have experience trying this? Auto-numbering the table so we can have valid display values, that is. What were your findings, please? Thanks!

Do any SN developers know why number is both the display field and auto-numbering is disabled, making all reference fields referencing these records display as "(empty)" due to the display field being blank? Please advise why I should not modify this. Thanks!

[Bert_c1]

I see the default value for number on the sys_import_set_run table is 'javascript:getNextObjNumberPadded();'. However that table does not have auto-number enabled. If I click the 'Auto-number' field, sugesstions for the prefix, Number, and number of digits show.  so you can do that.  

Values and configuration in in the 'sys_number' and sys_number_counter' tables. So deletions of "old" records won't affect the new record values. Numbers should be consecutive, previous numbers will not be used. Test in a sub-production instance, and monitor behavior.

[JosephW1]

Thanks @Bert Culpepper! Those are good points. Thinking ahead creatively, I realized that the auto-numbering probably won't honor any deletions, so I was actually thinking of incrementing it via an before-insert BR that sets the number value. In that BR a GR with orderByDesc('number') combined with setLimit(1) can easily get the latest number and then it can either A) reset it if a threshold is met or B) increment it, and push its result to the number field. That should make for a script that can very efficiently increment or reset the number on every import set run that is created. I'd want to find a formula for generating a threshold that would work no matter who the customer is, though.

Thoughts?

[Bert_c1]

Hello @JosephW ,

Please see:

Enforcing unique numbering (servicenow.com)

for a business rule that deals with auto-numbering, to enforce uniqueness.  (that "works" with the auto-number feature.) As in rare cases, duplicates can occur.  the auto-numbering is enabled for may tables. Please review the sys_number and sys_number_counter tables.  There is the "number of digits" field on the sys_number field. When I enabled auto-number for sys_import_set_run, 7 digits was the default value (as for many tables). That means the upper limit is TRA9999999, the 'number' value on my sys_number table record is '1,000'. That will result in the first record in sys_import_set_run being TRA0001000.  You may adjust those if you like.

I feel there is an inconsistency in my OOB configuration, between the sys_dictionary record for the number field on the sys_import_set_run field's default value, and that auto-numbering not being enabled on the sys_import_set_run table. But that may be an over-site or considered a "feature". 

I'm not familiar with Import set, I know about them. I am very familiar with Tables and Columns (and a few others).  Your idea looks like it would work, but there is an OOB feature for that.  I can't test using that table in my instance, but I did test with the incident table, and while the "number" of digits in sys_number for Incident is 7, once I reached INC9999999, the next one I created was INC10000000. So no checks OOB of exceeding the 'number of digits' on the sys_number record for incident.

If you want to reset, and use OOB functionality, you can use a BR as you propose, and once you exceed your threshold value, update the record in the sys_number_counter for the sys_import_set_run table with the desired reset value.  Both 'number' on task and sys_import_set_row are defined a String fields with max length 40. So you may never exceed that field's storage.

Again, you approach looks OK, it may impact performance more that the OOB feature, as the sys_import_set_run table grows.