sorting a portal

[pmusaev]

what would be my first step in trying to sort portal rows? i would like to create buttons near the top of the columns that will sort each column in ascending and descending order. any help would be much appreciated. cheers!

[John Caballero]

The basic idea is that you set up a calculation in the related file to sort by. Let's say you have nID, tName, & dDate fields in your related file that you're showing in your portal. If you click a field header, you'll set a global field (gFlag, for example) in the related file. The calculation determines which field to sort your portal by based on the value of the global field. Your calculation would look like this:

Case(

gFlag="ID", nID,

gFlag="Name", tName,

gFlag="Date",dDate,

nID)

When you set the sort by field option in your relationship, you'll select the calcuation in the related file. If you don't select a field header, the poral will default to sorting by nID.

Is that enough to get started?

[Vaughan]

I've done it by creating multiple relationships, each sorted as required. Create identical layouts each with the differently sorted portal on it. The magic then consists of switching between the layouts.

A bit crude compared with John's sugestion.

[John Caballero]

Here's a sample. I had to tweak the calculation a bit from what I posted. Hope this helps.

Portal_Sort.zip

[pmusaev]

the forums had went down last week before i was able to post a "thank you" to you John. thank you very much for helping me out with this issue, much appreciated.

cheers!

[pmusaev]

John,

I can't seem to get Numbers to sort properly, I'm pretty sure I know why, but I can't seem to resolve it. This is how it would sort numbers:

1,234,567

2,345

4,567,345

etc...

based on the first numeral.

Can you provide any insight?

[John Caballero]

It looks like the numbers are in a text field. You may have to have a calculation that puts leading zeros in front of your number if your sort by calc. returns a text value. In your example, your numbers would format like this:

1,234,567

0,002,345

4,567,345

That would cause them to sort properly:

0,002,345

1,234,567

4,567,345

This is because text is literal - the leading zeros must be considered when sorting, whereas they are truncated in a number field.

I don't have FileMaker here at work, so I can't check my sample file, but I think one of the tweaks I had to do was to put in leading zeros in my example. I'll have a look this evening.

[pmusaev]

yah you did John, I guess I was hoping for a way to have the numbers displayed without the leading zeros. thanks for the quick reply mate!

[John Caballero]

The numbers in the portal don't have to display the leading zeros. The "sort by" calculation has to have the leading zeros. You can have a seperate text calculation that converts your non leading zeros (the ones that are displayed in the portal) into numbers with leading zeros and use that value in your sort by calc.

[pmusaev]

sometimes i wish i was as smart as you john, you are the Filemaker god!

[John Caballero]

That's so nice of you to say...and so entirely wrong!!!

Compared to some of the people who post here, I'm small potatoes!!

[pmusaev]

argh, for the life of me..........

so i am trying to get this to work, but i do now know where to convert the number into text in order for it to sort properly. Would it be in the "sortBy" field?

Case(

sortFlag="Trade",subcontractor,

sortFlag="ContractAmount", contractAmount,

subcontractor)

The thing I noticed is that if the Calculation Result is changed to "Number" then it works. Sorry for the ignorance John, I am an extra newbie.

[pmusaev]

I guess what it is... I don't know how to put leading zeros into my calculation

[John Caballero]

No need to apologize!

I would create a separate calculation for this. Here is a relatively simple way to skin this cat.

Create this (number) calculation cLen: Length(Int(countractAmount))

This will tell you how many digits the whole dollar amount of your contractAmount is.

Let's say it's safe to assume that your max Contract Amount will never be more than $999,999,999 (maximum of 9 digits).

Create a text calculation called cContractAmt:

Case(

cLen=1, "00000000" & NumToText(contractAmount),

cLen=2, "0000000" & NumToText(contractAmount),

cLen=3, "000000" & NumToText(contractAmount),

cLen=4, "00000" & NumToText(contractAmount),

cLen=5, "0000" & NumToText(contractAmount),

cLen=6, "000" & NumToText(contractAmount),

cLen=7, "00" & NumToText(contractAmount),

cLen=8, "0" & NumToText(contractAmount),

NumToText(contractAmount))

This calc looks at the number of digits of the whole dollar amount in your contractAmount field and appends the number of leading zeros it needs based on the number of digits.

So now your flag calculation looks like this:

Case(

sortFlag="Trade",subcontractor,

sortFlag="ContractAmount", cContractAmt,

subcontractor)

My standard disclaimer: I don't have FM at work, so this hasn't been tested. And there are certainly more complicated ways of determining the max digits (using a self join relationship for example), but this will work unless you anticipate having huge contracts.

Good luck!

[-Queue-]

Why not use Right( "00000000" & NumToText(contractAmount), 8 )?

[John Caballero]

Is that a trick question?

It's been my understanding that Right & Left functions return a value based on the conditions specified - I didn't know you could use them to set a value (as in append the leading zeros in this case). I would've thought that if you had a contractAmt of $400,034 for example, that the right function as you provided wouldn't append the leading zeroes.

See...this is why I'm small potatoes compared to some here. Queue is a big spud.

[-Queue-]

Well, I've been called worse.

Incidentally, I don't think the NumToText is necessary either, but I could be mistaken.