AnsweredAssumed Answered

List related records AND # matches in found set

Question asked by CharlotteLasek on Jul 28, 2014
Latest reply on Jul 31, 2014 by CharlotteLasek


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!