3 tables - how to filter?

[chikanyc]

I have three databases/tables that all reference each other...

a Line Items table with a calculated Quarter field (dependent on the date that the record is created)

a Quarters TABLE that automatically generates a new record for each new quarter in the Line Items.

a Client table with a list of clients. The clients table is linked to the Line Items table via Client ID.

In my Client Database i am trying to display a Quarters portal that lists out quarters that Client has done business with us in, and show a sum of line items for each listed quarter. I am having trouble doing this. I've tried every which way but can't seem to get it. The best I can do is get the portal to display the quarters associated with the Client but cannot get the sum of line items (transactions) for each quarter row. Does anyone have a recommendation on what relationship or calculation I should be using?

(I re-posted this since my previous post didn't come up when clicked on for some reason...)

Edited September 9, 2009 by Guest

[comment]

You need to set a global field in Quarters to the ClientID of the currently viewed client. Then, using another relationship between Quarters and LineItems (matching on both quarter and ClientID), sum the line items in the quarters table, and show the results in the portal.

Note that this requires keeping the global in-sync with user navigation in Clients (easy in version 10 with script triggers).

Another option would be to use a custom function - but you'd need the Advanced version for that.

[chikanyc]

Thanks comment..I just updated my FMforum profile - I am using 10 advanced.

What is the Custom function you were referring to?

If possible I'd like the portal to list ALL relevant quarters with the sum of line items (transactions) instead of one quarter at a time...

Thanks for your help!!

[comment]

If possible I'd like the portal to list ALL relevant quarters with the sum of line items (transactions) instead of one quarter at a time...

Each quarter sums up its own items (for the client in global). So you do get ALL relevant quarters with their sums - for one client at a time.

I wasn't referring to a specific custom function.

[chikanyc]

but is it possible to see those summaries from the Client table and not the quarters table?

[chikanyc]

i did it! thanks so much comment! i did a script trigger in the Client layout and set the global client field to the current record's client id:)

thx:)