5 Replies Latest reply on Mar 14, 2014 9:26 AM by philmodjunk

    Data base integration



      Data base integration



           I am a little stock on my project. I am merging all my data bases together to automate the work. So far i have been able to synch out employee records with their payroll record. When an event is confirm the event's information is transfer to a new table called "Confirmed Events" what i want to do next is be able to add an "X" amount of employees to "X" event and have the information of that event automatically be transfer each employee's record. I tried doing a portal on the confirm events but it will not let me add employees. it tells me the record is not available. What is the best way to approach this?


        • 1. Re: Data base integration
          /files/85fe9b4545/Screen_Shot_2014-03-12_at_12.18.34_PM.png 1034x720
          • 2. Re: Data base integration
            /files/bf533686f7/Screen_Shot_2014-03-12_at_12.18.49_PM.png 1036x795
            • 3. Re: Data base integration

                   It appears that you are confusing "record" with "layout". You can show data from events on an employee layout and data from the employee table on an events layout using relationships.

                   But the exact type of relationship involved is key.

                   Am I correct that for an event, there can be more than one employee associated with that event? And a given employee can be associated with more than one event?

              • 4. Re: Data base integration

                     exactly correct!

                     I can have 20 guys working 1 event and or one guy working multiple events the same day. one employee will work multiple events throughout the week.

                • 5. Re: Data base integration

                       Then you have a many to many relationship and you cannot link an employee directly to an event. You need to add an additional "join" table to link employees to events.

                       And I will repeat the comment that I posted in an earlier thread that I see no need for more than a single ID field in your relationships. What you have appears to be redundant and a needless complication.


                       Employees::__pkEmployeeID = Employee_Event::_fkEmployeeID
                       Events::__pkEventID = Employee_Event::_fkEventID

                       You can place a portal to Employee_Event on the Employees layout to list and select  Events records for each given Employees record. Fields from Events can be included in the Portal to show additional info about each selected Events record and the _fkEventID field can be set up with a value list for selecting Events records by their ID field.

                       For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                       And I happen to have a many to many demo file that links contacts (think employees) to events: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                       It's in the older file format, but FileMaker 12 or newer users can use Open from FileMaker's File menu to open this file and produce a copy converted to the .fmp12 file format.