I need to create unique serial numbers as my primary keys which contain some kind of leading text identifier.
Primary key: _pkSchoolID
Formula/Entry Option: Indexed, Serial Number on creation with Current Value: "SC42" Increment:"1", Unique Values Only
Everything works beautifully except when I have to (in rare occasions) export to Excel. The FM export process strips all non-numeric characters, so it exports just the numbers (missing the leading SC) for my serial numbers. Another issue is with doing queries: If I do a search for all serial numbers containing "SC" it indicates that no records are found.
I have another table which adds serial numbers with the leading program number: Example P8_124, P9_100. When I export these table to excel I get the following respectfully: 8124, 9100.
I have figured out that if I change the field type to TEXT the number incrementation works well. queries work as expected and data exports as it should, but I loose sorting by number capability.
Is there a setting that I can set to force FM to export these serial numbers of type NUMBER as they appear and not strip the non-numeric characters?
Other than the loss of sorting capabilities, are there any other cons to setting Serial Numbers to the TEXT type?