Jump to content

GetSummary of Hours in a Range using Calculation Field


This topic is 2247 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!! 

Link to comment
Share on other sites

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

graph.png

layout.png

Edited by Steve Martino
additional info
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2247 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.