Bev, I think you are missing the point that five different child tables are involved in the current design; yet they want an integrated report.
What do these 5 child tables contain? Maybe these records are similar and can be kept in one table? The fact that you want one report out of all of them points in that direction.
Actually the child tables are Milestones , Business Stakeholders, Project Team , Resources , Benefits. Everyone is in relation with Master Table "Projects" based on Project ID. Each child table have differnt multiple records against project and sometimes they are not.
We want to display data in report like
Milestones Records List ( All Records Against Project ID)
Business Stakeholders records list
Project Team records list
Resources records list
Benefits records list
In above 5 Child table , if anyone don't have records , then we can ignore them in report and include others.
An additional table between project and the five child tables would help. Each record is one line in the report you want to generate.
Now the report can be built as list of related records in _one_ table.
You mean I want to merge all 5 child table records and master table record in another new table with single record for each project ?
Thank you for the additional information. What version of FMP are you using? Is this a report for printing? How many fields in each "list" (variable)?
You use the term "list", so I wonder if the ability of the List() function to get at related records will help:
If there are mulitple fields in the related tables, they may need to be concatenated into a calculation (text result) that can be called by the parent record with the
List ( Milestones::calcField ; Business Stakeholders::calcField ; Project Team::caclField ; Resources::calcField ; Benefits::calcfield )
The List() function will provide ONLY non-blank records.
And maybe portals can be made to work. Granted that they have significant limitations in a printed report but your data may be such that you can work within those limitations.
If the data in the portal row can work from a fixed row height it may be possible using sliding settings to adjust for varying numbers of portal rows. If this is true for all but one child table, it still may be possible.