portal sorting

[pmusaev]

i have a portal sort going on, but it does not sort "Number" values properly, somehow i need it to sort on a number that is preceded by 0's... but not actually display the 0's.

this is how it sorts at the moment:

45,567,123

5,600

9,020,111

but i need it to sort this way:

00,005,600

09,020,111

45,567,123

and then format the numbers for output without the following 0's

5,600

9,020,111

45,567,123

[Vaughan]

It sounds like it's a text field. Change it to a number field and FMP will sort it numerically.

[Vaughan]

It sounds like you store the data in a text field. Change it to a number field and FMP will sort it numerically.

Alternatively, make a calculated number field based on the original field and use it to sort the portal.

[pmusaev]

could you explain the alternative you provided in more detail please Vaughan...

"Alternatively, make a calculated number field based on the original field and use it to sort the portal."

i cannot change it to a Number field because there are other fields getting sorted that are text, as well as date. thank you for the quick reply!!!

cheers

[John Caballero]

To add to Vaughan's alternative suggestion, create a "sort by" text calculation that looks like:

Right("000" & your_number_field,3)

Sort your portal by this calculated value, but continue to display your regular number field.

[Vaughan]

"i cannot change it to a Number field because there are other fields getting sorted that are text, as well as date"

That makes no difference. You can include date, time, text and number fields in a single sort and FMP will work them all out.

John's suggestion is a good one, though I might be tempted to add a heap more leading zeros in case the numbers get bigger.

Right("0000000000" & your_number_field, 10)

[pmusaev]

thank you gentlemen *hat tips* your help is very much appreciated!

ps. John, you are a Filemaker legend, happy new year to you mate!

[pmusaev]

what about a solution for a "Date" field?

[John Caballero]

pmusaev said:

ps. John, you are a Filemaker legend, happy new year to you mate!

No way! Compared to the big spuds here, I'm still just a tater tot!

(Happy new year to you, too.)