Autonumbering behavior differs from Specify 6

[melton-jason]

Describe the bug
Specify 6 and Specify 7 treat autonumbering differently.
Specify 6 only filters and matches other records in the table based on the part of the formatter which increments.
Specify 7 filters and matches other records based on the non-incrementing parts of the field.

To Reproduce
Define a field formatter which contains parts which must be entered by the user, and at least one numeric incrementing field.
(Don't forget to assign the field formatter to the field in the Schema Configuration tool. If the format is for CollectionObject -> catalogNumber, also follow the steps outlined in https://discourse.specifysoftware.org/t/to-change-the-assigned-catalog-number-format-default/282)

Consider the below format for the CollectionObject -> catalogNumber field:

  <format system="false" name="NumericTest" class="edu.ku.brc.specify.datamodel.CollectionObject" fieldname="catalogNumber">
  <autonumber>edu.ku.brc.specify.dbsupport.CollectionAutoNumberAlphaNum</autonumber>
    <field type="numeric" size="4"/>
    <field type="separator" size="1" value="."/>
    <field type="numeric" size="2"/>
    <field type="separator" size="1" value="-"/>
    <field type="numeric" size="6" inc="true"/>
  </format>

The format has the pattern NNNN.NN-######, where each N is a number entered by the user, and each # is automatically incremented.

Reproduction instructions for both Specify 6 and 7

• On the Data Entry form for the table, enter a value in the field and save the form
• Add another record, ensuring the non-incrementing parts of the field differ from the first

In Specify 6

• See that regardless of the value of the non-incrementing parts of the field, the incrementing part is 'globally respected'

sp6_behavior.mov

In Specify 7

• See that the incrementing parts of the field will always start over if there are no other records which exactly match the non-incrementing parts of the field

sp7_behavior.mov

Please fill out the following information manually:

• OS: macOS Sonoma 14.3 (M1 chip)
• Browser: Chrome
• Specify 7 Version: production and v7.9.7

Reported By
Taran Meyer from the Canadian Food Inspection Agency (CFIA)

Additional context
For a more in-depth explanation on the autoincrementing behavior in Specify 7 see #5234 (comment)