11 Replies Latest reply on Apr 14, 2011 12:02 PM by JessicaKeyes

    Auto-create record in related table

    JessicaKeyes

      Title

      Auto-create record in related table

      Post

      I know this has been discussed, but I'm very new to FM and I haven't been able to figure out how to solve my problem.

      I have a very simple database for a veterinary ultrasound business.  Table 1 contains individual records for each scan that has been performed.  Table 2 contains the names, species, and breeds of the patients with portals to show the important data from Table 1 (some patients have multiple scans, but most do not).  I'm doing the bulk of my data entry into Table 1, and I want it to automatically create new patient records in Table 2 when I put someone new into Table 1. What is the best way to do this?

      Also, I've seen a lot of posters referring to record IDs, and I understand that this is a number that can be automatically generated. Is this necessary? What do you miss out on if you don't create record IDs?

      Thanks!

        • 1. Re: Auto-create record in related table
          philmodjunk

          I think you are confusing two slightly different things. There are auto-entered serial numbers and there are record ID's. Record ID's can be referenced with Get ( RecordID ), but aren't a good choice for the field that links on table to another. A number field defined to auto-enter a serial number is a much better field to use for this purpose.

          1st you need to relate your two tables. Let's call Table1, Scans and Table2, Patients. You may have already done this. In Manage | Database | Fields, define a field, PatientID, in the Patients table as a number field, select Option, click the auto enter tab and specify the serial number option. Define a matching number field in Scans, but do not define it as an auto-entered serial number.

          Now click the Relationships tab and drag from PatientID in the one table occurrence box to PatientID in the other. Double Click the relationship line and edit the relationship so that "Allow creation of records via this relationship" is selected for Scans.

          Now, on your Patients layout, you can place a portal to scans and use it to record the info for your ultrasound scans. You'll be able to create new scans records automatically just by entering data in the bottom blank row of this portal.

          Chances are that there are some of these terms and concepts are new to you. You can look them up in FileMaker help to learn more and feel free to ask follow up questions.

          • 2. Re: Auto-create record in related table
            JessicaKeyes

            Thank you so much for step-by-step answer! I've spent enough time trying to figure this out that I think I understand all the terms you used. I didn't know that I could enter data in a portal though, so that's especially helpful.

            I already have a few hours worth of data entry done on the Scans table, which I imported into the Patients table using a script. Will I be able to make the changes you described without having to start from scatch?

            • 3. Re: Auto-create record in related table
              philmodjunk

              Probably, but it seems strange that any data other than the serial number would be in both tables. The trick to good database design is to not put the same data (other than the match fields in your relationships) in more than one table. If you do, then it becomes easy for edits to such data to result in one copy of the data no longer matching that found in the other.

              • 4. Re: Auto-create record in related table
                JessicaKeyes

                Thanks for the advice.  I'll keep the Scans table get rid of the current Patients table. Can I manually enter a PatientID on the Scans table for each patient, then tell the new Patients table (linked only by ID) to display the records, then continue with automatic PatientID number generation from that point on, while doing the rest of the data entry from the Patients table?

                • 5. Re: Auto-create record in related table
                  philmodjunk

                  You can manually enter the numbers and then change the "next serial value setting" so that it provides the correct number for the next new record. You can also use Replace Field Contents to load an entire set of records with serial numbers in one go to get them fixed for use with the new relationship.

                  • 6. Re: Auto-create record in related table
                    JessicaKeyes

                    Great! It's working. Thank you so much for your help, Phil! I'd send you flowers if I could :)

                    • 7. Re: Auto-create record in related table
                      JessicaKeyes

                      I have another question.

                      In my Patients layout I made the portal and it's displaying all the records I had already entered perfectly.  When I made the portal I moved the fields around in the layout in order to make it more visually appealing, but now when I create new records on the Patients table the fields that are not in-line with the others get made into a separate record in the Scans table. Additionally, when I add a new record in an existing Patient record, it doesn't realise that the Name, Species and Breed already filled out in the other fields belong with that record too, though it does assign the correct PatientID.

                      In the screencap below, the Name, Species and Breed fields at the top left started out inline in the same portal as the box below. Is there a way to achieve this sort of layout while still making sure that all the fields stay in one record when I enter them?

                      • 8. Re: Auto-create record in related table
                        philmodjunk

                        Assuming Patient describes the animal and not the animal's owner, Name, Species and Breed should not be defined in the scans table. These should be fields defined in the Patients table. You don't want to have to enter that data with every scan, correct?

                        • 9. Re: Auto-create record in related table
                          JessicaKeyes

                          Yes, that makes sense.

                          I suppose I can then create a portal in the Scans layout to show that info?

                          • 10. Re: Auto-create record in related table
                            philmodjunk

                            Since it's one patient to many scans, there's no need for the portal. You can just add the fields from Patients to the Scans layout.

                            (Just so you know what should happen in more general situations, when you add fields from a related table to your layout, they will display data from the "first" related record. That's the top row of your portal if the portal is unsorted. Since you'll only have one Patient record linked to any given scan record, this is exactly what you want here.)

                            • 11. Re: Auto-create record in related table
                              JessicaKeyes

                              This is perfect. Thank you so much!