10 Replies Latest reply on Apr 25, 2012 11:21 AM by philmodjunk

    Searching Related Records



      Searching Related Records


      Hi there,

      I've develpoed a summary page in my database that allows the user to search for a patient via their NHS number, and a specific disease via their Treatment ID.

      The page is pretty much working as intended, however sometimes the page is not populating corectly.

      Example (shown in pics): A patient with NHS number of 098 has to disease episodes, 71 and 72. Selecting 71 will populate the fields correctly, however when I select 72 as the treatment and perform the search, nothing changes.

      Any suggestions as to why this could be happening?


        • 1. Re: Searching Related Records
          /files/4f22a8a15f/Screen_shot_2012-04-22_at_14.39.54.png 1280x800
          • 2. Re: Searching Related Records
            /files/35b0088d72/Screen_shot_2012-04-22_at_14.40.02.png 1280x800
            • 3. Re: Searching Related Records

              You've indicated that you are searching related records.

              I would guess that your Summary layout is based on the patients table and that your fields associated with Care Spell ID are from a related table--either placed directly on the layout or in a portal. Either way, fileMaker is doing what it is designed to do--not what you intended it to do.

              I'll need to see the tables and relationships and how that data is presented on the layout to suggest a way to get what you want in detail. (I believe I helped you with your design, but that doesn't mean I can remember the details...)

              In general terms, you can either use a layout based on the related table, (the episodes table?) and then performing a find will pull up the records you want or you can use a find to find the patient and then manipulate a portal filter or match field used in a relationship to pull up the data for Episode 71.

              • 4. Re: Searching Related Records

                Correct, the summary table is based on the patient table. The fields are placed directly onto the layout (no portal) and they are from the following tables:

                • DAHNO Care Spell ID
                • Histology SNOMED Diagnosis
                • MDT Summary 

                They are all related through the care spell ID.

                I have test the search by creating a new patient and giving him two diesease episosdes. when I input the NHS number, the care spell drop down box correctly populates with the treatments, and you can select either. However whichever you select, it will always revert back to the same treatment.

                • 5. Re: Searching Related Records
                  /files/0bf25b2f9b/Screen_shot_2012-04-25_at_17.56.35.png 1280x800
                  • 6. Re: Searching Related Records

                    What you want to do won't work with your layout.

                    When you specify search criteria in a related table such as DAHNO Care Spell I..., you are telling FileMaker to find all records in the Patients table that have at least one record in the DAHNO Care Spell I... table that match the specified criteria. Once it has found those records, the layout's design and underlying relationships kick in and data from your fields to related tables display data from the first related record--which may not be the related record that matched your criteria.

                    If you had a portal to DAHNO Care Spell I... on your layout, you'd see that a record matching your search criteria exists, but isn't always the first one listed in the portal.

                    I can think of two ways to deal with this issue. The best option depends on what a user most often needs to do with these searches:

                    1) Use a portal filter or filtered relationship to control which record from DAHNO Care Spell I... is displayed on the layout. The data you specify as earch criteria for this table must also be used in the portal filter or a match field in a relationship. This can be done by using a global field to specify the Care Spell ID value so that it can be used in a script to find patients and in the portal filter or relationship to control which related record appears on the layout.

                    2) Use a layout based on DAHNO Care Spell I... with Fields from Patients included on your layout. When you perform a find on this layout, you are finding DAHNO Care Spell I... records instead of patients records. Now the Patients table is the related table, but since you have a many to one relationship from DAHNO Care Spell I... to patients, there will be no issues with the incorrect patients data appearing on your layout.

                    • 7. Re: Searching Related Records



                      I've switched the layout so that it is now based on the DAHNO Care Spell table and it is fit for purpose. The drop down box Care Spell ID field is no longer functional however, Will I be able to get this working on the new layout?

                      • 8. Re: Searching Related Records

                        Don't see why not, but then "is no longer functional" is pretty vague here. Can you see any values listed? Is it a conditional value list?

                        Did you make this change just by selecting the other table occurrence in layout setup | Show records from?

                        What do you see when you click this field and check the "display data from" box in the inspector?

                        • 9. Re: Searching Related Records

                          I did it by creating a new layout using the DAHNO table and copy/pasting the format of the old summary page.

                          I've managed to fix the problem by running a script that copy's the Care Spell ID from the previous search page and pastes it into the Care Spell box and then performing the search.

                          • 10. Re: Searching Related Records

                            Copy/pastes messes up any data previously copied by the user to the clipboard.

                            Use Set variable in place of copy and set field in place of paste and your clipboard then remains untouched by your script.