Summing a Found Set of Records

[HunterBoss]

Soren was a huge help with my previous problem. I'm now able to sum records from one table and display them as a value in another table.

I have the client table (#1) and the revenue table (#2).

I can sum the revenue from #2 into #1.

I can create a found set in #2.

I cannot, however, have the value of the found set in #2 be the value that is returned to #1.

And, since Soren's solution was exceedingly simple and showed that I had had a tremendous brain cramp somewhere along the way, I'm sure this will be just as easy.

But, I've been going through help, the posts here and I also bought the Functions and Scripts Desk Reference. Only partial luck.

Thanks

Christopher

[Søren Dyhr]

I cannot, however, have the value of the found set in #2 be the value that is returned to #1.

What is "THE" value of a found set, is it the first records value or the last record in the found sets value ...in my humble opinion doesn't found sets have values, it's a collection of data which only gets into information from portalized displays, aggregations or summaries.

Is it that you would make a found set by a search in your revenue table, and then portalize it to make a view or perhaps even sum it??

http://previews.filemakermagazine.com/videos/550/PortalResults_full.mov

...another and more clipboard friendly as well as contemporary method is this one:

http://www.nightwing.com.au/FileMaker/demos8/demo803.html

--sd

[comment]

In general, a found set is summarized using summary fields in the same table, while a related set is summarized by aggregate functions in the "calling" table. Mixing them together does not work - because a single table occurence can have many found sets (one in each open window, for example). Therefore, you cannot refer to a found set in another TO.

There seems to be an effort of FMI to enable summary fields to work accross relationships (so that a single summary field could serve multiple found/related sets), but this is currently undocumented and does not always work well.

[Søren Dyhr]

because a single table occurence can have many found sets (one in each open window, for example)

Good point indeed! Rays "Summary" isn't a summary at all, but instead a set of recursive functions dealing with a found set in a specific window due to it's rendering.

--sd

[HunterBoss]

while a related set is summarized by aggregate functions in the "calling" table

comment -- you've hit the nail very squarely on the head. So, just how the heck do you do these aggregate functions in the "calling" table? That, I guess, is my biggest dilema.

Oh, one of the things I forget to include in this post to begin with ( sorry), is that I tried to make a self joined table with a 2 step query. I had the unique record identifiers related to each other, but then I also had an "AND" relationship based on the appropriate date range.

I then tried to sum to the self joined table. Didn't work. The best I could get it to do was to only include the records with the applicable date before the requested rate, instead of after. So, logic would lead one to believe that by reversing the relationship -- turning a greater than or equal to into a less than or equal to -- would solve the problem. Nope.

The comments you both made about found sets and what I'm trying to do with summaries was very helpful and right on.

I'm really interested in knowing how to make the aggregate functions that comment alluded to.

And, no, Soren, I haven't had a chance to look at the 2 links you included in your post. I definitely will tomorrow.

Thanks.

Christopher

[comment]

Suppose we have 2 tables, Clients and Transactions, linked on ClientID, so that:

Clients::ClientID = Transactions::ClientID

A calculation field in the Client table =

Sum ( Transactions::Amount )

returns the sum total of transactions of each client.

To filter transactions by client AND date range, add two global fields to the Clients table: gStartDate and gEndDate. Now place a new occurence of the Transactions table on the relationships graph, and link it to Clients thus:

Clients::ClientID = Transactions 2::ClientID

AND

Clients::gStartDate ≤ Transactions 2::Date

AND

Clients::gEndDate ≥ Transactions 2::Date

A calculation field in the Client table =

Sum ( Transactions 2::Amount )

will return the sum total of in-range transactions of each client.

[HunterBoss]

:worship: Thank you very very much comment and Soren.

comment -- I changed slightly what you told me to do with the second occurance of the Transaction table. I'm only ever going to want records from the date range, so I did it within the original occurance. Am I going to mess something up by doing this?

Also, I had almost done what you suggested on my own. I had made the second occurance of the transaction table. But, did I relate the client table to the second occurance of the transaction table??? No. I related the second occurance to the first!

thanks (I will still go and look at those files that Soren pointed me to)

Christopher

[comment]

I'm only ever going to want records from the date range, so I did it within the original occurance. Am I going to mess something up by doing this?

No, not really. But you won't be able to create transaction records in the portal (it will mess with the transaction date). Also, I like to keep the core relationships (the ones who define the basic data model) separate from auxiliary ones (for viewing/data entry), but that's just a personal preference.