7 Replies Latest reply on Apr 19, 2013 9:16 AM by philmodjunk

    Auto population of Join Table

    IanAllan(newuser)

      Title

      Auto population of Join Table

      Post

           FM help says " Each record in the “join table” would have the foreign key fields of the two tables it is joining together.  Nothing special needs to be done with the foreign key fields in the join table as they will get populated with data from the other two tables as records are created.  It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins."

           My join table is not automatically populated. If I manually enter the keys all works. But the creation of a record in the related tables on either side has no effect. Both related tables are enabled to write to the join table.

           I am looking for details of what FM needs to "create records in the join table as records are created in the two tables it joins"

           My situation involves Work Orders and Employee Time Records. Many Employees work on Many Work Orders on any date.

           Thanks

           Ian

            

            

            

            

            

            

            

      FMP_Structure.png

        • 1. Re: Auto population of Join Table
          philmodjunk

               But the creation of a record in the related tables on either side has no effect. Both related tables are enabled to write to the join table.

               Why would that automatically create a related record in the join table? How would FileMaker know which record on the other side of the relationship to link to the new record? It could be any/many/none.

               "Allow creation of records via this relationship" means that if you place portal to Joining on the TimeSheets layout, you can select a value in Joining::WOOrderNumber to specify a record in WOrkOrderes and a record in Joining will be automatically created and a value in Joining::TimeUnique will automatically get the value of TImeSheets::TimeUnique for the current TimeSHeets record.

          • 2. Re: Auto population of Join Table
            IanAllan(newuser)

                 Thanks Phil. I want to create a record in joining as soon as I create a record in WorkOrders. That way I can link a record from Timesheets to that joining record via the portal below on my Timesheets layout as soon as I entire a valid WO# in the first field in the portal.

                 My basic problem is that entering a WorkOrder does not creat a record in the joining table. So far as I can see that until a record with the valid Work OrderNumber exists in the Joining table I am screwed.

                 My question maybe should simply be how do I get FMP to create a recored in Joining when I create a valid record in WorkOrder?

                 Ian

                  

            • 3. Re: Auto population of Join Table
              IanAllan(newuser)

                   Again, from FMP Help "It is not uncommon for a join table to have a lot of records in it – since records are created in the join table as records are created in the two tables it joins."

                   Is my assumption that with the correct structure records are AUTOMATICALY created in the join table incorrect?

                   Ian

              • 4. Re: Auto population of Join Table
                IanAllan(newuser)

                     Hmm, my Key field TimeUnique is a calclated field made up from the entry date and the employee code (note FMP changes date date format). I now understand that calculated fields cannot be used as key fields if you want to write data to the related table via a portal. Back to the drawing board.

                • 5. Re: Auto population of Join Table
                  philmodjunk

                       Use auto-entered serial numbers in TimeSheets and WorkOrders as your primary keys. Use plain, simple number fields in joinings for the corresponding foreign key fields.

                  • 6. Re: Auto population of Join Table
                    IanAllan(newuser)

                         Thanks Phil. The serial number will work with the work order table. Actually the key work or number IS a sequence number assigned by FMP with a prefix 13- to identify the year.

                         Problem I have with time sheets is that I want to limit each employee to one timesheet for each date. The concatenation of TimeCode (which is the employee's code) and TimeDate into the TimeUnique field accomplished that goal. Can I retain the unique status of Time Unique within the Timesheets table and add a serial number field as the key for relationships? If FMP assigns a serial number and then the record is not saved because it fails the unique test is that serial number "used up" or does it remain available for the next saved record?

                         Thanks for your time.

                         Ian

                    • 7. Re: Auto population of Join Table
                      philmodjunk
                           

                                Actually the key work or number IS a sequence number assigned by FMP with a prefix 13- to identify the year.

                           Use just a simple serial number. Any time you add addiitonal "value content" to it, such as that prefix, you set yourself up for trouble. These are internal use only fields. "gaps" in them due to a record being created and then reverted or deleted don't matter.

                           You can have the other fields in your database to keep your users happy, just don't use them as your match fields in the relationships.

                           You can set up a validation that validates for uniqueness on your TimeCode/TimeDate field with an added text field that concatentates teh two in an auto-enter calculation. Then you set a unique values validation field option on it to catch errors where the same employee has specified the same date as a previous record assigned to them.