Placing fields from Multiple tables on one portal
I am trying to place fields from multiple tables into a portal. I am using FM Pro Advanced 11.0v3; Windows XP Professional, Version 2002, Service Pack 3, soon to convert to Windows 7. This database will be used in IWP and my knowledge of FMP is at the end of the Beginner stage.
My situation: 6 tables (Employee, Workload, Report, Rept_Empl (a join table to fix the many to many relationship for Workload and Report), Location, and Assignment.
I have a layout based on REPORT. On this layout, I have a portal that I want to populate with fields from EMPLOYEES and WORKLOAD. On this layout, I also have fields from the LOCATION table as well as other fields from REPORT table but they are not in the portal. This is what I want to do.
1) Create new record and REPORT::_kp_ReportID is populated (auto-enter serial)
2) Select the REPORT::_kf_LocationID on the layout. This field is a drop-down menu (populated from LOCATION table). Once the location is selected from this drop down menu, the EMPLOYEE portal is populated with the employees assigned to that location.
3) Enter the month and year of report in REPORT::month and REPORT::year fields which are not in the portal.
4) Create a new workload record for each employee listed on the portal via the portal.
I have been successful in populating the EMPLOYEE portal, however, when I create a new REPORT record and select the location, the WORKLOAD fields are already populated with previously entered data. The only time I want to see previously entered data in the WORKLOAD fields is if the REPORT::month and REPORT::year matches WORKLOAD::month and WORKLOAD::year.
The theme is: LOCATION has many REPORTS but a REPORT can only be about one LOCATION. LOCATION can have many ASSIGNMENTS but ASSIGNMENTS can have only one EMPLOYEE and EMPLOYEE can have many ASSIGNMENTS. EMPLOYEES have many WORKLOADS.
I have been working on this for over two weeks and have tried many different relationships, read hundreds of pages of documentation but cannot get what I want. Please help.
Thank you, Mary