Sorting records with numbers and text

[Conartist]

My database tracks orders for items rented to exhibitors at tradeshows. Several of the reports I print need to be sorted by booth #. 99% of the time everything works fine using the booth # as a number field and then sorting by number. However some of my tradeshows will separate areas of booths by including a letter in the booth #. For example booths in room A are labeled A1, A2 and booths in room b are labeled B1, B2. I need to be able to sort by number and letter so that my report would end up listing A1, A2, B1, B2. With the field categorized as a number I get A1, B1, A2, B2. Which is not helpful. Similarly if the field is text it doesn't sort the #s correctly if they don't have a letter in front of them. I'm sure there's a simple solution to this I just can't find anything similar in the forums. I need something that will work with mixed data as well for example the current tradeshow I'm working on has normal numbers 1 - 100 and then T1 - T100. Any help is greatly appreciated. I have to do reports tomorrow, so I guess I will manually sort them for now.

[LaRetta]

You can hold the booth 'numbers' in a text field. Then have a calculation (result is number) which would simply be that booth number field. In this way, you can sort on either. :wink2:

[comment]

I think you need to use TWO calculation fields - one to extract the text portion, one for the numeric part. Then you can sort by these two fields (text first, then number).

[agaperrk]

I would have two different layouts one with the Apha numeric as the subsort and the other layout with the Booth # Then it is a matter of assigning a button two run either script you can assign 3 different buttons.

[comment]

Huh? I think all you need is a text field for the booth "number", two calculation fields that do not need to be on any layout, and a scripted sort that always sorts in the same order.

[Conartist]

Yes! Comment you've got it. I think what you are suggesting is exactly what i need. I knew it was simple. Thank You! I think this problem might be a little hard to wrap your mind around unless you've seen it in action..we might be too used to Excel's handing of sorting text and numbers to not quite understand the way Filemaker handles them.

[Conartist]

I wanted to submit a follow up with exactly what I did...for the archives...not sure if this is the most elegant code but it seems to work.

I ended up creating two calculation fields to parse out my "booth #" text field.

Booth # Text - is a calculation which results in text

Filter (Left (Booth#; 1); "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")

This selects the first character in the booth # field and then filters out anything that is a number. I'm left with either a blank field or a letter if present.

Booth # Number - calculation is

GetAsNumber ( Booth#).

This parses out only the number characters.

I then sort by Booth # Text and then Booth # Number and the records sort how I want them to.

Thanks to everyone who helped out with this.