AnsweredAssumed Answered

Report using multiple related records (some with null sub values)

Question asked by ezz on Apr 27, 2012
Latest reply on Apr 27, 2012 by ezz


Report using multiple related records (some with null sub values)



I am new to Filemaker although have vast experience with Access and relational database design / vba. I cannot seem to find a way to make a report in Filemaker show ALL the correct records although this is probably just due to lack of knowledge with Filemaker, so if anyone can help explain the solution to the below I would appreciate it.


Databse Layout Example

I have 4 tables related as follows:


Table1 (top level 1)

Table1-1 (related to table 1)

Table1-1-1 (related to table 1-1)

Table1-2 (related to table 1)


Now i am trying to do a report showing ALL records regraldess of if Table1-1-1 has data for the single master reecord of of Table 1:


Table1 (show single record)

Table1-1 (show all records)

Table1-1-1 (show all related records if any)

Table1-2 (show all records)


The layout I would like to achieve is:


Table1 (header)

Table1-1 (sub summary header - show all regardless if 1-1-1 has no data)

Table1-1-1 (body - multiple records or none)

Table1-2 (footer - multiple container records on a page at the end of the report)


The prolem is:


a) a sub summary report of 1-1-1 works to a point but if no records are valid for Table1-1 then it does not show these group headers at all. Understandable as its only showing records that are present and related from 1-1-1.


b) a sub summary report on 1-1 works to point but only shows the first related record of 1-1-1 in any set of 1-1 instead of all of them. I thought i could put a portal in this section to solve it but that works to a point but only shows the fixed number of rows I set where there could be none or one or more records.


c) I have not even tried to place all the photos onto the footer from 1-2 yet as I can not get past the first part. I am not sure if this will work so any tips here would also help to create photo pages at the end of the report.


*In Access this is chaieved by modifying a query relationship join to (show all records from table a and only those from table b) or a right/left join (as opoosed to show records where both are equal) to force the records of 1-1 to ALL display regardless if 1-1-1 has a related record.