Filtered Portal with Checkbox

[tbfox]

I have read several FM books, read every forum thread and downloaded the samples, I even understand the CONCEPTS, but dang it, I cannot get this to do what I want. I am aware it will take you seconds, so I defer to your judgement:

I have a list of Courses and ClinicalParticipants. Some of the ClinicalParticipants are hospital employees (TJUH), and some are not. I want to filter the Course portal to show the course expense based on all records in the portal, employees only, and non-employees only.

I can't get the global filter in the right spot to be related to the right field. I know I'm inches away from the solution, and I dissected so many other databases to try and figure it out, but I wave the white flag. I appreciate any assistance. Feel free to adjust the file as necessary.

Thanks,

Traci

FilterPortalCheckbox.zip

[Keith LaMarre]

You need another table - you have "courses" and you have

"people" - Clinic Part Demo -

But, assumedly one person could be registered in multiple courses - therefore you need a table called "Course Attendees" which consists of the course id and the particpant id.

You would probably add some fields to thias attendees table like the rate for the attendee (could be based on job, etc.)

Then, you need a second relationship - based on a global in the courses - I called it "Employee Status" - i made a tabbed interface where you can select the status you want to look at - you can't select "all" as no one in you demo file will have that status.

[tbfox]

Thanks. I tried that and still couldn't get it to work. I have relationship issues, I think.

Maybe a script would be better. Perhaps I could populate a portal on another layout with records that are employees, non-employees, or showing all records. I would still need a functioning relationship, but then I could get "all."

[John Mark Osborne]

I spent about 15 minutes looking at this solution and I would have to agree with Keith. You can get done what you want but your relational design is flawed. It's going to take more than a simple tweak or two to fix this solution.

[tbfox]

Drat. Well, thanks anyway. I'll keep on trucking along with it. I'm not new to FM, but I am with relational design. It has been an interesting learning experience. Heck, I cheered when I got the portal to work. :-)

[Fenton]

Like John, I looked at the file, but came to the conclusion that while it would not be terribly difficult to set up the portal filter, there were questions and problems beyond that. Like why there is an EmpStatus AND a PayRollEmp in Invoices? Which one would have "TJUH" in it? Whichever field it is, it needs to Lookup (or auto-enter by calculation) the PayRollEmp value from the person's record in ClinParticDemo. Otherwise you have nothing to filter in Invoices.

Second, the global filter field likely belongs in Course, not in Invoices (though you could use it there with a self-relationship; these things are flexible, but you have to know what is what). It is a different portal from the one you have. You still need the "straight-ahead" relationship however, so don't delete it.

Third, because you want to flip between "TJUH", not TJUH, and All, you'll need another calculation field, based on the global, to do the actual filtering. Also, there is a small problem with "not TJUH", Is it actually a value? That is, what does it mean if it's empty? This is important. Is it logically "non-TJUH", or is it nothing?

You have to answer this question from the point of view of the filter in order to know how to filter for it. If it isn't really a value, it's just the absence of TJUH. since you want to filter by "non-TJUH", as well as by "All", it needs a value. "All" can be done various ways. I just put the values as a multi-line field, text result; ie.,

1

0

That will match either.

I prefer to use Boolean values, because they take up much less disk space, and are not so affected if you decide to change the name of the thing later; though that is unlikely in this case (TJUH). You may want to use the text values.

FilterPortalCheckbox_fej.zip

[tbfox]

Fenton,

You hit the nail on the head. Thank you _very_ much.

(I had some extraneous tables & fields because that was my "test document," so I was trying things and ended up with a sloppy database.)

I'm going to study your changes and implement them in my "real" version.

Thanks to all in the forum.

Traci