AnsweredAssumed Answered

Serial Number Issues (Number vs Text Type) while using combination of Text and Numbers

Question asked by sussetteh on Oct 27, 2016
Latest reply on Oct 27, 2016 by alangodfrey

I need to create unique serial numbers as my primary keys which contain some kind of leading text identifier.

 

Example:

Table: School

Primary key: _pkSchoolID

Type: Number

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.

 

My questions:

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?

Outcomes