1 2 Previous Next 16 Replies Latest reply on Apr 23, 2014 11:25 AM by luciano991

    To Portal or not to Portal



      To Portal or not to Portal


           Hello all,

           I am a new user of FileMaker Pro 13. I have completed a 10+ hour course in FMP12 on Lynda.com which I highly recommend for new users. 

           I have searched the forum and Google for articles and posts of interest on, dare I say it, creating a file for a Medical Practice. I have found some good information there. And one thing that people seem to say a lot is that a specific question is easier to respond to than a general "how to from start to finish" question. So here goes. I have a question.

           First of all, my client's needs are simple so a commercial solution would be overkill.

           So first I created a table for Patients. It's got the usual stuff in it. 

           Then I created a table for Visits which simply has date of visit, length of visit and a large box field for notes.

           I figured one patient can have many visits. So I created a one to many relationship between patients and visits.

           Now I figure creating a new patient record is easy. Go to the Patients Layout (based on the Patients table) and simply create a new record. But visits are another story.

           So to create a new visit, I figure I would have to create a layout based on the Visits Table, put in the Visits table fields  and include the fields from the Patients table. So would I just drag those patient fields into the layout or would I create a portal to the Patients table to create a new visit?

           And if I may be permitted one follow up question, how could I add a new patient from this Visits based layout?

           There. Two simple questions. How did I do?

           All the best,



        • 1. Re: To Portal or not to Portal

               But the answers aren't simple wink

               You could put a portal to visits on the patients layout and use that to create new visits records and record the needed info about each such visit.

               But you can also use a layout based on visits to create new visits on log the info about them. This is a more complex thing to set up, but gives you a possibly more flexible format for recording your visits data. The complication is that you have to add your own "mechanism" for linking each new visits record to the correct Patient record where a portal can manage these details for you.

               A drop down list of patientID's and names could be used on such a visits layout to select a patient for the new visits record. And if you start from the Patients record and click a button to go to the visits layout and log a new visit, the script can take the patient ID of the current patient record and create that new record for you on the visits layout and put that patient ID into the correct field for you to link it to the current patient.


                    how could I add a new patient from this Visits based layout?

               You probably wouldn't. More likely, you'd click a button to go to the patients layout, create and complete the record for the new patient and then return to visits to log that new visit.

               But it is possible to do that if you really want to. You can enable "allow creation of records via this relationship" for Patients and then entering data into any blank field from Patients placed on your visits layout will, if there is no related patient record already linked to the visit record, create a new record in patients and link it to the current visits record.


                    So would I just drag those patient fields into the layout

               If you have a valid relationship linking patients to visits then yes, you can add fields from patients directly to your visits layout.

               Please also note that the option to use a portal and the option to use a visits layout are not mutually exclusive. You can set up both.

          • 2. Re: To Portal or not to Portal

                 Well, PhilModJunk, right you are.

                 But you were a big help. So let me tell you what I've accomplished and see if it makes sense.

                 I have created a layout based on the Patients table (first and easiest choice but not the most flexible as you pointed out). I had already populated the table with sample data. I could then page through the data in Form mode. Worked great.

                 Then I created a portal into the Visits table. I had previously successfully added a visit or two to one or two of my "patients" and these displayed magnificently; the data of the visits table properly connected to the appropriate record(s) in the visits table. Hallelujah. 

                 Now I was then able to create two tabs: Patients and Visits and deposit the Visits portal on the Visits tab. It's still not very elegant looking but it works. 

                 Now, it appears that if no visit is recorded for a particular patient then there is a blank form there that can be filled in and this adds that visit to the patient record. If there are visits recorded you can scroll down until you find a blank form and fill that in to automatically add a new visit for that patient. I have to scroll down because I have arranged the portal data in descending order, that is if I have this correctly, most recent visit on top. Maybe if I flipped that order the blank form would be on top. 

                 So let me know if this makes any sense at all. I still have another tab to add for Prescriptions. I reason that one patient can have many prescriptions. I have a proper prescriptions table and I believe the one to many relationship I have created between Patients and Prescriptions is proper as well.

                 Thanks for your observations. I look forward to hearing any additional thoughts you might have.

                 All the best,



            • 3. Re: To Portal or not to Portal

                   What you have will work,

                   If you want to add new visit records in a blank first row of the portal, do the following:

                   It sounds like you have already specified a sort order that lists the most recent visit record first, if this is not the case, add this sort order in portal set up.

                   In layout mode, select the visits tab and enter "VisitTab" into the name box in the Inspector to give this tab panel an object name.

                   Add a button that performs a script to add the new portal record:

                   Freeze Window
                   Set Variable [ $PatientID ; value: Patients::PatientID ]
                   Go to Layout ["Visits" (Visits) ]
                   New Record/Request
                   Set Field [Visits::PatientID ; $PatientID ]
                   Go to Layout [original layout]
                   Go to Object ["VisitTab" ]

                   Go to Object is being used here to reselect the Visits tab so that you appear to stay on the visits tab when you click a button to add a new visits record to your portal.

              • 4. Re: To Portal or not to Portal

                     Hi Phil,

                     So when I try to create the script you gave me I get an error message that says 'the specified table cannot be found.'

                     Double checking my tables I have 3 tables called 'patients' 'visits' 'prescriptions'

                     Is case sensitivity an issue?

                     The three layouts I am using are Patients R1, Visits and Prescriptions.

                     What I'm seeing in Browse mode is that on the Visits tab there is a space for entering a visit. And that space or blank form is below the last visit I have entered. Ideally the doctor should be able to see the visits by using the scroll bar if there is more than one visit. And he should be able to add a visit at the top of that list using a button or a form. Same with prescriptions.

                     On the good news side, I think my tables and relationships are OK, and my portals are working because I can see the data in the list view.

                     Thanks for putting up with my early explorations here. It's a great help and much appreciated.

                     All the best,



                     PS Would it help if I uploaded my file? 

                • 5. Re: To Portal or not to Portal

                       What step are you trying to create when you get that message? Set Field? Set Variable?

                       What exactly did you have entered into the dialog just before you clicked OK and got that message? (You could copy and paste that text here so that others can see it.)

                  • 6. Re: To Portal or not to Portal

                         Hi, I saw this post and I have a related question. I am creating a similar medical database. I have a Patients Table and a Visits Table. The Patient ID field is in both tables with an "equal to" function. It is not auto-entry, as I want to enter particular numbers for PatientID. I will enter many visits on separate dates for each patient.

                         In the relationship table, I have the Patient ID from each table related by an "equal to" function. I have allowed creation of records via this relation and "deletion of records" as well.

                         The Visits table with several fields is my main layout and holds the 3-4 fields from the Patient Table too. Records are created by Visit, not by Patient. So every time I type in a patient ID (lets say 121), if a visit record has been previously entered for Patient 121 in the database, then a few fields from the Patient Table like Date of Birth, Sex, Ethnicity fill up automatically. Then, I continue filling data unique to that visit like the date of visit, chief complaint, Name of attending, medication etc. I apologize for my questions as they may sound very basic.

                         1) Is this setup okay-any obvious problems? Eventually, I need to be able to extract all the records into Excel and run statistics.

                         2) Do I need to set up a one-to-many relationship? Between which fields and how do I do this?

                         3) Is it advisable to create a unique Visit ID by auto-entry? Please suggest how the relationship should work.

                    • 7. Re: To Portal or not to Portal

                                as I want to enter particular numbers for PatientID.

                           I do not recommend that you do that for PatientID. It opens the door to specific problems with your database if you enter a value incorrectly and do not immediately discover and correct the error. Use an auto-entered serial number of Get (UUID) for PatientID. If you also have an ID number that you want/need to enter manually, define a field in your patient table for that purpose so that you can use it for searching, etc, but don't use it as your primary key in relationships.


                                I have allowed creation of records via this relation and "deletion of records" as well.

                           Be careful of where you select "delete" options in relationships. Used incorrectly, you can create a situation where deleting a single record can delete large numbers of records in other tables that you did not want to delete.

                           2) What you describe IS a one to many relationship. One record in Patients will link to many records in Visits.

                           3) You do not have an immediate need for such a field, but such a field--called a primary key is often needed. It is simplest for a new user to add such a field in every table at the time that they create it, but it's not terribly difficult to add in such a field after the fact--you can use Replace Field Contents to assign ID's to all existing records pretty easily.

                      • 8. Re: To Portal or not to Portal


                             You asked me to make a button in layout mode that ran a script that would create a new portal record. 

                             So I drew the button on the layout. For the button action I selected Perform Script. On that dialog I clicked the specify button and created a new script according to your instructions. I went back just now and tried it again and didn't get the error this time but I don't think the button is working. 

                             I'm wondering if like NSA A I should make the Visits the focal point of the layout and do what he does by creating a portal to the patients table. But then I'd have the same issue with prescriptions. 

                             I would like the doctor to pull up a 3 tabbed layout. On the first tab is the Patient Data(contact information and the like) The second tab is Visits and it's a portal to the visits table and the third tab is Prescriptions and on that tab is a portal to the prescriptions table. Now the doctor should be able to pull up a patient record, add visit notes for today's visit with the patient and add/delete/modify a prescription record all in the same layout. Maybe that's a clearer way to express it.






                        • 9. Re: To Portal or not to Portal

                               It sounds like you clicked the specify button for defining a calculation for a script parameter instead of clicking the + button to start a new script.

                          • 10. Re: To Portal or not to Portal


                                 Again forgive my newbieness. 

                                 When you click the plus sign you get a dialog and you add the script elements from the list on the left. So what I got was.

                            Freeze Window
                            Set Variable [ $PatientID ; value: patients::_pkPatientID ]
                            Go to Layout ["Visits" (visits) ]
                            New Record/Request
                            Set Field [Visits::PatientID ]
                            Go to Layout [original layout]
                            Go to Object ["VisitTab" ]


                            This is pretty close to the script but not exactly. I can't tell if the button is doing anything. As I said I can go to the Visits tab and the visits are arranged from most recent to least recent. And there' always one blank at the bottom of the list where I can create a new visit for that client. But the client has to scroll all the way down through all the visits to get to that blank form. Wish it could appear at the top. 

                            What should be the behavior of the button after I click it? A popup for a new visit record entry? I'm not sure what I'm looking for.

                            Thanks again.



                            • 11. Re: To Portal or not to Portal

                              Set Field [Visits::PatientID ]

                              Is not set up correctly. It's missing the calculated result parameter. It should look exactly like this in the script editor:

                              Set Field [Visits::PatientID ; $PatientID ]

                                   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.

                              • 12. Re: To Portal or not to Portal

                                     Yup that works. Is there a way to make the new blank form appear at the top of the list of previous visits?

                                     And I am going to attempt to create a similar button for the prescriptions tab so let's see if I've learned anything. I'm going to just substitute prescriptions for visits in that script. 

                                     Thanks for the help and the patience. I think I'm just about there.

                                     All the best,



                                • 13. Re: To Portal or not to Portal

                                       If your portal's table auto-enters a serial number into a field of type number, you can open Portal Setup... and specify a sort order for the portal that sorts on the serial number field in descending order. Then the newest record will be listed first in the portal.

                                  • 14. Re: To Portal or not to Portal

                                         PhilModJunk = FileMaker Pro Ninja, 

                                         And not only did you give me the solution but you taught me something about how to think. Thanks.

                                         And the New Visit button is working great. 

                                         One last question on this issue. When I record a visit and click away from the notes field to commit that data to that visit record a new blank form is created just underneath the one I just filled in. Can you tell me why this happens and if I can prevent it from happening. By clicking the New Visit button I get just what I want; a new form right above the last entry. 




                                    1 2 Previous Next