Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 2 of 2
  1. #1
    Forum Member
    Join Date
    Nov 2017
    Posts
    1
    Location
    Sydney, Australia
    FileMaker Pro Software
    FileMaker 11 Pro
    OS
    Windows
    Skill Level
    Novice
    Last Post
    11-05-2017
    Local Date
    11-25-2017
    Local Time
    07:31 PM
    Last Active
    11-05-2017

    Count records with same value in date range

    I'm fairly new to Filemaker and this is my first post so I hope I can explain what I'm trying to do...

    I have a layout (Members) where I would like to display the count of records from another table (Attendance) between a date range.

    The data in the Attendance table looks like this:

    attendance.PNG

    I have manged to create a fields on the Members layout that counts the number of the attendance types "Gold", "Silver" and "Bronze" records using a calculation, however I only want to count the records if they fall in a date range (such as a financial year).

    count_calcs.PNG

    Further to this I'd like to indicate if the required attendances have been met for each type. I was hoping I could change the colour of the field or display a tick to indicate compliance. I have another table which defines these requirements:

    reason.PNG

    The above will be so the operator can see if each member has met the requirements for the year, but I would also like to be able to run a compliance report that only show members who have not met the requirements for the reporting period.

    Everything hangs together like this:

    links.PNG

    I hope this makes sense.

  2. #2
    Senior Moderator - FMT Preferred Developer Consultant - New York - Worldwide AHunter3's Avatar
    Join Date
    Aug 2006
    Posts
    12,187
    Location
    New York and Nassau County, NY, USA
    FileMaker Pro Software
    FileMaker Advanced/Developer 14, 13, 12, 11, 10, 9. 8.8, 8, 7, 6, 5.5 FileMaker Pro 8, 7, 6, 5.5, 5
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    11-22-2017
    Local Date
    11-25-2017
    Local Time
    04:31 AM
    Last Active
    11-24-2017
    a) I would create these fields in Attendance:

    OneIfGold = Case (Type="Gold"; 1)
    OneIfSilver = Case (Type="Silver"; 1)
    OneIfBronze = Case (Type="Bronze"; 1)


    Summing those up, either with summary fields within Attendance or with calc fields (e.g. Sum (RelatedAttendanceRecords::OneIfGold) to get total Gold Attendances), will let you get the aggregate data you want for the three attendance types; you can incorporate date ranges into either the relationship you use or the found set you utilize depending on whether you wish to go with Summary fields or calc field from the perspective of a different table occurrence.

    If you are, in fact, summing up across a relationship from a different table, you could, instead of the OneIfGold (etc) fields, use constants on the other side of the relationship, e.g., Gold.cst defined simply as "Gold" (text result calc field), and use it in the relationship, therefore constraining the related recs to those where Type = Gold" and so forth.



    b) I am massively unclear on what the heck makes an attendance "Gold", "Silver", or "Bronze", and you didn't explain. My initial assumption was that Joe Jones is on the Gold Plan and hence all his attendances are Gold (and he either makes the threshold or doesn't), whereas Suzy Smith is on the Silver plan with fewer that she is contracted to make in order to make her threshold etc. I see, however, that you've got Bob Builder with at least one of all three types. What the heck makes Bob Builder's Jan 13 attendance "gold", his Oct 25 "silver", and his July 18 "bronze" ????

    It may not be necessary to know in order to answer a question but then again it may whether you realize it or not. In this case, without knowing what makes it gold, silver, or bronze, I haven't the vaguest idea what you wish to tally up in order to ascertain whether the "required attendances have been met".


    Are you by any chance saying that within a specifc (but unspecified in your post) time frame the first attendance by anyone is their "gold", then once they have reached 3 they start getting silver, and staring with their sixth attendance they get bronze? (Isn't that backwards from the usual deployment of bronze, silver and gold? Isn't gold usually reserved for the most difficult to attain accomplishment, silver less than that, bronze yet less than that?)

Thread Information

Users Browsing this Thread

There are currently 6 users browsing this thread. (0 members and 6 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-05-2015, 08:41 PM
  2. Find records within date range
    By janis85 in forum FileMaker Pro 10
    Replies: 7
    Last Post: 06-18-2009, 01:11 PM
  3. Count Number of record by date range
    By stbenoit in forum Calculations and Define Fields
    Replies: 1
    Last Post: 05-08-2009, 06:38 AM
  4. Count days in a date range with a twist!
    By alexc in forum ScriptMaker and Scripting
    Replies: 2
    Last Post: 07-28-2007, 08:05 PM
  5. Displaying only records within Date Range
    By Corey in forum Calculations and Define Fields
    Replies: 1
    Last Post: 02-02-2006, 11:44 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Welcome to the Original FileMaker Forum
With designated forums for almost every FileMaker topic...FileMaker Today is a FREE to join community where you can boost your FileMaker expertise, build better apps & solutions and interact with over 42,000+ FileMaker Developers from all over the world. If you're building FileMaker solutions, this is the place for you.
Follow FMT
We Are FMT
FMT is home of The World's leading FileMaker News Site, Worldwide Developer Directory, FileMaker Connect Community and so much more.