11 Replies Latest reply on Oct 9, 2012 3:08 PM by philmodjunk

    Creating unique parent records from child table

    jlin

      Title

      Creating unique parent records from child table

      Post

           Hi,

           I am a FM beginner and am developing my first database for use in a meta-analysis (a quantitative synthesis of multiple empirical studies).  I have a fairly simple question but have not been able to find the answer in any available training materials or forums.       

           I need to track information about STUDIES, the main entity.  Each study comes from one journal, and each journal can be associated with multiple studies.  Now, I also want to track journal impact factors (and their relationship with study effect sizes) and therefore created a separate table, JOURNAL.  So, JOURNAL --> STUDIES is a one-to-many relationship, with JOURNAL being the parent. 

           My goal is to enter data from the STUDIES table in the related JOURNAL:: Journal Name field, and thereby populate the blank JOURNAL table with unique records, meaning one record for each journal.  When I try to allow creation of new records based on this relationship, the values in the JOURNAL table duplicate, so I have multiple records of the same journal. (So, if I enter "Nature" in the Journal Name field for 2 records from the STUDIES table, the JOURNAL table then shows 2 records that each say "Nature.")  I just want a running list of journals so that as I enter data about studies, I have a comprehensive list of all the journals represented.    

           Any help would be appreciated.  Thank you. 

        • 1. Re: Creating unique parent records from child table
          philmodjunk

               What match fields are you using for your relationship?

          • 2. Re: Creating unique parent records from child table
            jlin

                 Journal ID is the only match field. 

            • 3. Re: Creating unique parent records from child table
              philmodjunk

                   There must be a pair of match fields. I take it that you have this relationship?

                   Journal::JournalID = Studies::JournalID

                   And is JournalID defined as an auto-entered serial number in the Journal table?

                   If so, Studies::JournalID should be set up with a value list of Journal ID's and titles from the Journal table. A button next to it can be used to add a new Journal when the title cannot be found in the existing list of titles.

                   Once you can get that working, if you want, there's a way to turn this around using a script and a second relationship that allows you to do this via an auto-complete enabled list of Journal Titles.

              • 4. Re: Creating unique parent records from child table
                jlin

                      

                     There must be a pair of match fields. I take it that you have this relationship?

                     Journal::JournalID = Studies::JournalID

                     And is JournalID defined as an auto-entered serial number in the Journal table?

                     If so, Studies::JournalID should be set up with a value list of Journal ID's and titles from the Journal table. A button next to it can be used to add a new Journal when the title cannot be found in the existing list of titles.

                --> Yes, I had done all of the above, except for adding the button solution, which is simple enough-- thank you.  It works.  As a side question, is this process of filling in the parent table via the child table somehow "wrong" or suboptimal?  For my current purposes, I could not find or think of another straightforward solution to what I thought was a simple problem, which made me think I might not be conceputalizing things correctly.

                     Once you can get that working, if you want, there's a way to turn this around using a script and a second relationship that allows you to do this via an auto-complete enabled list of Journal Titles

                --> Yes, this would be very helpful and appreciated.  Thank you very much for your input.

                • 5. Re: Creating unique parent records from child table
                  philmodjunk

                       Often Parent records have to be created from the context of a child record. So I wouldn't consider it wrong by any means. The trick is to make the process of generating the Parent record as simple and error free as possible.

                       Here are a pair of down load links to the same demo file:

                  https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                  https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                       Down load the one appropriate for your version of FileMaker.

                       Note that it has a script that checks for duplicate matches by name. The same script could modified to detect that there are no matches and then could ask the user if they want to add a new parent record or not. (That extra dialog asking them helps avoid automatically creating a new parent when the user simply mistyped the name.)

                  • 6. Re: Creating unique parent records from child table
                    jlin

                         Thank you for the demo.  One thing I am confused about is that the demo provides a script for identifying already existing duplicate contacts, whereas for my purposes, I don't want to create duplicate journal titles in the first place.

                         I am still having difficulty applying the script, so I may just use the button solution.

                    • 7. Re: Creating unique parent records from child table
                      philmodjunk
                           

                                One thing I am confused about is that the demo provides a script for identifying already existing duplicate contacts, whereas for my purposes, I don't want to create duplicate journal titles in the first place.

                           Which simply means that this portion of the script would never execute with your data. With many situations where names are recorded in a database, you can't ensure that they will always be  unique and thus this code contains scripting that will handle the case should it arise.

                           Note that this is the part of the script that I suggested modifying to detect when there is no matching record. This is where you can open a dialog saying "No Journal of this name exists. Create a new Journal Record?". It the user clicks yes, you'd add code to use the name entered to create a new record in the Parent table.

                      • 8. Re: Creating unique parent records from child table
                        jlin

                             I'm sorry, if you could please bear with me, I'd like to understand this and need some real spelling out.  

                             1) What field do I use for the matching record script?  

                             2) What is the purpose of the second relationship?  

                             I set up Studies:: Journal ID with as an auto-complete enabled  value list populated by Journals: Journal ID (and titles).  Is this part of the script solution, or does this just pertain to the manual button?  I'm unclear of the next steps.

                        • 9. Re: Creating unique parent records from child table
                          philmodjunk

                               1) See the script in the demo file. You've already referred to it in your previous post. The script uses a name based relationship to count the number of records. If it finds more than one, it presents them in a list so the user can select the individual that they want. In your case, this is the point where you can check to see if the count is zero and have the script offer to add a new Journal record.

                               2) There are two relationships to two occurrences of the same table so that you can have two different relationships.

                               1) In terms of database design and maintaining data integrity, it's best to link records by a serial Id number so you have one relationship that matches by ID number. Note that this relationshp remains unaffected even if you have records with duplicate names or you need to change a name. So this is why you have a relationship that matches by contact ID.

                               2) But in terms of setting up a user friendly interface, matching by names is much nicer. Thus a second relationship matches to the same table (but a different occurrence of it) by name fields instead of ID. This allows you to use an auto-complete enabled list of names where you can start typing a few letters and see a very long list of names quickly filter down to just the few that start with those letters.

                               To complete the operation, the _fkContactID field is set to use the name based relationship in a looked up value setting to look up the ID number of the record that matches by name. Where this can fail is if there should be more than one record in the contact table with the same name. This is where the script kicks in to check for multiple matches by name and if this is the case, it lists them for selection. I realize that if you are enforcing unique values on your journal names, this last feature is of no use to you, but I wanted to try to help you understand why both relationships make it possible to select by name, but link records by ID in a fashion that is more user friendly than the standard ID number based value list with the name displayed in the value list's second field.

                               Here's a modified form of the script that would ask the user if they want to add a new Journal record if the name entered doesn't match any existing journal record:

                               IF [ IsEmpty ( ContactsByName::__pkContactID ) // no records match by name]
                                  Show Custom Dialog [Invoices::SelectContact & " is not found in contacts. Create a new Contact record for this person?"]
                                  If [Get ( LastMessageChoice ) = 1 // OK was clicked (or whatever you name button 1 to be)]
                                     Freeze Window
                                     Set Variable [$Name ; value: Invoices::SelectContact]
                                     Go to Layout [Contacts]
                                     New Record/Request
                                     Set Field [Contacts::FirstName ; $Name ]
                                     Set Variable [$ContactID ; Contacts::__pkContactID]
                                     Go to Layout [original layout]
                                     Set Field [Invoices::_fkContactID ; $ContactID]
                                  End If
                               End If

                               You would just need to change the names here to match what you set up in your own database. (I didn't bother trying to add script steps to split a name entered into SelectContact into separate first and last names as I assumed that a Journal would only need a single name field.)

                          • 10. Re: Creating unique parent records from child table
                            jlin

                                 Thank you for spelling this out-- it works.  Two things eluded me: 1) linking records by name via ID, and 2) the concept of lookups.  I will have to practice this more to understand it better.

                                 One last question: 1) I tried the script without the red lines below, and I'm wondering why, upon creating the new journal record, the new journal ID doesn't automatically show up in the Studies::Journal ID and Journal::JournalID fields displayed from the Study layout?  It only shows up in Journals_Match::Journal ID.   

                                  

                            IF [ IsEmpty ( ContactsByName::__pkContactID ) // no records match by name]
                               Show Custom Dialog [Invoices::SelectContact & " is not found in contacts. Create a new Contact record for this person?"]
                               If [Get ( LastMessageChoice ) = 1 // OK was clicked (or whatever you name button 1 to be)]
                                  Freeze Window
                                  Set Variable [$Name ; value: Invoices::SelectContact]
                                  Go to Layout [Contacts]
                                  New Record/Request
                                  Set Field [Contacts::FirstName ; $Name ]
                                  Set Variable [$ContactID ; Contacts::__pkContactID]
                                  Go to Layout [original layout]
                                  Set Field [Invoices::_fkContactID ; $ContactID]
                               End If
                            End If

                            • 11. Re: Creating unique parent records from child table
                              philmodjunk

                                   Until the child record (invoices in this script has a matching value in _fkcontactsID there is no link between it and the new parent (contact) record that works with the ID number based relationship.