5 Replies Latest reply on Jun 18, 2015 9:33 AM by Annette

    Report based on related tables



      Report based on related tables


      Hi all,

      I've hit an issue that I can't wrap my head around resolving.

      I have two related tables:

      Referrals::ReferralID = Services::fkReferralID

      For a referral, there are multiple service records which collect information such as the Service Type and Start Date.

      What I'm trying to do is run a list report which shows me all referrals that have service records meeting specific criteria.  So basically if the referral has a Service Type called MDT with a start date entered AND also has a Service type called SMO without a start date entered. 

      I had tried creating two calculation fields on the referral table using case statements, for example:

      Case(Services::Service="SMO" and IsEmpty (StartDate); 1 ; Services::Service="SMO" and not IsEmpty (StartDate) ; 2 ; 0)

      The other one the same except referencing the service type MDT.  My logic was then to find where each of these fields had the appropriate number.  But, the problem I was having with this was that it only considers the first service record created for the referral, not all.

      Am I missing something really obvious for considering all records related to a table?  Apologies if this is a really stupid question. frown

      Thanks in advance for the help.

        • 1. Re: Report based on related tables

          I don't understand the use of the case statement.  I think you want a scripted find.  I always try it manually first.  Just to make sure you are looking for 2 types of records.  Those that have MDT and a start date, OR those that have SMO without a start date.  If so:

           Enter find mode, type in MDT in Service Type, type a "*" in the Start Date (no quotes),  This is the search criteria for 'MDT" & something in start date.  Don't click Perform Find button.

          Click the New Request button. Still in find mode this sets up your "OR' request,  Type in SMO into Service Type and type "=" (no quotes) into Start Date.  This is the search criteria for 'SMO' & empty start date.

          Click perform Find.  This gives you the records in the first criteria and the (technically OR for searching purposes) second criteria.

          Then make sure your layout is in list view.  If this gives you the results you are looking for, then you can script it

          • 2. Re: Report based on related tables

            Unless I'm missing something this doesn't do what I need.  This gives me any referral that has a service that matches either of those criteria (SMO no start date OR MDT with start date).  I'm also getting referrals that don't have one or the other of the two.

            What I need is to meet both criteria and find only referrals that have services for MDT with start Date and SMO with no start date. 

            Again, maybe I'm misunderstanding and doing something wrong in my find.

            • 3. Re: Report based on related tables

              I just did a quick five minute flow chart thinking it might explain better than I am doing.  Basically if I was running a find I only want Referral 2 to be the result as its the only one of the three that matches both criterias.

              • 4. Re: Report based on related tables

                The calculation won't work for you for the simple reason that when you have a table of many related records and set up a calculation that refers to fields of a related record in that table, the calculation only references the value of the first related record.

                Enter find mode and specify the first criteria for a service.

                Return to find mode, specify the criteria for the second service example

                Now, DO NOT, click Find to perform the find. Pull down the Requests menu (Records menu is replaced with the Requests menu when you are in find mode) and select "constrain found set'.

                The first find will pull up a found set of all referrals with the first service example. The "constrain" then omits from that found set all records that do not have the second service example.

                The result will be all referrals where there is both at least one related record matching the first criteria and at least one related record matching the second criteria.

                This type of manual find can be performed in a script.

                • 5. Re: Report based on related tables

                  Thanks for the response.  You're a star!!  Working like a charm now!  Thank you SO much!