5digit vs 4digit sort

[innodes]

I have a list that just gained a digit. It went from K9999 to K10000. But the sort throws the K10000 to the bottom of the list, not following the K9999. Is there anyway to make this sort from 9999 to 10000, 10001, etc?

[LaRetta]

We need to know more about this field. If you make this field a number, it will sort properly but it can cause other problems if not considered carefully. You can create a number calculation which is just the field name and calculation result of number and sort by this.

You can also consider prepadding a text number with leading zeros so it sorts properly but it would still take into account the alpha prefix. What do you use this text-number for?

[innodes]

I tried to talk the user into the padded option last year, but they refused. That would have been the best plan. The number is a sku number. It is used with other legacy data on other systems beyond fmp. That is why the format has to remain.

The number field is an interesting thought. Can a number field contain an alpha? What problems might it create if the field (currently text) was changed to a number?

It is currently a text field with an auto entered calculation of:

"K" & Right ( "0000" & Filter( Last ( KNUMBERS_knumbers::knumber ) ; "0123456789") + 1; 5)

and validation of unique.

[LaRetta]

A number field can contain an alpha. But if this sku is used to bind any relationships then you can't simply change it to number in one table and leave it as text in others ... you risk breaking relationships. Also if you change the leading alpha, it will break because a number will not recognize alpha in the relationship and K9999 will match L9999.

You realize that you ARE padding now? If your field were pre-padded with zero then you wouldn't have a sort issue because they would be K09999 and then K10000. And you will eventually need to extend that to 6 as well. So it sounds like you aren't padding with leading zero if you have K9999.

How is this sku used in FileMakear? Does it link any relationships? I would be discussing with Management how they will run out of numbers and should address the issue in near future. And, although you have it set to 'unique', I would be nervous about your method of assigning the unique number. Why aren't you letting FileMaker just assign it itself? I know you said other legacy systems used this sku but does the sku come FROM these other systems?

So for once, I would just say, "If it ain't broke don't fix it" and just sort by the number calculation.

[innodes]

What do you mean to "sort by the number calculation"?

right now it looks like this:

K9999 (at the top of the list)

K9998

K9997 ...

K4231

K4230 (that is the oldest in this dB).

K10003 (the new numbers, "missplaced")

K10002

K10001

K10000

I would like the 10,000 series of numbers to start above the 9,000 series (in a logical order). Right now they are wacked. Hopefully the "sort by the number calculation" you mentioned will do this?

When I changed it from text to number, it does sort correctly. I don't believe there are any relationships built on this number. It is primarily for tracking. It is self referenced to count, but that seemed to still work.

thanks

[LaRetta]

The order you have listed can't be default creation order (if you are using another table as your serialize table + 1). Your Skus (as you have listed them) are being sorted in descending (text) order. I suggest that you leave your actual Sku number alone (in case there ARE places which may be affected if you changed it to number, that you are not aware of). Instead create a calculation called cSku (result is number) = SkuNumber

... where SkuNumber is this current text field we are discussing. And then sort cSku in DESCENDING order. This will put K10003 at the top as "the 10,000 series of numbers to start above the 9,000 series." But each time you create new records, your set of records will become semi-sorted, ie, not perfectly sorted and you will need to re-sort them again.

It is self referenced to count, but that seemed to still work.

It is all your decision; I can only caution you. But your auto-enter calculation which is supposed to be padding that number hasn't been. If it was working, your K9999 would be K09999 and it would sort properly. :wink2:

[innodes]

On the padding, you are correct, when it increased a digit I changed the calc. to work.

If I have already changed from text to number, have I already potentially broken any relationships? If I add the "sorting field" and change it back to text, should everything be clean? Or have I already hosed it?

[LaRetta]

You should be fine to change it back to text instead of number. For future, it is always best policy to back up your file before trying anything new. Only once you are CONVINCED that something is working exactly as planned, then put that process into effect. Back up often and keep multiple copies back through time. YOu will never regret it. :wink2:

[innodes]

thanks again for the help.

And yes, backups are good.