To Change a Catalog Number Format (other than default):
Catalog number format is one of the few things that cannot be changed through Specify once the database has been created. In order to change the catalog number format, you have to make changes in the schema config in Specify and to several tables in MySQL. You must be logged into Specify as an admin level user and know your IT user information in order to complete the steps involved. You must also have direct access to your MySQL instance to follow these instructions.
You cannot change the catalog number format if you already have Collection Object records in your database that have Catalog Numbers that do not match the format you want to use. If you already have Collection Object records with a different format, you will have to change the numbers via MySQL before you can create the new format.
If you have more than one collection in your database, make sure you are making these changes to the correct collection.
Before making any significant changes to your database, such as changes in MySQL, you should backup your database.
Edits made in Specify (must be logged in as an admin user):
In Specify, go to Help → About.
In the About window, double click the name of your database. In this example, you would double click “testdata”.
In the Local Prefs window, click Add Property.
Enter the term “EDIT_CATNUM” exactly as it is here in the Property field. In the Value field, enter “true”. Then, click in another field to save the value.
Click Close on the Local Prefs window and on the About Specify window.
Go to System → Schema Configuration.
In Schema Configuration, click English, then Edit a Schema.
In the Schema Configuration – English window, scroll to and click the collectionobject table in the list of tables.
Then find catalogNumber in the list of fields and click it.
Once the Catalog Number field has been selected, the Field Format will become activated. Click the ellipses at the end of the line.
In the Field Formatting Editor, click the green plus to create a new format.
In the next window, enter a Name and a Title for the new catalog number format. You can enter the same value for both. Remember what you have entered in the title field, because you will need to enter it in MySQL later exactly as it is entered in the title field.
In the right side of the window, add the components of the format as you want them. Start by clicking the green plus. Select a component, then click the green up arrow. There is more information on the different components and navigating the Field Formatting Editor in the Specify help document. You can click the Help button in the window to be taken to that part of the help document.
Once you have the format as you want it (do not forget to click the up arrow for the final component, or it will not be added to the catalog number format), click OK.
In the next window, select the format you just created and click the green check mark. Then click OK.
Click OK in the Schema Configuration window then close Specify.
Edits made in MySQL:
In the MySQL WorkBench, log in as the “root” user for the database.
Find your database in the list of Schemas and click the triangle next to your database name.
Click the triangle next to Tables to reveal the full list of tables.
Find the autonumberingscheme and right click to reveal a hidden menu. Select Edit Table Data from the list.
In the autonumberingscheme table, identify the collection you want to modify and scroll over to FormatName and replace the current value in the field with the title of the format you created. It must be an exact match.
If your format contains more than just numbers, make the value in the IsNumericOnly field with a 0. If the format is purely numeric, make the value a 1.
Click a different field then click Apply.
Scroll down to the Collection table, right-click it to reveal the menu and select Edit Table Data.
Scroll over to the CatlogFormatNumName for the collection you want to modify and replace the current value with the title of the format you created. It must be an exact match.
Click a different field then click Apply.
Exit out of MySQL and restart Specify
To Restore the Settings in Specify:
Return to the Local Prefs window by following steps 1and 2 above.
Scroll through the prefs until you locate the EDIT_CATNUM pref. Once identified, double click “true” to edit the value.
2. Enter “false” in the values field.
Click another field to save the change. Click Close.
Restart Specify.