4 Replies Latest reply on Jun 29, 2011 9:06 AM by JustinLevens

    Showing only matched records from relationship



      Showing only matched records from relationship


      I have a many-to-many relationship.  One table is holding around 5 records per subject and there is another table holding appointments.  The appointment tables is coming from an enterprise appointment scheduling product.  What I'm doing is matching up when an appointment/visit might match up to an assesment based on a date window.  I have the relationship working perfectly however because there are many assessments per subject there are a bunch of empty records where I'm pulling in the appointment information.  I would only like to see the records where there is a match between two tables via the relationship and omit the other records.  Any suggestions or settings to get this to display right?  What I'm going to end up doing is quering this table for assessment date and creating a report.

        • 1. Re: Showing only matched records from relationship

          On what table is your layout based?

          Is this a list view layout? (describing your layout would be helpful here.)

          A simple find that omits records from your layout when there is no related record should produce the list of records you need if I'm interpreting your post correctly.

          • 2. Re: Showing only matched records from relationship

            The layout will be based on the assessment table and it will be a table view that the user will be able to query by entering parameters on a front end form then a report will be generated based on their query.  The end report will be based on the query of the assessment table by date range and doctor.  I only want to be able to show the assessments that have an appointment that coincide with them on the report.  I tried performing a search on date (which will be one of the parameters) and it worked fine however there are about 5 records that don't conincide with an appointment, so I wouldn't want to show those on the report.  Does that help?

            • 3. Re: Showing only matched records from relationship

              You have two tables, Assessments and Appointments linked in a one to one relationship?

              Your report layout is based on Assessments or Appointments?

              Either way this method works, but the details need to be "flipped" depending on which of the two tables the layout is based.

              Assuming Assessments is the specified table for the layout, which makes Appointments the related table.

              The following script will omit all Assessment records from the current found set that do not have a related appointments record. It should be performed only from a layout based on Assessments, but can be performed immediately after the user performs their find:

              Enter Find Mode [] // clear pause check box
              Set Field [Appointments::PatientID ; "*"] //any field in appointments that is never empty can be used here.
              Set Error Capture [on]
              Constrain Found Set[]

              If your layout is based on appointments, change the above set field step to put an asterisk in any nevery empty field in Assessments instead.

              If you are using a script to find the records in the first place, you can simply include the asterisk operator as part of the criteria used in your find and this is also how you would do this in a non-scripted find.

              • 4. Re: Showing only matched records from relationship

                I see the key here...setting the asterisk in the related table to may sure there's something there which will limit the set.  Makes sense and worked perfecting.  Thanks a lot PhilModJunk.