Portal Filter Help...

[trevc]

Have a table structure sumthing like this:

[People] –(#1)---<[MeetingFees]>--(#2)----[Meetings]

Where #1 relationship based on: peopleID = fidPeopleID and #2 based on fidMeeting = MeetingID

I want a Portal from People that has the ability to control what’s viewed in it based on Year AND Quarter of the Meeting (i.e. show all Meeting Fees in Q3 of 2007)

I immediately thought to create a relationship based on a calculated fidPeopleID in MeetingFees (i.e. If meeting year = requested year then fidPeopleIDCalculated = fidPeopleID) … but that goes down the path of globals and calculated fields that you just can’t do relationships on.)

The reason I want this to be setup this way is due to the fact I want to calculate sums of fees, etc. in the People layout so the user has the ability to export or Save as Excel and have those values available for import into another system.

In the past I’ve used a ‘report’ table in which I do a find and recreate the table with just the selected records, but since this is also a user interface … it’s not that slick.

It’s just a form of Portal Filtering, but my Left Brain just isn’t talking to my Right Brain today…

Any direction or points to samples would be appreciated...

[Søren Dyhr]

I think I would solve it this way - otherwise will the keying depend on autoenters into stored fields.

If it should be made in two different layouts, would I break up the TOG ...anchor buoy'ish, the way they're tied is due to the use of the tabbed layout.

--sd

Untitled.zip

[comment]

I agree on principle. But there's no List() function in version 8, so you will need either a custom function or a value list to compute the multi-line key of the relevant meetings.

[trevc]

That's so much closer to what I need ... never seen the List function used in a relationship before.

I'm using 8.5 Advanced so the List function is fine, the issue is that the structure seems to fall apart if you have more than one meeting in a quarter.

I tried to implement a yearly summarization as well, but same thing. Suggestions? (file attached)

unititledYearly.zip

[Søren Dyhr]

I agree on principle.

Sh*t, I'd deliberatly put my reply in a way, it should do the work of the red cloth in front of a bull - in sincere hope for something more crafty and concise from your hand Michael!

--sd

[Søren Dyhr]

fall apart if you have more than one meeting in a quarter.

Thats not quite the problem, becasue isn't it meaningless to assign the same person to the same meeting several times? You need a mechanism to create a new meeting and strain it from the list when it's used by the person - just once!

I could have implemented it as well, but it have obscured the basic reasoning by adding further complexity to it - similar would I probably strain in the availiable persons for a given meeting as well.

--sd

[comment]

It could be done with relationships (Ugo's way), but I don't think it would be more concise.

I don't see the problem with having more than one meeting in a quarter. Admittedly, I didn't study the demo file too hard, but the method should sum all of person's fees for a given quarter, for any number of meetings. Even if a person attended the same meeting twice, the sum should still include both fees.

[trevc]

Not really .... if there is more than one meeting in a quarter and a person is goes to both of them, that's where it falls apart.

In my file above ... the amounts properly show up, but the names of the meetings aren't different, i.e.

Summit $45

Summit $500

Total $545

Where the second entry should be a different meeting name, but the amounts are different. The relationship to Meetings is not working? Does the relationship using the List command really only take the first value with the meeting relationship?

[comment]

Well, I said the principle was correct, but the implementation is not. It was rather difficult to spot at a glance, especially since Søren's naming conventions make my brain spin. The way he had it was summing ALL fees in the selected quarter, without filtering by the person.

See if the attached makes it any clearer.

FilterJoin.fp7.zip

[Søren Dyhr]

Indeed a better implementation, my hessitation payed off anyhow! It's much clearer to approach it with all records in a timespan and then filter

--sd

[trevc]

At a highlevel it works as advertised ;-), but I'm trying to figure out exactly how it's done.

Can you translate the cSessionsinQ calculation that the relationship is based on ... I now understand how the List is used, just don't understand how setting the variable trigger is even utilized (unless it's a reserved word or sumthin!?!)

Let ( trigger = gQuarter & gYear;

List ( SessionsInQ::meetingID ))

Can you translate?

I really appreciate this help ... as I thought I knew a thing or two but am being proved otherwise.

[comment]

As always when one relationship depends on the results of another, there is an inherent screen refresh problem. In this case, the problem can be solved by making the keyfield of the second relationship artificially dependent on gQuarter and gYear. When you change one of these, Filemaker thinks it has to recalculate cSessionsinQ - and therefore also refresh the relationship that depends on this field.

In fact, the result of the calculation does NOT depend on these two fields in any way. But since they ARE REFERENCED in the calculation, they serve as a trigger for re-evaluation.

The calculation would work just as well if it were just plain List ( SessionsInQ::SessionID ). But then selecting another quarter would not cause the portal or the sum to update - because Filemaker is not aware of the dependency. You would need to apply a Refresh Window [Flush cached join results] to get the correct data to display.

[trevc]

Thanks ... I was feeling pretty silly trying to figure out what the variable trigger was doing!

Thanks to To Søren and You in advancing my FileMaker knowledge!

[comment]

trying to figure out what the variable trigger was doing!

Triggering? :)