Background:

FMP8 client & server on OS X Panther

I have a database of customer contacts (10k+ records)
Each record has fields for "owner" (ie the member of staff responsible
for that contact) and "recontact date"

What I want to do is, for each user, have a dynamic list of contacts
that are due. I want the list to be sortable in order of name, date or
organisation.

So I have a global field that is set to the current user name.
Am I right in saying this can have a different value for each concurrent
user? Or will the "global" nature force it to a single value for all users?

Anyway, assume that is OK for the moment.

I have a self-join relationship with the global "current user" on the
left and "owner" on the right.
And I have a layout with a portal that shows the name, organisation and
recontact date from the relationship.

This works.

The problem is that I want to be able to change the sort order of the
portal at run time. I want the user to be able to select the sort order
to be either name or organisation or date. All ascending or descending.

If I try and do a normal sort it has no effect. Because the sort works
on all the "normal" records in the layout outside the portal - of which
there are none. The sort does not work on the protal records.

So it seems to me that the sort order for the portal is hard coded at
design time - I know how to set this up in the portal definition. But
it can't be changed at run time.

Could someone please tell me if I am going about this the right way?
I am a little worried that I have a layout with no normal records and
just portal - although it shows the records I was exp[ecting, it
doesn't feel right.
Is there a better way to do this?

And is there any way to get the sort behavior I want?

Many thanks

Luke,

I would probably use a tab control. Each tab would have an instance of the
portal sorted the way you wanted. Clicking a tab results in that version of
the sort being shown.

FileMaker 8.5 gives you a little more elegance in programmatically
controlling those tabs, since you can name them and use scripts to "go" to
the tab you want.

Yes.

Create a global field (gSortOrder) and an unstored calc field (text
result) in the table that is the source of the portal. The global will
hold a keyword that you define to be your desired sort order. For
example, you might put the word 'name' or the word 'date' in there.
Your calc will be a case statement, something like:

case(
gSortOrder = "name"; OwnerName;
gSortOrder = "date"; right("0000000" & getasnumber(ReContactDate), 7);
OwnerName) // this is the default in case gSortOrder is empty


Note that for dates, it is helpful to convert to a number first for
sorting purposes. But it is also helpful to convert numbers to text
with leading zeroes so that all numerical values being sorted have the
same length. Keep in mind that since the calc result is text, the sort
order will be based on text rules. This means that 100 will sort before
9. But by adding the leading zeroes (as I did above), it will sort
0000009 before 0000100.

One other thing you should know is that after you have changed the
global field, you may need a Refresh Window [Flush cache results] step
in your script to get the portals sort order to refresh on screen.



Luke Siemaszko wrote:
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance

Thank you both. Very helpful suggestions. I thought Howard's
particularly cunning. But - it doesn't allow me to reverse the sort
order (ascending / descending).

Thinking about it a bit more, I'm probably making a rod for may own back
using a portal. I don't think it needs to be that complicated, a second
window on the same DB with a script to find and sort the records I want,
is all I need. I think a portal is an overkill. Unless there is
something I have forgotten.

But I liked the suggestions and even if I don't use them here they were
educational and I'll bear them in mind for other problems.

Thanks

You're welcome!

I always wished that FileMaker had implemented a kind of "Layout Panel"
similar to what an "iframe" does on web pages. Such a panel would enable you
to have more flexible control over presentation within that region, as if it
were a full-fledged layout instead of just a portal. Or, if you wished to
think of it a different way, an embedded "window."

"Luke Siemaszko" <no.spam.for.me (AT) please (DOT) com> wrote