GetSummary of Hours in a Range using Calculation Field

[cbombeck]

Hello Everyone 

Very new to FileMaker here. 

I have an Event Table with fields EventID, EventDate, and EventDuration, AND an Attendees Table with fields EventID, PersonID, and SignupCreatedTimeStamp. I would like to add a Field to the Attendees table that that will tell me how many hours of activities this person is already signed up for in the 30 days before/after that event. I can do this using the Find Records method, but I think this could be done in a Calculation Field using GetSummary. 

The point of this all is to prevent people from signing up for more than 20 hours of activities in a month. After I get this calculation worked out, other checks will be put in place when creating new records to make sure people are in compliance. 

To make this a little more flexible I added the following fields to the Events table 

• EventWindowStartDate = EventDate - 30
• EventWindowEndDate = EventDate + 30

Guess what I'm not sure on is the syntax for GetSummary .... I've tried putting the following in a calculation field with no luck

• GetSummary(EventDuration, if(EventWindowStartDate...EventWindowEndDate, EventDuration, 0))

Am I going about this the right way? What is wrong with my calculation above? 

Any thoughts or pointers would be much appreciated. Thank you very much in advance!! 

[Steve Martino]

I'm not sure I totally grasp the situation, but see if this points you in the right direction.

In your Attendees Table:

Create a global field EventWindowStart, with the calculation:  EventDate-30

Create a global field EventWindowEnd, with the calculation:  EventDate+30

Create a summary field, and made it a running total of your hours field.

Create a second Table Occurrence (TO) of  Attendees Table-Attendees_hours.  Make the relationship:

__pkID (primary key) Attendees = __pkID Attendees_hours

                                 and

EventWindowStart <= EventDate

                                and

EventWindowEnd>=EventDate

Put the summary field on a one row, one field portal on the Events layout, and use the Show related records from: Attendees_hours.

It will show the sum of the attendees hours only for that time period.  You could then use that as the basis for denying someone the ability to sign up for more events.

That's the basic idea, hopefully I took a good enough guess to help.  I'm sure others can chime in with better/easier ideas.

Here's 2 screen shots, one of the graph, and one of the field on the layout

Edited May 4, 2016 by Steve Martino
additional info

[cbombeck]

Thanks Steve!! Like I said... very new here but that all sounds like it should get me the info I'm looking for. Thank you also for the visuals. I think I should be able to make that happen!! 

[Steve Martino]

This can also be done with ExecuteSQL (just not by me).  Then you don't have to create the extra TO, just one field on the layout.  Hopefully someone with that answer will pop on.