AnsweredAssumed Answered

Report based on related tables

Question asked by Annette on Jun 17, 2015
Latest reply on Jun 18, 2015 by Annette


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.