11 Replies Latest reply on Jun 7, 2011 4:21 PM by philmodjunk

    Creating new related records with Client ID and Patient ID already filled in.

    CraigKiebler

      Title

      Creating new related records with Client ID and Patient ID already filled in.

      Post

      FMP 11

      I am trying to set up a database that has tables for veterinary clients, patients, and treatment sessions. I'd like to enter a new client record, then be able to open a new patient record based on the initial client record (each client can have multiple associated patients). Likewise, each patient record would have multiple treatment session records - hence, opening a new session record would be linked to the patient. Basically, I'd like each client ID to be automatically entered onto the patient record when opening a new patient record and each patient ID entered upon opening a new session record.

      I've tried to use the 'set file' script, but I can't seem to get it working. Any help would be appreciated. I'm new to FMP and this problem has been nagging at me for hours.

        • 1. Re: Creating new related records with Client ID and Patient ID already filled in.
          philmodjunk

          The details depend on your relationships and the design of your layouts.

          Starting with the simplest approach:

          Define these relationships:

          Clients----<Patients---<Treatments

          Clients::ClientID = Patients::ClientID   (Enable allow creation of records via this relationship for Patients)
          Patients::PatientID = Treatments::PatientID (Enable allow creation of records via this relationship for Treatments)

          Clients::ClieintID and Patients::PatientID should be defined as auto-entered serial numbers.

          With this setup, if you place a portal to patients on your clients layout, you will not need anything more to add a list of patients for a given client, you can simply enter data into the bottom blank row of the portal and a new record will be automatically created in Patients with the client's ID automatically entered for you. A portal to Treatments will function the same way.

          With portals, it isn't really needed, but a script can also be written so that clicking a "new client" button creates both a new client record and also a matching new patient record:

          Go to Layout [clients] //must be on a layout based on clients or this won't work.
          New Record / Request //new client record
          Set Field [Patients::ClientID ; Clients::ClientID] //creates new patient record.

          The set field step will create a new record in Patients only if you've enabled the "allow creation option" for the relationship between clients and patients.

          • 2. Re: Creating new related records with Client ID and Patient ID already filled in.
            CraigKiebler

            Thanks for the info. Yeah, I really don't want to use portals on this section. I've tried setting the script parameters and every time I try different combinations (to include your Set Field [Patients::ClientID ; Clients::ClientID] //creates new patient record, the script specify, calculated result section returns, "A number, text constant, field name or “(” is expected here."

            My tables are "Client Info" and "Patient Info" with "Client ID" as the field. Could this be due to the fact that I have a space in the name? 

            Basically, I want to push a button on the Client Info layout that says 'new patient', and it go to the patient info layout and have already created a record with the associated client id. Same thing for patients and sessions.

            Clients can have multiple associated patients and patients can have multiple associated sessions, so I need to be able to add new ones to each as time progresses.

            Thanks in advance.

            • 3. Re: Creating new related records with Client ID and Patient ID already filled in.
              philmodjunk

              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. Click OK. 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.

              And the information that starts with // is a comment that should be either left out or included to the left of the ] (I intended it as an expanded explanation to you not as something to include in the script step.)

              • 4. Re: Creating new related records with Client ID and Patient ID already filled in.
                CraigKiebler

                Ok, the problem was that I was trying to enter the entire expression under the 'calculated result' button. So, the 'set field' expression is all set up. I give up, though, because it still won't work (you would think this would be a commonly requested functionality...clients---<invoice say...

                I have Client Info table, Patient Info table and Session info table, each with their own specific layouts. Client ID and Patient ID are auto-generated numbers and Client ID is the key between Client Info table and Patient Info table. The 'allow creation of records' buttons are all clicked. The only think I can think of is there's something screwed up with the field on the Patient Info table layout for Client ID. I have 'Patient Info::Client ID', but have also tried 'Client Info::Client ID' for the field on the Patient Info layout.

                • 5. Re: Creating new related records with Client ID and Patient ID already filled in.
                  philmodjunk

                  In what way exactly does this "not work"? How it fails for you could be a useful clue here.

                  The script I posted does not depend on the presence or absence of any fields on either layout.

                  • 6. Re: Creating new related records with Client ID and Patient ID already filled in.
                    aammondd

                    If you are not using portals you have to do things a bit differently.

                    When you move to the patient layout and create a new record you dont have access to the ClientID value any longer.

                    The way to pass this value in order  to set the field properly is either by Variable or by Global Field.

                    You set the variable or global field to the clientid value before moving to the patient layout.

                    Then set the patient:clientID field.  Set Field (Patient:ClientID; Global Field or $variable name)

                    (You may want to include a portal on your client layout of patients just in case a patient returns you could go to thier record without having to create a new record.)

                    The allow creation of records piece of the relationship is designed to allow the creation through a portal. You can still use a portal to create the key connections and use a button on the portal to then move to the related record to continue to fill out the patient information.

                     

                    • 7. Re: Creating new related records with Client ID and Patient ID already filled in.
                      CraigKiebler

                      The new record opens for a patient and the client ID is not present...basically just opens a new blank record with no link to the client.

                      I planned on using a portal to show associated patients on the Client layout. I like the idea of adding in initial patient info and then going to related record. I'll also try setting the global field. Again, I'm wading my way through this with no filemaker experience. I appreciate all the help!

                      • 9. Re: Creating new related records with Client ID and Patient ID already filled in.
                        aammondd

                        What you describe is what happens when you dont use a portal to create the keys first. In a portal of patients names you could add the name to the last portal row which would create a new record and then press a button toGo To Related Record to continue filling in info.

                        this same button could be used to take you to an existing patient record

                        A similar concept could be used on the patient layout to view/add  procedures/treatments

                         

                         

                        • 11. Re: Creating new related records with Client ID and Patient ID already filled in.
                          philmodjunk

                          Allow creation of record via this relationship can also be used to create related records without using a portal.

                          Set Field [patients::ClientID ; Clients::ClientID ]

                          Will create a related record in patients if executed from a layout to the clients table provided the "allow create" option is enabled for the relationship. I've used this method frequently. In fact you can assign a value to any field in patients and the related patients record will be created with the required value from Clients::ClientID entered into Patients::PatientID just like adding a new record in the bottom blank row of a portal.

                          The only difference is that this action can only create a single new record in patients and only if no related patients records exist for the current client record.

                          If you check the number of records in Patients after running the script, you should see that the number of records in the table has increased by one. What you may not see is that the current record in patients may not be the one this script just created, you may need to perform a find or use Go To Related Records to bring it up on the Patients layout.