Another "Sum of related fields" question

[gseymour]

This is a follow on to a question previously asked and answered:

A calc field that uses Sum (relationship::numberfield) will total the related records. That is, the records included in the total depend upon the relationship. If the relationship,

for example, is Client to Invoices by ClientID, then it'll only include the total of the Invoices for that client.

I have 3 tables: 1) Visits (made/month); 2) VisitDetailLines; 3) Businesses. The VisitDetailLines Table is a child table of both the other two tables. There is a field in the VisitDetailLines Table (YesNo) that either contains a 1 or 0. My goal is to be able to total the number of of "1" for each business BY MONTH. Right now the SUM function does fine for the if I only have 1 month. I thought that I could simply create a Found Set for the second month. Obviously, the SUM function doesn't know about that relationship and still sees the entire VisitDetailLines table. I can imagine a script that will address this problem but there MUST be a simpler way. HELP??? (FYI - I have a script that populates the VisitDetailLines table based upon additional input. I did not include that since it is not directly relevant to the problem.)

I've attached the DB... THANKS!!!

WBATest1.fp7.zip

[comment]

I am having trouble understanding what does the Visits table represent. "Chris & Sandra" or "Todd & Kristie" don't sound like "visits" to me.

I am also not sure what you mean by "total the number of of "1" for each business BY MONTH." If this includes more than one month at a time, it would be best to produce a report from the lines table, summarized by business and by month.

[gseymour]

I am having trouble understanding what does the Visits table represent. "Chris & Sandra" or "Todd & Kristie" don't sound like "visits" to me.

I am also not sure what you mean by "total the number of of "1" for each business BY MONTH." If this includes more than one month at a time, it would be best to produce a report from the lines table, summarized by business and by month.

This business makes calls on new residences (move-ins) and drops off advertising materials for each business. Chris & Sandra are the names of people who have been visited - in this case in month 3. In he "visit" set you'll see that 25 were visited in month 3 and 5 were visited in month 2. The 1 or 0 in the detail line table indicates (for each business) whether the literature for "that" business was dropped off.

On the Business side I want to calculate the number of people who received information for each business during each month.

I can generate a report using sub-summary fields but I need the specific number of visits/business/month for additional calculations.

Hope this helps... Thanks!

[comment]

Do I understand correctly that "Chris & Sandra" will be visited only once (perhaps when they move in) and never again?

On the Business side I want to calculate the number of people visited per visit during each month.

You still haven't answered my question re "each month".

[gseymour]

Do I understand correctly that "Chris & Sandra" will be visited only once (perhaps when they move in) and never again?

You still haven't answered my question re "each month".

You are correct. Chris and Sandra will be visited only once. At that time they will receive information from each business. There ARE some businesses who's information does not apply (ie Christian Church information to a Jewish family) That is where the 1 or 0 comes in.

The "each month" comes from the fact that I need to produce several documents/reports at the end of each month to summarize the activities for each business for that month.

Check the Form View on the Advertiser Input_Edit layout. The portal represents the people who were visited during the month for that business. HOWEVER - the total at the bottom

represents the total number of records in the VisitDetailLines table. Only 25 of these people were visited in month 3. 5 were visited in month 2. That is my problem. I want to be able

to select month 3 and have the SUM reflect the correct amount.

Thanks for hanging in there with me.

[comment]

I need to produce several documents/reports at the end of each month to summarize the activities for each business for that month.

I still think this would be best done by producing a report "the Filemaker way" - i.e. go into a layout of Lines, find the lines where YesNo = 1 and Visits::Month = [the month], and sort them by BusinessID. Remove the body part of the report and keep only a sub-summary part by BusinessID.

See the attached for one possible way to do it through a relationship.

---

BTW, I hope in your real file there is a year in addition to the month (or even better, the entire date).

WBATest2.zip

[comment]

Good. I just hope it's obvious that the year is necessary here - otherwise you'll be summing up the data of month X in all the years you have.

[gseymour]

Good. I just hope it's obvious that the year is necessary here - otherwise you'll be summing up the data of month X in all the years you have.

Thanks again for your help - 2 more questions - as a Newbie - I am having trouble understanding - what does the calculation for cYesNoInMonth do? I see the results but I am trying to understand what the expression really does. Secondly, I think I understand the Let (...) function but what does the variable "trigger" represent? Thank you!

[comment]

what does the calculation for cYesNoInMonth do?

YesNo and Visits::ReportMonth = Advertisers::gMonth

It's a simple Boolean test: when YesNo returns True (1) AND the two month fields are equal to each other, the result is True (1).

what does the variable "trigger" represent?

It's just a means to get the calculation to re-evaluate when gMonth is modified - by including a dummy reference to the field.

[gseymour]

YesNo and Visits::ReportMonth = Advertisers::gMonth

It's a simple Boolean test: when YesNo returns True (1) AND the two month fields are equal to each other, the result is True (1).

It's just a means to get the calculation to re-evaluate when gMonth is modified - by including a dummy reference to the field.

I got it now... I thought Trigger was a dummy field. Don't know how I missed the Boolean test. I should have figured that one out myself.

Thanks again!