7 Replies Latest reply on Jul 31, 2014 4:42 AM by CharlotteLasek

    List related records AND # matches in found set



      List related records AND # matches in found set


           Hi everyone,

           I’m a graphic designer who got sucked into FMP (which I am finding challenging and fun). This is my first time posting, but I've been using this forum a lot over the last month or so as I learn how to do… well… everything. I’ve been officially tasked with developing a brand new DB of survey responses, from which we’ll need to create reports and summaries based on various criteria. I have no control over how the raw data is given to us from the survey company. I think I’ve got a good start as far as the input, basic setup and relationships go, but I’m having trouble summarizing and reporting when it involves more than a simple Find.

           Here’s an example of my setup. There are about six more questions/answers like this:

           Main Table: t_SurveyInput
           Fields: Relationship_input, ID_Relationship (match field)

           This is the table that imports all the raw data from the surveys. ID_Relationship is a calculation field that is assigned a code based on the survey answer. For example, if Relationship_input “Parent attending with child” ; ID_Relationship “R1” 


           Table2: t_Relationships
           Fields: ID_Relationship (match field), Relationship_Display

           There are 7 possible answers to the relationship question, so each is a record on this table with codes that match the previous calculation:

      ID_Relationship | Relationship_Display
           1) R1 | Parent
           2) R2 | Grandparent
           3) R3 | Relative


           The surveys are conducted by city, so I need to create a report for each city’s results. When I have a found set for “City”, I need a list of the 7 values of t_Relationships::Relationship_Display AND how many matches there are within this found set. (I’d ideally include the percentage of each response, too.) Ultimately, it would look like this:

      Summary for “City”:
      Relationship_Display         # in found set          % of found set
      Parent                                           85                        85%
      Grandparent                                 15                        15%
      Relative                                          5                          5%


           And that’s where I’m stuck. I don’t know how to list each of the records from the related table (sounds like a portal). It seems like this would be a basic thing and maybe it’s really easy and I just don’t know what it’s called. I don't know how to write a calculation that only looks within a found set. I’m not sure which table the calculation or summary field would go on, or if it should be a global field (on a separate t_globals table?), or if it should be a portal with a calculation there. I’ve tried a bunch of things that didn’t work and I’m just stuck. Any help will be very much appreciated!

           Thanks in advance!