1 2 Previous Next 18 Replies Latest reply on May 23, 2014 11:24 AM by philmodjunk

    A simple drop down record search

    luciano991

      Title

      A simple drop down record search

      Post

           Hello,

           So now thanks to the assistance I have received in the forum I have a nice database set up for a doctor's office. It has 3 tabs; one for the patient info(patients), one for visits(visits) and one for prescriptions(prescriptions). The Visits and Prescriptions tabs are portals.

           What I would like is a dropdown on the Patients tab that would have the values of the patients Last Name, First Name and when a value was selected from the list that patient's record would appear. 

            

           Thanks,

            

           Mark

        • 1. Re: A simple drop down record search
          philmodjunk

               Define a text field with the global storage option. Put this field on your layout and format it with your value list of patient names. set up a script trigger (on objectModify for a pop up menu, OnobjectSave for a drop down list) to perform a script that finds all records matching the specified name. For examples of such a scripted find, see this thread:

          Scripted Find Examples

          • 2. Re: A simple drop down record search
            luciano991

                 In this script:

            Go To Layout [Select a layout based on table being searched]
            Enter Find Mode [] ---> clear the pause check box
            Set Field [YourTable::YourField ; Globals::gField]
            Set Error Capture [on] ----> Keeps error dialog from interrupting script when no records are found
            Perform Find []
            If [Not Get ( FoundCount ) // no records were found ]
                Show Custom Dialog ["No records were found by this search."]
            End IF

            I'm having trouble with Globals::gField

            In a previous post you pointed out that a Set Field command has two paramaters. I got the first one OK but this second one I don't quite understand.

            Thanks,

                  

            Mark

            • 3. Re: A simple drop down record search
              philmodjunk

                   The second parameter is simply a reference to your global field. You'd find that field in your list of fields in the top left part of Specify Calculation and double click it.

                   Since global fields can be accessed from any layout or calculation in your file no matter which table is used to define it, I routinely define a table just for all global fields not used as match fields in relationships as a way to keep better track of them, but this is an option. You don't have to do that if you don't want to. Just make sure to use field options to specify global storage or the example script won't work.

              • 4. Re: A simple drop down record search
                luciano991

                     OK, so I got as far as entering IF[] and then there is a Specify button and in that dialog I entered: Not Get ( FoundCount ) // no records were found ]

                    Show Custom Dialog ["No records were found by this search."]

                Is that correct? Because when I try to exit that dialog the message says: The Specified Table Cannot be Found.

                      

                Mark

                • 5. Re: A simple drop down record search
                  philmodjunk

                  Show Custom Dialog ["No records were found by this search."]

                  Is a separate script step. It is not part of what you specify for the IF step.

                  Not Get ( FoundCount ) // no records were found

                  is the only thing to enter into the specify calculation dialog for the If step. Then you click OK to close the dialog, Find the script step for Show Custom Dialog in the list on the left side of the script editor and double click it.

                  Why, after years of developer requests for it, we still do not have a basic text based script editor is beyond me...http://www.filemaker.com/company/contact/feature_request.html

                  • 6. Re: A simple drop down record search
                    luciano991

                         OK, now I get it. For a newbie like me it actually helps to have the correct code automatically entered but I agree it's a bit awkward. Sort of like saying you can be trusted to do it.

                         Now here's what I have done:

                         I created a Field in the patients table called Patient Search. It is a text field with no boxes checked on the Auto Enter Tab and the Global option checked on the storage tab. I have also set that field to a drop down list looking for PatientLastName in the patients table.

                         My script trigger is OnObjectSave(for a drop down list) and my script looks like this:

                             Go to Layout['PatientsR1"(patients)]
                             Enter Find Mode [Restore]
                             Set Field [patients::PatientLastName; patients::Patient Search]
                            Set Error Capture [On]
                           If [not Get ( Found Count ) // no records were found]]
                         Show Custom Dialog ['No Records Fund"; "No records were found by this search.\""]
                         End If

                         So when I bring up the layout in Browse View, I see the dropdown and it's populated with the Last Name of the record it is showing. I can drop the list down and see all the names but when I select a name, no records are found. What have I missed?

                         Thanks,

                          

                         Mark

                          

                          

                          

                             

                    • 7. Re: A simple drop down record search
                      philmodjunk

                           Your script should be:

                           Go to Layout['PatientsR1"(patients)]
                           Enter Find Mode []-->no criteria should be specified here
                           Set Field [patients::PatientLastName; patients::Patient Search]
                           Set Error Capture [On]
                      Perform Find [] ----> this step seems to be missing
                           If [not Get ( Found Count ) // no records were found]]
                                Show Custom Dialog ['No Records Fund"; "No records were found by this search."]
                           End If

                      • 8. Re: A simple drop down record search
                        luciano991

                             Phil,

                             Thanks for the reply. I'm sorry but I've been distracted by other projects. I corrected the script but I was still having issues. It turned out that the client is perfectly content to use Find Mode. But someday I'd still like to learn how to fetch up a record with a dropdown. I suggest we table this discussion for another time.

                             I am extremely grateful for all the help. You've been very patient as well. I ended up creating a file that allows the doctor to record/edit patient data, visit notes and prescriptions on a 3 tab tab layout. He's very pleased with it and is using Dragon Dictate to create his patient notes.

                             And I learned a great deal as well. I will be happy to share my work if you think that's appropriate.

                             His final question was one of security. If he keeps what are essentially his patient files on his MacBook Air, what kind of security can he apply to the computer and the file to protect his data in case his laptop is stolen?

                             Thanks,

                              

                             Mark

                        • 9. Re: A simple drop down record search
                          philmodjunk

                               Using FileMaker Advanced 13, you could encrypt the files stored on that laptop. You can look up "encrypt" in FileMaker help to learn more. Such encrypted files require an additional password to open.

                          • 10. Re: A simple drop down record search
                            luciano991

                                 OK, I've come back to this now and I need to start over. I also have a better idea now of what I want. Let's disregard the previous information in this thread unless you think it's relevant.

                                 I have a layout called Patients. It has 3 tabs: Patients, Visits, Prescriptions

                                 So on the Patients tab I have arranged the fields and their labels on the layout. Let's say I'm on the first record which is Bob Jones and all his information is populating all the fields. Now I want to put a drop down that when I click the drop down arrow all the First and Last Names are in the list, and when I click on a Mary Smith, all of her information now populates the fields on the patient tab. And if I have my relationships correct then I should see her visit information and notes on the Visits tab and her prescriptions on the Prescriptions tab. Please give me a swift kick in the brain and get me started.

                                 Teach me to fish.........and I'll never ask you about this again.......:-)

                                 Mark

                            • 11. Re: A simple drop down record search
                              philmodjunk

                                   And what should happen when you get two or more patients named Mary Smith? People with identical first and last names happen all the time. They can even live in the same house and thus have the same address when you get a Jr. and Senior sharing an address.

                                   First the basic "beginner level" setup:

                                   Define a global number field in your database. (it can be defined in any table in your file and it will still work.)

                                   Define a text field in patients that auto-enters LastNameField & ", " & FirstNameField

                                   Select the Unique Values validation field option for this field.

                                   In Manage | Value lists set up a value list with the "use values from a field" option.

                                   Select PatientID from Patients as the first field. Click "also display values from" and select the above text field that combines first and last names into the same field as "Field 2".

                                   Click the "only show values from second field" option.

                                   Now format your global number field to use this value list. You can make it a drop down list or pop up menu. I suggest playing around with the different options to see what works best for you.

                                   Now a scripted find can be performed via a script trigger (OnObjectSave for drop down lists, OnObjectModify for pop up menus) to do this:

                                   Enter Find Mode []
                                   Set Field [YourTable::PatientID ; YourTable::GlobalNumberFieldHere ] ---> this step fails if you don't specify global storage
                                   Set Error Capture [on]
                                   Perform Find []

                                   This works, but it has some limitations.

                                   1) it forces you to give each patient a unique name when you add new patient records. This may seem simple to do by adding a Jr. Sr., 1, 2, etc to the person's first name or an additional text field that you include in the above concatenation calculation, but when you see three Mary Smiths, it may be impossible to tell if the Mary Smith you want is Mary Smith Jr, Sr or III.

                                   2) as your set of patient records increases, you have to do more and more value list scrolling to find the needed patient.

                                   Thus, you may ultimately want to use a more sophisticated method that better handles duplicate names and allows you to type in part of a patient's name to get a much shorter list of just those patients that match what you have typed in.

                                   This demo file shows one such method:

                                   FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                                   Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

                                    

                              • 12. Re: A simple drop down record search
                                luciano991

                                     OK, not quite there but let's review what I have. I started with your first suggestion, skipped the script and looked at the two examples you mentioned/authored(bravo!) at the end of your post.

                                     Step 1: I created a field in the Patients table called Patient Search. Set it to Global and Number. Nothing checked on Auto  Enter tab, and on Validation tab the following are checked: Allow modification only during data entry and allow user to override during data entry.

                                     Step 2: Created a text field called PatientFullName in patients that auto-enters PatientLastName & ", " & PatientFirstName   Indexed, Calculation replaces existing value

                                     Step 3: Created a value list called Full Name. Use values from field: in first value _pkPatientID and in second value PatientFullName from patients table.

                                     Step 4: Dragged Patient Search Field into Layout. Formatted as follows: Display data from Patients::PatientSearch; Control Style: Drop-Down List; Values from Full Name.

                                     When I save and go into Browse mode what automatically fills the drop down is a number. Then when you drop down you see the list of patients, but when you select a patient you then see a different number in the default drop down value and nothing changes in the patient information fields.

                                     I want to thank you most sincerely for assisting me today. My brain is pretty fried but I learned a lot and I need to walk away from this now and clear my head.

                                     Thanks again,

                                      

                                     Mark

                                • 13. Re: A simple drop down record search
                                  philmodjunk

                                       Your drop down list is working exactly as you have designed it to work. It's just not doing exactly what you expected. Remember that while you are using the text from the text field to help select a patient, the value list is entering the patient ID number. That's why you see it in the field once you have selected a patient. This really shouldn't matter much as the next step is a script that uses that Patient ID to find the patient. Since that find will pull up the record for the selected patient, the fact that you have a number in the field isn't all that big a deal. You might even add a step in the find script that clears this field after finding the patient record.

                                       But there are ways to deal with the cosmetic issues involved:

                                       You could use a pop up menu instead of a drop down list. A pop up menu displays the matching value from the second field even after you exit the menu.

                                       You can also define a relationship matching Patients to a second table occurrence of Patients using the search field as the match field to the PatientID field in the second table occurrence. Then you can add the patient Name field from this new occurrence to your layout to show the selected name, it can even be placed on top of the drop down list field if you give it an opaque fill color and set up behavior options for it in the Inspector to deny browse mode access. (there's a check box to clear in the Inspector).

                                       But keep in mind that the demo file does NOT use this specific value list. What I described as the first option is the simplest to set up method that gets this working and confirms that you've set everything up correctly with needing any scripting.

                                       The demo file requires scripting to both manage the possible existence of duplicate names and also to keep the search field up to date if the related record's name field should be modified.

                                  • 14. Re: A simple drop down record search
                                    luciano991

                                         Good Morning,

                                         You wrote: Your drop down list is working exactly as you have designed it to work.

                                         Well, that is the most diplomatic way of putting it and you're perfectly correct.

                                         You wrote: Since that find will pull up the record for the selected patient, the fact that you have a number in the field isn't all that big a deal.

                                         No it wouldn't be but since there is a flaw in my design, only the patient ID number changes when I select a patient from the dropdown list. For some reason I'm not fetching the rest of the patient record. The information in the First Name, Last Name fields, etc. remain the same. I need to get that problem solved.

                                         Thanks,

                                          

                                         Mark

                                    1 2 Previous Next