1 2 Previous Next 17 Replies Latest reply on Aug 4, 2014 9:32 AM by SteveFransen_1

    Creating a new record in a table with match fields storing key fields from records in two other...

    SteveFransen_1

      Title

      Creating a new record in a table with match fields storing key fields from records in two other tables?

      Post

           I want to create a new record in an Encounter table that includes two match fields storing key fields from records in two other tables: Patient and Provider. I can create a button on a Patient table layout, use a script to store the Patient record key field in a variable, go to an Encounter table layout, create a new record in the Encounter table, and set the match field in the new Encounter record to the Patient record key field stored in the variable. But then I have to repeat the process to get the key field from the Provider table into it's match field in the Encounter table record.

           What I'd like to do is create a single layout from which the user can look up a Patient record from the Patient table, look up a Provider record from the Provider table, store the key fields from both, then tap a button to create a new Encounter record in the Encounter table with the key fields stored in their respective match fields in that new Encounter record.

           Thanks in advance for you help!

           Steve

        • 1. Re: Creating a new record in a table with match fields storing key fields from records in two other...
          philmodjunk

               You appear to have these relationships:

               Patient----<Encounter>-----Provider

               Have you considered using a portal to Encounter for this process. No scripting is then required. You can put a portal to Encounter on the Patient layout and select a provider by formatting the key field for Provider in the Encounter table with a value list.

               But you can also use a script to do exactly what you describe. Just set up a search mechanism for Patients (drop down list, search field with a find script, search portal....) and another for Provider. Set them up to enter the needed ID values into a pair of global fields or global variables, then have your script switch layouts, create the new record and then have a pair of Set field steps copy the needed keys from the global fields or variables into the needed foreign key fields in the new Encounter record.

          • 2. Re: Creating a new record in a table with match fields storing key fields from records in two other...
            SteveFransen_1

                 My search mechanism, when using scripts, is less elegant than your suggestion. I'll work on that but would like to try using portals anyway. I simplified my initial description for clarity since there is a Facility relationship as well:

                 Encounter>----Patient

            Encounter>----<Provider

                 Encounter>----Facility

                 So, if I understand, you're suggesting I set up portals for Patient, Provider and Facility on an Encounter layout and go from there, correct?

                 Thanks!

            • 3. Re: Creating a new record in a table with match fields storing key fields from records in two other...
              philmodjunk

                   What I am suggesting would use a Portal to your Encounter table, not a layout based on Encounter.

                   To expand on my original suggestion:

                   Patient-----<Encounter>-----Provider

                   Patient::__pkPatientID = Encounter::_fkPatientID
                   Provider::__pkProviderID = Encounter::_fkProviderID

                   You can place a portal to Encounter on the Patient layout to list and select Provider records for each given Patient record. Fields from Provider can be included in the Portal to show additional info about each selected Provider record and the _fkProviderID field can be set up with a value list for selecting Providers records by their ID field.

                   Since you also have a related Facility table, you'd have these match fields:

                   Facility::__pkFacilityID = Encounter::_fkFacilityID

                   and you'd also format the _fkFacilityID field as a drop down list of Facilities so that you can create an Encounter record linked to the current Patient record and then link it both to an existing Provider record and also to an existing Facility record.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              • 4. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                SteveFransen_1

                     Thanks Phil, I'm getting closer. I like your key nomenclature. For now I'm including the table name in each field name and I'm using UUID's for keys.

                     To summarize:

                Patient-----<Encounter>-----Provider and Facility-----<Encounter

                     I set up an Encounter portal on a Patient layout showing all the keys, so I can see what's going on, as well as Provider::ProviderLastName and Facility::FacilityName so I have something meaningful to do a Find on. They're set up with a value list for selecting Provider records by Provider::ProviderLastName and Facility records by Facility::FacilityName. This is exactly where I wanted to go with this!

                     Qustion #1: Do you explicitly set the following field values somewhere or are you just stating what they will become?

                Patient::__pkPatientUUID = Encounter::_fkEncounterPatientUUID

                Provider::__pkProviderUUID = Encounter::_fkEncounterProviderUUID

                Facility::__pkFacilityUUID = Encounter::_fkEncounterFacilityUUID

                     I can Find on Provider::ProviderLastName or Facility::FacilityName but when I hit the Perform Find button I get en error "No records match this find criteria".

                     Question #2: Unless I'm missing something I haven't created a new Encounter record yet. What else do I need to do?

                     I appreciate your help,

                     Steve

                • 5. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                  philmodjunk

                       All __pk fields should be set up to auto-enter either a serial number or Get (UUID). If you use the latter, it's a good idea to also specify a unique values validation field option. If nothing else, that setting causes the relationship graph to more clearly show which relationships have to be one to many.

                       The _fk fields in the join table (Encounter) have to receive a value in some way or the record will not link to an existing record in the specified parent table (Provider, facility or patient). How that value is entered in to the field depends on the design of your layout.

                       With a portal to Encounter placed on the Patient layout and with "allow creation of records via this relationship" enabled for Encounter in the Patient to Encounter relationship, simply entering data into a field in the blank "add row" just below the last actual portal record will automatically create a new Encounter record and copy the value of Patient::__pkPatientID from the current patient record into the _fkPatientID field of that new Encounter record.

                       If you then format _fkProviderID with a value list (drop down list or pop up menu usually) of Provider ID's and Names such that selecting a provider by name enters the associated __pkProviderID, Selecting a value in this field in the Add Row will: a) create a new record, b) copy over the __pkPatientID and c) enter the __pkProviderID. This would leave you with a new record linked to a provider and a patient, but not yet to a facility. If you format the __fkFacilityID field with a similar value list of Facility ID's and names (which can be a conditional value list of only the Facilities associated with the selected provider), you can then select a facility in this drop down to finish linking the new encounter record to the current patient and a selected provider and facility.

                       Now just for "grins", note that if you placed this portal to encounter on a facilities layout or a provider layout, you can get the same results by enabling "allow creation..." and setting up value lists so that you can select patients and providers for a given facility or facilities and patients for a given provider....

                       You might find this demo file on Many to many relationships of interest: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                       It's in the older file format so users with FileMaker 12 or newer will need to use Open from FileMaker's File menu to open this file and thus produce a copy of the file converted over to the newer file format.

                  • 6. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                    SteveFransen_1

                         Closer but still not there so I've simplified the environment to:

                    Patient-----<Encounter

                    Patient::_pkPatientUUID = Get(UUID) on the creation of a new Patient record.

                    Patient::_pkPatientUUID = Encounter::_fkEncounterPatientUUID

                    For testing purposes I added a text field Encounter::EncounterComments

                    With a portal to Encounter placed on the Patient layout and with "allow creation of records via this relationship" enabled for Encounter in the Patient to Encounter relationship, simply entering data into a field in the blank "add row" just below the last actual portal record will automatically create a new Encounter record and copy the value of Patient::__pkPatientID from the current patient record into the _fkPatientID field of that new Encounter record.

                         When I add a new Patient record and a comment in Encounter::EncounterComments, which is the only field in the Encounter portal, everything works as you describe and I end up with a one new Patient and one new Encounter record with Patient::_pkPatientUUID = Encounter::_fkEncounterPatientUUID and the comment in Encounter::EncounterComments.

                    However, when I simply add a new comment in the new Encounter::EncounterComments field which appears in the "add row" of the portal I don't create a second Encounter record.

                    Do I need to create an "Add Record" button as you did your demo link? That appears to illustrate a many to many relationship. I believe my Encounter to Patient relationship is one to many and "should" work as you describe above if I've implemented it correctly. I'd like to get that working before adding back the Provider and Facility relationships and fields which are many to many.

                         Thanks,

                         Steve

                    • 7. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                      philmodjunk

                           Now I'm confused.

                           

                                When I add a new Patient record and a comment in Encounter::EncounterComments, which is the only field in the Encounter portal, everything works as you describe and I end up with a one new Patient and one new Encounter record with Patient::_pkPatientUUID = Encounter::_fkEncounterPatientUUID and the comment in Encounter::EncounterComments.

                           and

                           

                      However, when I simply add a new comment in the new Encounter::EncounterComments field which appears in the "add row" of the portal I don't create a second Encounter record.

                           seem to contradict each other. As I read it, this is one and the same field in two different rows of the same portal and is, in fact, two instances of doing exactly the same thing. So I am puzzled as to why it would work the first time and not the second. (The first, time this happens, you are also using the "add row".) If this were not set up correctly, entering text in the comment field would not work as the system would not allow you to enter the field.

                           What do you see that leads you to believe that you are able to enter data into this comments field and not get a new record in the Encounter table?

                      • 8. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                        SteveFransen_1

                             I use an Encounter layout to watch what's happening with the Encounter table. It contains all the Encounter fields but nothing else.

                             Let's start with 0 Patient records and 0 Encounter records, then create a new Patient record from the Patient layout and enter "This is the first Encounter record comment" in the Encounter::EncounterComment field in the Encounter portal on the Patient layout. A new "add row" appears with another instance of the Encounter::EncounterComment field in the Encounter portal.

                             At this point I have one new Patient record and one new Encounter record. When I browse that Encounter record from the Encounter layout it’s Encounter::EncounterComment field contains "This is the first Encounter record comment".

                             Now go back to the Patient layout and add "This is the second Encounter record comment" in the “add row” and click somewhere else on the layout and a new "add row" appears with another instance of the Encounter::EncounterComment field in the Encounter portal.

                             However, now I still have just one Patient record and one Encounter record. The Encounter::EncounterComment field of the one Encounter record contains "This is the first Encounter record comment".

                             This may or may not be related but is unexpected…

                             If I delete the single Encounter record from the Encounter layout, the single Patient record is deleted as well. This is unexpected since I have not checked “Delete related records in this table when a record is deleted in the other table” for the Patient or Encounter table in the Edit Relationship dialog box.

                             Thanks-again,

                             Steve

                        • 9. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                          philmodjunk

                               Something is clearly not set up as expected. Please check the following:

                               Enter layout mode on your Patient layout. Check to see exactly what text appears to the right of "table" in the status area tool bar at the top left of yoru screen. Thus must read "patient" and be exactly the same text used as the name of the table occurrence box for this table in your relationships graph.

                               Then double click a lower portion of your portal and check to see what text shows in "show related records from". This should read "encounter" and be exactly the same name shown in the table occurrence for Encounter that is linked to the table occurrence for Patient.

                               Now click the comments field inside the portal row and check the text to the left of :: in the Display Data From box on the Inspector's Data tab. This text should read "Encounter" and also exactly match the name of the Occurrence box for Encounter in your relationships chart.

                               Finally, go to Manage | Database | Relationships and hover the mouse over the arrow in the upper left hand corners of the Patient and Encounter table occurrence boxes. Make sure that the data source table name that pops up is consistent with the name of the occurrence box name.

                               If you find any discrepancies in that, please let me know. What you describe sounds like either the layout, or the portal is not based on the correct table occurrence or you have a table occurrence that does not refer to the correct data source table.

                          • 10. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                            SteveFransen_1

                                 Hi Phil,

                                 It all looked okay but I heard your concerns so I started over with a new database with only two tables, Patient and Encounter; and fields _pkPatientUUID, PatientLastName, _pkEncounterUUID, _fkEncounterPatientUUID, and EncounterComments.

                                 Then I set everything up as you recommended with an Encounter portal on the Patient layout and it all works perfectly!

                                 So, there's something wrong with my first database which I'll unravel tomorrow… or simply rebuild from the new database one table at a time.

                                 Thanks for your guidance,

                                 Steve

                            • 11. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                              SteveFransen_1

                                   It looks like I declared victory a bit too soon. Problems reappeared when I tried to bring in the Provider records.

                                   This all works perfectly...

                              With a portal to Encounter placed on the Patient layout and with "allow creation of records via this relationship" enabled for Encounter in the Patient to Encounter relationship, simply entering data into a field in the blank "add row" just below the last actual portal record will automatically create a new Encounter record and copy the value of Patient::__pkPatientID from the current patient record into the _fkPatientID field of that new Encounter record.

                                   Here's where I'm having problems...

                              If you then format _fkProviderID with a value list (drop down list or pop up menu usually) of Provider ID's and Names such that selecting a provider by name enters the associated __pkProviderID, Selecting a value in this field in the Add Row will: a) create a new record, b) copy over the __pkPatientID and c) enter the __pkProviderID. This would leave you with a new record linked to a provider and a patient

                                   Here's what I have:

                              Patient-----<Encounter>-----Provider

                              Patient::__pkPatientID = Encounter::_fkPatientID

                              Provider::__pkProviderID = Encounter::_fkProviderID

                                   In addition to the Encounter fields that are working as expected, I added Provider::_pkProviderID and Provider::LastName to the Encounter portal on the Patient layout. Then I formatted that Provider::LastName field as a Drop-down list with Values from: Provider::LastName.  Now however, I can't even enter those fields by clicking on them or trying to tab into them.

                                   In your comments you wrote format _fkProviderID. That's from the Encounter table, right? If so, how would I do this next step?

                              format _fkProviderID with a value list (drop down list or pop up menu usually) of Provider ID's and Names

                              That field, Encounter::_fkProviderID is supposed to be set equal to Provider::__pkProviderID at some point. How does that happen if I set it up with a value list of names from, for example, Provider::LastName.

                              I think I'm confused.

                              • 12. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                                philmodjunk
                                     

                                          I added Provider::_pkProviderID and Provider::LastName to the Encounter portal on the Patient layout. Then I formatted that Provider::LastName field as a Drop-down list with Values from: Provider::LastName.  Now however, I can't even enter those fields by clicking on them or trying to tab into them.

                                     Why add the _pkProviderID field to this layout? Why format LastName with a drop down list? If you enter those fields and modify data, you are editing data in the Provider table and a) this should never be permitted for a _pk field and b) such a change will then appear for all Encounter records linked to that Provider record. And that's why you cannot enter data in these fields. They are not from Encounter and will not be editable until you link that encounter to a Provider--which hasn't yet happend.

                                     

                                          That's from the Encounter table, right? If so, how would I do this next step?

                                     Yes, and until this works, you can't edit fields from Provider that you might place in the portal row.

                                     Open Manage | value Lists

                                     Create a new value list, name it and select the "use values from a field" option.

                                     Select Provider in the top left drop down of the dialog that appears and click the __pkProviderID field to select it as the first field. Then select the "also use values from" check box and select the Provider Name field as the second field. If you have two or more fields for provider name, Define a new text field, ProviderFullName, and set it up with an auto-enter calculation that combines the last, first name field values into a full name. Select "unique value" on the validation tab for this field as the name must be unique for each provider with this "beginner level" value list.

                                     Click OK until each dialog has closed.

                                     Now select the Encounter::_fkProviderID field while in layout mode and use the Inspector to set this up as either a drop down list or popup menu. If you select "show only values from second field" option, the layout will only show names, but enters the associated ID value into the _fk field. If you specify a popup menu format, you'll still see the provider name when you exit the field. If you prefer the drop down list format, you can include the provider name field from the Provider table to show the name after you select a provider and exit the field.

                                     Note: this type of value list is the one most often found in tutorials as it works and is easy to set up. But it isn't very practical once your list of values increases in size. But once you have this version working, you can experiment with more sophisticated selection methods such as:

                                     A conditional value list where you select a "category" in one field and then the value list only lists values that are a member of that category.

                                     A drop down list set up on a text field that auto-completes on a name. This method requires an additional relationship and scripting to handle such things as a name that does not yet exist in your list and multiple values with the same exact name.

                                     A search portal--possibly installed inside a popover, where you select filtering values and enter search text used in a partial text matching scheme to progressively narrow the values listed in the portal. Clicking a row in the portal clicks a button that enters the selected value into your _fk field to select that provider. (One advantage to this approach besides more sophisticated partial text matching that auto-complete is that you can sort the values listed in the portal in any desired order--even an arbitrary order.)

                                      

                                • 13. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                                  SteveFransen_1

                                       That cleared up several misunderstandings and everything works now. I didn't realize the potential power of value lists either. I'm going back to my original database to implement this correctly then move on to the tables with many to many relationships. Your portal approach is much cleaner and more elegant than using the scripts I had considered.

                                       Earlier in this thread you mentioned...

                                       

                                            All __pk fields should be set up to auto-enter either a serial number or Get (UUID). If you use the latter, it's a good idea to also specify a unique values validation field option. If nothing else, that setting causes the relationship graph to more clearly show which relationships have to be one to many.

                                       I understand the importance of assuring all keys are unique and I can set that for _fk fields with the Options for Field | Validation | Unique value checkbox. On the other hand,  I can't get to that dialog when I look at the options for _pk fields since they're calculated fields set to Get(UUD). Instead I get the Specify Calculation dialog, and the ability to set Storage Options, but where do I specify a unique values validation field option for a calculated field.

                                       Thanks again Phil,

                                       Steve

                                        

                                  • 14. Re: Creating a new record in a table with match fields storing key fields from records in two other...
                                    philmodjunk
                                         

                                              and I can set that for _fk fields with the Options for Field | Validation | Unique value checkbox.

                                         Do NOT specify unique values for _fk fields. In almost all cases, _fk field values CANNOT be unique as you'll have multiple records in the same table with the same value in the _fk field because they are linked to the same parent record and thus have to match the same __pk value in that parent record.

                                         

                                              but where do I specify a unique values validation field option for a calculated field.

                                         __pk fields should not be of type calculation. if you are using the Get ( UUID ) method, (not my first choice in most situations), you should define this as a text field with an auto-enter calculation specified on the auto-enter tab in field options. Then you can also click the validation tab and select the unique values field option.

                                    1 2 Previous Next