It looks like the Sheet # field is defined as text. Change the field to a number field; this should correct the sort.
If these fields are changed to number fields will I still be able to enter letters if needed?
1 of 1 people found this helpful
You can enter them, but FileMaker will ignore them.
If you need to enter letters, but want the field to sort as if it were a number, then enter leading zeroes. Instead of this:
1, 2, 3 ... 10, 11, 12
01, 02, 03 ... 10, 11, 12.
That will cause FileMaker to sort the field correctly.
Thank you so much for the help and quick response! I will talk to the developer about this when i see her next.
Just for fun, you can have the system automatically enter leading zeroes for you. Go into the field definitions (File / Manage / Database) and set the field to have an auto-enter calculation:
Case ( Filter ( SheetNo ; "123456789" ) = SheetNo and Length ( SheetNo = 1 ) ; "0" & SheetNo ; SheetNo )
Set the calculation to replace the existing value, and FileMaker will automatically put a leading zero on any value that's exactly 1 character long and in the range 1 through 9.
Another option would be to define a second field that is a calculation of the first.
Use the function GetAsNumber(SheetNo).
This field would then be the one used to sort the report. Because the result is a number it will sort correctly numerically. In the case of mixed text and number value entries in the first field, the sort would interpret the number and place the sheetNo record accordingly.
Note: the second field does not have to display on your report. It is just used to define the sort order.
Sample screen shot attached.
sample.jpg 23.2 K
I know it's been nearly a year since you wrote this but I was having the same issue with leading zeroes (we're importing a lot of data from Excel and, unfortunately, FM is stripping out the leading zeroes because they're being imported into a text field.) I did what you suggested above and it seemed to work but then I noticed it was also adding leading zeroes to double-digit numbers (well, anything 11 and above. For some reason, it doesn't do it to tens.) I'll paste what I've litereally written below. Any ideas on fixing this?
Case ( Filter ( Chapter ; "123456789" ) = Chapter and Length ( Chapter = 1 ); "0" & Chapter ; Chapter )
Wow. I wrote a bad calculation a year ago.
Try this instead:
Right ( "0" & Chapter ; 2 )
Thanks for your response. Do you mean that's the entire, new, calculation script or that I should be replacing part of the earlier script with what you've suggested above? Sorry to be so dense! I'm unable to play "developer" at the moment so I can't just test it out.
FMP does NOT strip out data when importing. IMHO, you are mistaken in your understanding of the problem.
Excel does not recognize leading zero's in a number cell. So the value being supplied by Excel does not include leading zero's. The same thing happens when you export a number value with leading zero's in a text field from FMP and open it in Excel. Excel recognizes the value as a Number and removes the leading zero's.
The question is how long is the value supposed to be? Is the vale a Zip code, 5 characters, Social Security, 9 characters, Serial Number, unknown length, Etc.? Hopefully all the values in this field are the same length.
To get leading zero's means adding zero's before the value and truncating to the desired number of characters.
So we need to keep everything from the left edge and back up. For example if the maximum number of characters is 5:
Left ("00000" & field ; 5 )
However, what should happen if the field is blank? If it should remain blank, no zero's at all, then we need to test for that too.
What if the value is already over 5 characters?
IsEmpty ( field ) ;
Length ( field ) < 5 ;
Left ("00000" & field ; 5 ) ;
If you want the value to be 5 characters and include the field, shouldn't the formula be:
right("00000" & field; 5)
or to handle an empty field:
if(isempty(field); ""; right("00000" & field;5))