6 Replies Latest reply on Jan 23, 2014 8:25 AM by MichaelPoling

    Multiple table interaction



      Multiple table interaction


           Hi everyone, 

           Having a bit of trouble understanding what I did wrong here.

           I have 6 tables created with a number of fields.  The tables are designed to capture various elements of a patient examination.  They are Demographics, SubjectiveHistory, GeneralObjectiveAssessment, NeuroAssessment, ROM, ProgressNotes and __PrintPhysicianReport.

           I need to track several things repetitively.  For example, I have multiple patients, so I have a PatientID field in the Demographics table which is a primary key.  

           Each patient may have more than one injury, or attend at another time, so I have an EpisodeID (primary key) in the SubjectiveHistory table (not in the Demographics table, as the demographics will either be the same, or change and I don't have to track the previous demographics.).  

           Patients will attend more than once for a particular episode, so in the ProgressNotes table, I have a VisitID field (primary key)-not in the other tables because I'll only assess someone once for an episode, but will treat them multiple times for it, so I really only need visits tracked within the context of each episode.

           Finally, I might write reports to a physician once, or more, so I have a table for the printed reports and need to keep old reports, so I have a ReportID field (primary key) within the __Print_PhysicianReports table.

           I created foreign keys for PatientID within all of the other tables, relating them back to the Demographics Table.  Then I created another foreign key in each for EpisodeID and related it back to the SubjectiveHistory table.

           I expected that when I created a new record in Demographics, that I would be able to enter the SubjectiveHistory and other tables, but data entry seems to be blocked.  It reports that there is no record.  If I create a new record in the SubjectiveHistory it creates an entirely new record, completely blank and retaining none of the info entered in the Demographics layout.

           I am attaching a pic of the relationships.  Any ideas where I went wrong here?



        • 1. Re: Multiple table interaction

               In your relationships.. check to make sure you are allowing creation of records..

          • 2. Re: Multiple table interaction

                 Ahh, what a silly little mistake on my part!  Thanks so much!  I enabled one way (downstream) record creation and deletion and that solved the data entry issue.

                 It raises the next issue, however :)

                 Now when I am within the SubjectiveHistory tab on my layout (I created a tab on a single layout for data entry into each table for ease of navigation by the user) if I use the NewRecord button within Filemaker, I still get an entirely new record being created, even though I did not allow reverse record creation (upstream).  Am I correct in thinking that I'll have to create new Episode and Visit records with a script of some sort?

                 Thanks again


            • 3. Re: Multiple table interaction

                   If you need the ability to create multiple records to one main record, a portal on the layout is the easiest..

                   Otherwise, yes you will have to do it with scripts that create the related records.

              • 4. Re: Multiple table interaction

                     Ok, I was afraid of that.  As you can probably tell, I'm learning FMPro from the ground up, having used MS Infopath for this sort of thing before.  I LOVE FMPro, but scripting I haven't tackled yet.  It's a little daunting as I haven't programmed in many years, so I'm rusty...very rusty :)

                     I like the portal idea, although Portals seem to be limited in format to a table style.  Is there any way to rearrange it to be vertical instead of a horizontal table and then replicate down maintaining that format ?

                     Thanks for the help!  Much appreciated!


                • 5. Re: Multiple table interaction

                            Portals seem to be limited in format to a table style.  Is there any way to rearrange it to be vertical instead of a horizontal table

                       Double click the bottom section of the portal while in Layout Mode to open Portal Setup... Change the number of portal rows temporarily to 1. Click Ok and then click the portal and drag a resize handle down to increase the size of your portal row. You can now drag the fields to different positions within the enlarged portal row and format them as necessary. You can use the field tool in the status tool bar or the field picker (FMP 13 only) to add more fields to your portal row as well as any layout text, buttons, graphics you care to add. The result can look much like a "miniature form" instead of the default table like arrangement of fields. When done, return to layout setup and set the number of portal rows to a number that will fit the available space on your layout.

                  • 6. Re: Multiple table interaction

                         Oh cool!  I had no idea you could do that!  You guys rock!

                         This forum is much more helpful for learning than the FTS (although it's not bad).  It allows me to be much more specific in my learning.

                         Thanks again!