11 Replies Latest reply on Oct 27, 2016 2:17 PM by alangodfrey

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


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



      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?