6 Replies Latest reply on May 5, 2014 9:41 AM by nickphillips

    FMP 13 - create related records - how to ?



      FMP 13 - create related records - how to ?


           I am a surgeon with an imported excel database containing patient names and demographic data.I am using this as the parent table. I have set up 2 child tables to display info about 

           1. the surgery those patients have had.

           2. MRI scan data.

           I have 160 patients and records in the demographics table , but no records in the other two tables.

           Whats the best way of creating records already filled with the relevant demographics in the other two tables for me to populate with data about the surgery or MRI (which I will need to do manually) ?.

           Currently they are linked in a relationship diagram using primary and foreign key fields. I have  navigation buttons on the parent table which take me to empty records on the surgery and MRI tables. I thought I would like the button to create a linked record from the relevant patient demographics. Instead it just takes me to the table, I then need to create a new record and search for the right patient to pull the demographics through.

           perhaps I have set it up badly ?.




        • 1. Re: FMP 13 - create related records - how to ?

               You haven't described what you set up the button to do. Normally such a button would perform a script like this to create a new record in the Scans table:

               Set Variable [$PatientID ; Value: __patienIDpk ]
               Go to Layout ["Scans" (Scans) ]
               New Record/Request
               Set Field [Scans::_patientID_scans_fk ; $PatientID ]

               Note: When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

          • 2. Re: FMP 13 - create related records - how to ?

                 Thanks Phil. I was way off track because I had just assigned it to go to the scan or surgery layout , no wonder it didn't create a new record.

                 My first attempt at your help has just created a new field without pulling the demographics through. I think this is because I am unclear what I am doing with the set field part. Am I defining the patientIDfk field to relate to the patientIDpk field to pull the demographics through ?.


            • 3. Re: FMP 13 - create related records - how to ?

                   You are linking the new record to the current Patient record. To see the demographic fields from Patient, do the following:

                   Enter layout mode.

                   Drag and drop from the field tool to add a new field to the layout.

                   This opens the specify fields dialog.

                   Select your patient table from the drop down at the top of the dialog.

                   Click to select a demographic field.

                   Repeat this for each field you want to add.

                   Note: FileMaker 13 users can use the new field picker tool to save a lot of steps as this opens a dialog from which you can select multiple fields at once, but you still need to first select the correct table, then select the desired fields.

                   This also assumes that you have set up a relationship in Manage | Database | relationships to link the two tables by these ID fields and only these ID fields.

              • 4. Re: FMP 13 - create related records - how to ?

                have the relationship set up so that the linked ID fields (patientIFpk in patients and patientIDfk in surgery also show the surnames of the patients - see  highlighted field in the screenshot) so that I can scroll through the surnames. when I hit a particular surname in the list it accurately pulls through the patient fields at the top of the screenshot as described in your last post. So I think thats working.

                     My script must be wrong somewhere, because tho it makes a new record in the surgery table it is empty and the patient demographics from the table I have hit the button on are not there.

                     this is my script , I am not sure its right.


                      Set Variable [$patientID; Value:chordoma_patients::__patientIDpk]

                     Go to Layout["surgery" (surgery)]

                     New Record/Request

                     Set Field [surgery::_patientID_surg_fk; surgery::_patientID_surg_fk]


                • 5. Re: FMP 13 - create related records - how to ?

                       Your set field step is incorrect. It does not refer to the variable and thus fails to enter the correct value into the fk field.

                       Set Field [Scans::_patientID_scans_fk ; $PatientID ]

                  • 6. Re: FMP 13 - create related records - how to ?

                         Thanks Phil , I was trying to click this in from the script edit box , now that I have manually typed it it is working. I will do the same for the scans table. Many thanks