Filtered portals with dynamic relationships

[d94dsj]

I hope someone could help in discussing a good way to solve the following problem.

I have a "parent" file A, in which I have a portal displaying records from a "child" file B. So far, so good.

For the sake of easy explanation, I have a real-life example: File A contains Customers and that file B contains Events for those customers.

This is a basic "1-m" (One-to-many) relationship. Each customer can have one or more events related to it. An event could be one of the following types: Fax, E-mail, Phone Call, Meeting.

Each event is marked with the person responsible for that event. So, an event record contains:

* The foreign key for the customer

* The "type" of event

* The foreign key for the person responsible for the event (there is also a "persons" file C.)

So, these are the "facts".

Now, in file A, I would like to have a portal to file B, displaying the events. The user should be able to filter the events by selecting values from two lists.

The first list would limit the list with respect to the persons responsible for the events. It's caption would be "Show events for: " and the list would contain values such as "All persons, John Doe, Jane Doe, Foo Bar".

The second list would limit the list with respect to the type of event of the event record. It's caption would be "Show events of type:" and the list would contain the values "All events, Fax, E-mail, Phone Call, Meeting".

Now, I can easily create a "simple" dynamic relationship. For example, say you wanted to display *either* "marked" records from the child file, *or* the records that are not marked. Then you could define a "left key" which concatenates the primary key of file A with a global value (which the user could toggle with a button), which is either 1 or empty, and then relating that "left key" to a "right key" which is a concatentation of the foreign key in file B and the "marked" field, which would be either 1 or empty. A portal based on this dynamic relationship would show *either* marked records *or* non-marked records.

But for the above situation I'm a bit stumped. The right side of the relationship must be indexed, and thus cannot be a calulation that depend on a global or a related value. So, how can I create a "right side" key that match for some records in some settings, but not in others?

I thought that this might be solved by putting several portals on top of each other, each having a dynamic relationship. But I haven't tried it yet, and I dont't like the complexity it adds to the system, it makes it hard to understand and maintain in the future.

Has anyone successfully implemented a filtered portal based on several values before?

Thanks for any imput,

Daniel

[andygaunt]

Daniel,

We use a similar procedure to you with great effect.

File 1 Customers

File 2 Activities

In file 2 the activity type is set from a text field as is the activity for.

So, creating a calculation [text] field that concatenates this is a simple procedure.

Calc is as follows:

Activity For & :| & Activity Type & " " &

Activity For & :|ALL" & " " &

"ALL|" & Activity Type & " " &

"ALL|ALL"

What this returns is

Joe Bloggs|Phone Call

Joe Bloggs|ALL

ALL|Phone Call

ALL|ALL

Now this key can be used with your globals in your company database.

1 global text for Show Activity For

1 global Text for Activity Type

1 calc that concatenates these as

Global For & "|" & Global Type

This calculation is used in the relationship to the secondary file calculation.

You now have the ability to show ;

any user and a particular type of event

any user and all their events

All users and a particular type of event

All users and All events

All done in one portal.

HTH

[d94dsj]

Andy,

You've just been top rated!!

Thanks a lot for the description of your excellent technique.

I haven't implemented it yet, but I've "compiled it" in my head and I can see that it will work.

The only thing I'm concerned about now is that it will get a bit messy, since I store a foreign key ,and not the name, of the persons that will be assigned to the events.

When you choose the value in the lists, I need to have a dynamic list, which contains both the word "All" and the foreign keys for the persons. I could create a "dummy" record called "All" in the persons file, but I don't like "false data".

But the main thing is that it's going to work in the end!

Daniel

[andygaunt]

Daniel,

Thanks for the compliment. It is always a pleasure to help.

As to your record for "ALL".

we have a users database that has certain priviledges.

The record for all is there, but in each record there is a field for allow creation of an activity for user (yes/no) and also display user name in list (yes/no).

These help us in the fact that we don't want anyone to create an activity for ALL, because basically they do not exist.

So, in our other files there is a constant yes field for each record.

Using this as a relationship back to the field allow creation of an activity for user in the users database will not allow the ALL to be displayed in the value list, but ALL will be displayed from the global value list.

[d94dsj]

Andy,

Thanks again.

I thought of doing it something like that, but it adds complexity to the system, a fact which I'm not particularly fond of. Especially since the system I'm building consists of 30+ related files.

But, if there's a will, there's a way as the saying goes...

Daniel