did you have a look in the table for actually stored serial numbers ? My guess is that only the numbers are there, because (it seems) that the serial field is of type number. Then when you export to Excel, letters can't be there. Looks like the serial number is set to GetAsNumber(the_created_Serial_Number). GetAsNumber takes a string as the parameter, and strips all non numerical caracters, but the decimal character.
what if you have the field as TEXT (not number)? increment will still work
there is even a function that does that
When I view table in Browse Mode, Table View the Serial numbers do indeed show the leading Text Value (SH or P8_), so they are being stored as I intended them to be.
Perhaps you did not read my entire post in which I indicated that I did make that change and everything does work properly, but question does still stand....
What are the PROS and CONS to doing NUMBER vs TEXT other than loosing sorting capabilities when using NUMBER type?
no, I read it all. I didn't see your acknowledgement of the function (that may or may not help you or others).
But you are talking about Export (as Excel) and trying to push a number field which should be TEXT, so Excel knows it's text (and you found that). Now if you are talking leading 0's all bets may be off. Excel is terrible at guessing a zero-leading value is TEXT.
your "sorting" seems to be the issue. I will, in those cases, have two fields (and perhaps a concatenated third field for display and export). Sort by the two fields and they should be correct.
Just to clarify.... Are saying that my problem lies with Excel?
I have verified that when I exported my serial numbers of NUMBER type to excel, Excel set the field type to GENERAL not NUMBER. When I exported my serial number of TEXT type to excel, Excel set the field type to TEXT as it should.
Also, to eliminate .xls exporting as the problem, I also exported to Comma Separated Values and FM still stripped my non-numeric characters which lead me to believe that the removal of my leading "SC" is being done by FM.
Primary "con" to using text and smart values (i.e. some char code + integers) is that if you ever have to change scNNNNNN to trNNNNNNN you have to change is where its used as PK AND FK.
I always go for user agnostic primary key values because it reduces the chance of error and reduces future maintenance needs.
Could you elaborate on "user agnostic primary key" or give me an example.
Also, wouldn't you have to make the same change on all PK and FK anyways whether you have the serial number set to NUMBER or TEXT if you needed to change scNNNNNN to trNNNNNNN? Or is it because when set to NUMBER type it ignores all text and therefore it doesn't matter whether it's set to sc or tn...is that correct?
I'm wading in, like a drunk at a party, having not even read the responses in detail (forgot to check the dress code, arrived in scuba suit and fancy dress), but I think the problem started here:
"I need to create unique serial numbers as my primary keys which contain some kind of leading text identifier."
Why not use unique serial numbers as Primary Keys? If you want to develop those into other compounded text-and-number fields feel free, but why stain the good name of Unique Serial Numbers as Primary Keys? If you are looking to qualify the Serial Number with a School Identifier in a relationship, you can always make it a two-factor relationship when you want to.
That's me: back to the bar...
I'm saying Excel sometimes will "guess" (incorrectly).
Does Excel distinguish between type 'General' and type 'Number'? I thought it only had 'General', 'Date', and 'Text'.