9 Replies Latest reply on Apr 24, 2013 12:09 PM by AndrewFoo

    Issues with related records



      Issues with related records


           I'm trying to create two databases that share related records between them.

           Database 1 - Will eventually end up on Filemaker Go but it has a desktop component for it (In the attached image, the script to the left is part of database 1). Tables of note: Student info page

           Database 2 - Contains employee information as well as work history (on two separate but related tables). Think of this as the back end. Tables of note: Employee Informaton and work history.

           Just for reference, the UMID mentioned in the scripts is suppsoed to be a unique ID number and will act as the primary key for each record.

           The problem I'm running into is that database 1 can export all of the correct information for the first test and it  properly stores it on database 2. However, when I do another test case (with a new UMID), the UMID does not transfer over correctly for the work history table, it actually uses the UMID of the first test case for the work history table as opposed to the new one.

           Any help would be greatly appreciated not only for this problem but also improving the scripts right now.



        • 1. Re: Issues with related records

               And what is the relationship between Employee Information and WorkHistory?

               There's a lot more that isn't clear about your design and what you are trying to do, but that bit of missing info was the most obviously relevant to your question.

          • 2. Re: Issues with related records

                 They are related tables.

                 Employee information has one unique record per UMID whereas Work History can have multiple entires with the same UMID.

                 The work history essentially shows the employment history of a particular employee and they have a relationship via UMID

            • 3. Re: Issues with related records

                   I know that they are related, but HOW are they related? The details can be significant.

                   Please note, I'm severly limited in my time to spend in the forum today and this weekend and really haven't had the time to go over both scripts in detail.

                   Other questions:

                   Is UMID a text field that auto-enters a calculation: Get ( UUID ) ?

                   The right hand script starts with "go to Layout [original layout]". What layout is it going to? Specfiically, is the script being executed on a layout that lists Employee Information in Layout Setup | Show Records From?

                   I am correct that these are two different scripts? Are they scripts in the same file?

                   Assuming two scripts, how does $$UMID in the right hand get a value? It references that global variable, but there's no clear indication how the variable gets a value and if the two scripts are performed in two different files, the value of $$UMID in one file has nothing to do with the value of $$UMID in the other.

              • 4. Re: Issues with related records

                     The two scrips are located in separate files that have been linked via "manage data sources". They are related via UMID.

                     The UMID is entered by a new employee in the database that contains the script on the left.

                     I didn't realize that you can't share variables between two files, I have since changed the script on the right to grab the UMID variable from the database that contains the script on the left but I'm still having problems with adding additional employees.

                     When there are no records in either database the transfer works beautifully but subsequent test cases fail to generate a related record with the script on the right under the "work history" table








                • 5. Re: Issues with related records

                       I need to see what you now have since you made the changes you described in your preceding post.


                            They are related via UMID

                       So you have this relationship?

                       Employees::UMID = WorkHistory::UMID


                            The UMID is entered by a new employee

                       This is not the ideal approach to use. An interally generated serial number or an auto-enter calculation with Get ( UUID ) is a MUCH safer way to link your records in a relationshp. If you need some kind of manually entered identifier, you can use a data field in Employees for this and use it to find the employee record in a find, then you can access the related work history records that are linked via internally generated primary key.

                  • 6. Re: Issues with related records

                         Yup, here is an in-depth description of the databases and relevant relationships, hopefully this will clear everything up:

                    There are two databases: Student Forms and Test Database

                    •           Student forms will serve as the database used in FM Go 
                    •           Test database which will be used to keep track of all the students on the backend

                    There are three tables that are relevant to the problem that I'm having (with the database names in parantheses):

                    •           Student Info Page (Located on the studen forms database)
                    •           Employee Information (Test Database)
                    •           Work History (Test Database)

                    They are all linked via UMID.

                    •           Student Info Page - Does not matter about UMID since the record is deleted once it is submitted to the test database
                    •           Employee Information - UMID is UNIQUE, there can not be duplicates
                    •           Work History - There can be multiple records with the same UMID since an employee can have worked multiple times
                    •           Think of the Employee Information and Work History as a one-to-many relationship
                    •           The work history table is displayed as a portal on the table that displays the "employee information " data

                    Your second suggestion can definitely be implemented but I think I would still run into the same problem of not being able to properly link records


                    • 7. Re: Issues with related records

                           My second suggestion is to make your system less vulernable to data entry errors and the complications that ensue when you try to correct the errors. Validating to ensure unique values is a necessity, but does not protect your system from the complications that can ensue if the value is entered incorrectly and then related records are linked to it.

                           This is separate from the issue you are dealing with here, though. I'm simply suggesting a way to make your basic data model more robust.

                           I read through the left hand script and stopped at the first serious issue:

                           Go to Layout ["PPE" (PPE) ]
                           Save Records as PDF [.....

                           You are chainging layouts to a different layout based on a different table or table occurrence (PPE), but there is nothing in your script that performs a find or does a Go To Related Records to make sure that the record or records that makes up the found set on the PPE layout has any connection whatsoever to the current record in Student Info. (I am assuming that the left script is on a layout based on Student Info when it is performed.)

                      • 8. Re: Issues with related records

                             So I made a few changes

                             1. I Changed the "Go to Layout" to "Go to related record" which worked beautifully. Your post on GTRR was very helpful: The Complete Go To Related Record

                             2. I created two new "ghost" layouts so to speak whose tables are from the other database file.

                             By implenting #2 I have fixed all the errors, it's not as I clean as I would have liked but it works and doesn't appear to have any lag

                        • 9. Re: Issues with related records

                               Here is a screenshot of the new changes