Tough portal sorting on calculated field

[awayand]

Hi guys,

I have a Parent db and a child db. I created a child portal in the parent db, so that I can see multiple children for one parent. The children have some fields in them, that are calculated fields. Now I'm trying to define a sort in the relationship needed for creating the portal, but the sort should be on a calculated field. For some reason, the sort seems to be random. Do you guys know if a sort on a calculated field is possible inside a portal?

Thanks! I know this is a hard one.

[ernst]

Hi awayand (?)

One example says more then a thousand words...

See the attached file on how this can be done. Example works with a self join (relation to the same file) but that should not make any difference.

Regards,

Ernst

PortalSorting.fp5.zip

[awayand]

That's actually pretty cool. I don't quite understand it though. What does the self-relationship do?

[Anatoli]

RE: What does the self-relationship do?

Instead relation A to B it is A to A. It can be used for example as Main Address of HQ and branches/offices with different addresses.

[Ugo DI LUCA]

Hi,

As Ernst said, the selfjoin here was just a shorter example of what you could do within 2 related files...

You're just "altering" the sortkey of the right side of your relationship using this calculation.

With some adjustments, you can even change the sort order from descending to ascending, but this is another topic.

[awayand]

Hi,

I still don't understand. What's the concept of a "sortkey"? I noticed one of the fields in the relationship was a global. Why is that? I tried changing it to "Text", but all the entries disappeared and couldn't be revoked even when I changed back to a global. This is a really *cool* feature, but I just don't seem to get it. Any more detail on how this works? Thanks!!!

[Ugo DI LUCA]

OK,

So I didn't downloaded Ernst sample but I'll bet that he is "altering" a sortkey based on a global field.

Go in define relationship---->Modify---->check the box "sorted" and pick up a sortkey.

Imagine you have field A and field B

you want to sort either by A or B.

Just add to your related file a calculation Case(global = 1, A, :.

Then use this calc as a sort key for your relationship.

But as Ernst said, a sample is better than words. So check back his demo file and you'll find the clues.

Now, if you were to sort either by numbers or text fields, you'll have to first re-define your numbers as text fields in a new calculated field (you could use Right("00000000"&yournum,8),

then use this new calculated field in the Case calculation for the new sort key.

[awayand]

Hey,

I guess here's the problem I have. I tried implementing your approach into my little database, but somehow, the sort doesn't work. Can you guys direct me to my problem? Any help *greatly* appreciated!

Sites.zip

[Ugo DI LUCA]

I guess we need either the password and the related file to help you more on this one.

If your 1 or 2 choices is a globa field, just create a constant relationship from Main to related (1 at left side and 1 at right side using indexed calcultation). Call it constant.

Then, the Sortkey, in the related file should look like

Sortkey = Case(Constant::global field = 1, Country, City)

And use this sortkey as a SORTKEY for your "Main to related relationship"....

[awayand]

Sincerest apologies. The password is "master".

[Ugo DI LUCA]

What about changing the result of your calc to TEXT....

[awayand]

And use this sortkey as a SORTKEY for your "Main to related relationship"....

So what exactly is a sortkey?

[awayand]

Ok. Here's my latest attempt. Something happens, just not the right sort. any ideas? The file doesn't need its related file...

Sites Example.zip

[Ugo DI LUCA]

You would notice some changes if :

1. You used the field c_fieldtosort as the Sort key in your relationship definition.

2. You changed the recult of c_fieltosort from Number to TEXT.

[awayand]

Ugo,

when I try setting the c_fieldtosort as the key to constant on the right, nothing shows up in the portal. When I do the reverse, I get the error that "this relationship won't work because c_fieldtosort can't be indexed". Any clues?

[Ugo DI LUCA]

The main clue here is :

I NEVER SAID TO CHANGE THE RELATION KEY....

Just the Sort field you defined within the relationship.

Go in Define Relationship---->Modify---->At bottom right of the Menu is 2 checkboxes.

Pick up the "sort by field" and select the new sort key c_fieldtosort.

[ernst]

Hi Awayand,

I looked at your file and I think you just overlooked on which field to sort.

The field that the records should be sorted on is 'cfieldtosort' (so not the field 'sortkey', which actually is unnecessary.)

I also changed the last two script, those that are called when you click in the header of the portal. These scripts set the global field 'sortfieldselect' to 1 and 2 respectively, which results in changing the contents of 'cfieldtosort' to either 'sitecity' or 'sitecountry' for all records.

The last scriptstep, which refreshes the field called 'constant', is a somewhat dirty trick to force FIlemaker to refresh the portal.

Hope this helps.

Attached the modified file.

Regards,

Ernst

Sites Example Ernst.fp5.zip