Our Preferred 5 Star Hosting Provider - Award Winning FileMaker Pro Hosting
Results 1 to 3 of 3
  1. #1
    Forum Member
    Join Date
    Feb 2008
    Posts
    7
    Location
    Central Scotland
    FileMaker Pro Software
    16 Advanced
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    05-18-2017
    Local Date
    10-20-2017
    Local Time
    11:24 AM
    Last Active
    08-11-2017

    Report summarising data from both a single and all child records on one layout....

    I got my head in a mess working this one out so hopefully someone can point me in the right direction as I'm not sure I'm going about this the best (most efficient) way...

    Essentially I need a report from the context of the parent contact record so that the data extracted from child survey records can be sub-summarised by contact area and category. The problem is that the survey data for each contact needs to be summarised 2 different ways: sums from the most recent survey; and also counts from all surveys in the specified date range)... so essentially from two different relationships.

    I have a working solution which basically involves adding a whole bunch of calculation fields and the summary fields into the parent contact table. When the global report start and end dates values are set, the date of the most recent report for each contact is calculated:

    mostRecentSurvey = ExecuteSQL( "SELECT ''||surveyDate FROM surveys JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate<=? ORDER by surveyDate DESC FETCH FIRST ROW ONLY" ; "" ; "" ; contacts::contact_ID ; globals::reportEnd )

    This is used as a match field to isolate the most recent survey record ( contactID = fk_contactID AND mostRecentSurvey = surveys::surveyDate ) from which I can pull the figures to be summed using simple calculation fields, e.g. result_Q1 = mostRecentSurvey::Q1

    For the counts I used ExecuteSQL calculations rather than defining another relationship to pull in the appropriate results for each contact, e.g.:

    surveysDone = ExecuteSQL( "SELECT COUNT surveyDate FROM surveys JOIN contacts ON fk_contactID = contact_ID WHERE fk_contactID=? AND surveyDate>=? AND surveyDate<=?" ; "" ; "" ; contacts::contact_ID ; globals::reportStart ; globals::reportEnd )

    With the appropriate survey data pulled into each parent contact, it is then summarised using Summary (Total of) fields.

    So, whilst I have a solution that works, it's a bit slow to update and I'm sure I'm missing something simple and there is a better, more efficient way to go about this.

    Any thoughts or input would be much appreciated!

  2. #2
    Senior Moderator - FMT Preferred Trainer - Consultant: Australia, New Zealand, Japan & Pacific Rim David Head's Avatar
    Join Date
    Apr 2001
    Posts
    2,892
    Location
    Sydney, NSW Australia
    FileMaker Pro Software
    FileMaker Pro 15 Advanced
    OS
    Windows & Mac
    Skill Level
    Developer
    Last Post
    10-17-2017
    Local Date
    10-20-2017
    Local Time
    09:24 PM
    Last Active
    10-19-2017
    Without knowing anything about your data and how it is structured, I would suggest you look at the virtual lists technique. This will certainly allow you to summarise from multiple relationships.

    Just Google "FileMaker virtual lists". Lots of useful links.
    Regards,
    David Head
    FileMaker 15 Certified Developer
    FBA Trainer
    LinkedIn Profile

    There is always a well-known solution to every human problem - neat, plausible, and wrong.
    H. L. Mencken, Prejudices: Second Series, 1920

  3. #3
    Forum Member
    Join Date
    Feb 2008
    Posts
    7
    Location
    Central Scotland
    FileMaker Pro Software
    16 Advanced
    OS
    Mac
    Skill Level
    Intermediate
    Last Post
    05-18-2017
    Local Date
    10-20-2017
    Local Time
    11:24 AM
    Last Active
    08-11-2017
    Thanks David – I'm familiar with the virtual lists technique having deployed it elsewhere, just hadn't considered it as an option here (perhaps as I was over-thinking things and getting my head in a mess!)... I can see how it might work, pulling the different types of summarised data (e.g. one-to-one sums, one-to-many counts) from the same table into a new VL table (rather than pulling data from multiple tables into a single one as I have used the technique elsewhere), just not sure if it would be more efficient or not.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 12-12-2014, 02:05 PM
  2. How to show a report of records based on a single field inside of another layout
    By MorenoMdz in forum FileMaker Pro 12 - All versions
    Replies: 8
    Last Post: 05-22-2014, 10:17 PM
  3. Compiling records into single report/email
    By joshw in forum FileMaker Pro 12 - All versions
    Replies: 0
    Last Post: 07-09-2012, 10:39 AM
  4. Combine Multiple Child Records into a single Field
    By FredP in forum Calculations and Define Fields
    Replies: 3
    Last Post: 12-01-2008, 04:59 AM
  5. Report summarising data in date range
    By angelfire4xx in forum FileMaker Pro 7.0
    Replies: 2
    Last Post: 08-01-2007, 05:18 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.