1 2 Previous Next 26 Replies Latest reply on Sep 17, 2012 2:02 PM by marybarrett-sparrow

    Placing fields from Multiple tables on one portal



      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



        • 1. Re: Placing fields from Multiple tables on one portal

               People >--< Events    Many to many

               People >-- Attendance --< Events     Many to One to Many [Join Table]     fields from both People and Events can be on Attendance

               People >--< Associations    Many to Many

               People >-- Membership --<  Asscociations   Many to One to Many [Join Table]     fields from both People and Asscociations can be on Membership

               Membership >-- Overview  --< Attendance   unsure on how to phrase this....

          Tutorial: What are Table Occurrences?
          Tutorial: What are Table Occurrences?

          CHAPTER 05: About Relational Design

               The idea behind FileMaker join tables is that you have two (or more) table occurrences that are linked together via a middle table occurrence. Because FileMaker relationships can flow from one table occurrence, through another table occurrence to a table occurrence on the other side, you can have an almost unlimited number of interlocking relationship combinations.

               The join table is designed to support many to many relationships. In a many to many relationship a collection of records in one table occurrence can share a relationship to many records in another table occurrence. Here I have a few illustrations that might help.

               EXAMPLE - A car mechanic may have worked on many cars and a car may have been worked on by many mechanics. So if you wanted all the cars a mechanic has worked on and all the mechanics that have worked on all those cars, you might have an impressive list.

               EXAMPLE - A movie may have many actors and each actor may have been in many movies. So if you wanted a list of all the actors in a movie and then a list of all the movies those actors had been in, you would probably have a large list.

               EXAMPLE - A high school teacher may have many students and each student may have many teachers. So if said you wanted to find all the students for a particular teacher and all of their associated teachers ... you would get a very big list.

               So a join table will likely have a primary key field but it isn’t used for much. The power comes from the collection of foreign key fields it contains and how they interact with the data. Normally, a join table will have a foreign key field for each table it links, to support the join operation. When you are talking about a join table setup, it is not uncommon to call the central table the join ... obviously .. and refer to the linked table occurrences as the outer tables. This is because they circle the central join table in an outer orbit.


          Home > Designing and creating databases > Creating a database > About planning a database

          About planning a database
               A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.
               Follow these general steps to plan a database:

          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

          Relational Database Design 101 (part 2 of 3)

          Relational Database Design 101 (part 3 of 3)


          The White Paper for FMP Novices is useful  - 


          • 2. Re: Placing fields from Multiple tables on one portal

                 Okay, I read all of the above and changed relationships:

                 Now I have the following:

                 Employee2 -->Assignment <-- Location -->Employees--> Workload <-- Report

                 Location and Workload are essentially used as join tables.

                 Layout table is Report

                 Report::_kf_LocationID populates the Employee portal which has fields from Employee and Workload.

                 All that works fine.  When I select the location from the drop down menu on Report:;_kf_LocationID, it populates the Employee field with employees assigned to that location.  My problem is that I am not able to create new record for Workload.

                 I guess my question is how do I create new record for Workload in the Employee portal if no Workload record has been created for the Report month or year.

            • 3. Re: Placing fields from Multiple tables on one portal

                   Maybe this cannot be done without scripting.

              • 4. Re: Placing fields from Multiple tables on one portal

                     It depends on the relationship between report and workload. In some cases, you can enable the "allow creation of records via this relationship" option and you can then create a new record in the portal simply by entering data into a field of the blank row that then appears in the portal for this purpose.

                     With other relationships (and sometimes just to avoid having to scroll the portal to expose this "add row"), you need to use a script that freezes the window, copies needed data from the match field of the current report record and then changes to a layout based on the portal in order to create a new record, copy the data from the variable to the matchfield of the new portal record and then returns to the original layout.

                     Note that this script can trip script triggers due to the layout change so you may have to make changes to avoid that issue.

                • 5. Re: Placing fields from Multiple tables on one portal

                       I was incorrect in saying that I cannot create new Workload records while on the Employee portal.  Allow creation is checked and I can enter information in the fields. What happens is when I create a new Report and populate the Employee portal via the drop down menu, the old data in the Workload fields is present.  I want the Workload fields to be empty so I can create a new Report record and a new Workload record at the same time based on the Report Month/Year and the Workload Month/Year.  I probably will have to script to create new record in Workload, but how do I make the Workload fields come up empty?


                       Thank you,Mart

                  • 6. Re: Placing fields from Multiple tables on one portal

                         Note that your relationship "tunnels" though work load to get to employees.

                         That means that you must have a record related to Report that is also related to the selected employee before any data can appear in the portal.

                         What "match fields" do you use for the relationship between Workload and Report? Between Workload and Employees?

                    • 7. Re: Placing fields from Multiple tables on one portal

                           Workload::_kf_ReportID  <-- Report:_kp_ReportID  (A Report can have many Workloads but a specific Workload can only belong to one Report)

                           Employee::_kp_EmployeeID --> Workload::_kf_EmployeeID (Employee can have many Workloads but each Workload can only belong to one Employee)

                           Location:_kp_LocationID --> Assignment::_kf_LocationID -Assignment::_kf_EmployeeID <-- EmployeeID (Assignment is a join table for Location & Employee.

                      • 8. Re: Placing fields from Multiple tables on one portal

                             So the relevant relationships for the issue at hand are:

                             Report----<WorkLoad>-----Employees    (----< means "one to many" )

                             Report::_kp_ReportID = Workload::_kf_ReportID
                             Employee::_kp_EmployeeID = Workload::_kf_EmployeeID

                             This is a typical many to many relationship between Report and employees. A report can list many employees and an employee can appear in many reports.

                             Your portal should be to WorkLoad Rather than Employees. The fields from Employees can then be added to the portal row to show the employee selected for that Workload record.

                        • 9. Re: Placing fields from Multiple tables on one portal

                               Thank you for the information.  I guess I was trying to have the employees' names already in the portal because there will be at least 50 names per report then I could just tab to the Workload fields and enter Workload information right in the portal. I did not want to manually enter or select employee name each time a new Report records was created.

                               I thought it would be simple to create new record in Report layout, select the Location from the drop down menu; the drop down selection populates the portal with the assigned employees and the Workload fields are ready to enter new data.  I thought I would use a match field for Report and Workload as the month/year fields on both.

                          • 10. Re: Placing fields from Multiple tables on one portal

                                 But how else woud you determine which employee is assigned to a given WorkLoad Record?

                                 If you don't assign an employee ID to the workload record in this portal, you would need to assign it through other means.

                            • 11. Re: Placing fields from Multiple tables on one portal

                                   Employees are pre entered via the Employee layout (context of Employee table).  On this table their current location and assignment are selected.  There is also a portal on this layout context of Assignment which keeps a history of that employee's location and assignment.  So, the EmployeeID number will never change, no matter what location or assignment they are in.

                                   Location represents the city the employee works/worked in. Location is pre populated and has a fixed LocationID.

                                   Assignment represents the department the employee worked in while in a particular location.  The employee does transfer around to different cities and within the same city but with a different assignment.  Any time an employee is transferred to a new assignment, a new record is created with a new AssignmentID.

                                   The only ID numbers that will change are ReportID and AssignmentID.  Everything else will always be the same.

                              • 12. Re: Placing fields from Multiple tables on one portal

                                     So the link between Employee and Location is pre-established that is why I have the drop down menu to select location.

                                • 13. Re: Placing fields from Multiple tables on one portal

                                       Can I say I really do appreciate your help.  Thank you

                                  • 14. Re: Placing fields from Multiple tables on one portal

                                         But none of that links to the workload record. When you to to a specific report record, your current relationship links you only to workload records with the same Report ID. Some means has to be used to specify what employee goes with a given workload record. Note that the word 'workload' appears no where in your last post.

                                         What, besides selecting an employee in the portal, can be used to identify which workload record should link to a particular employee? What does a workload record represent?

                                    1 2 Previous Next