Report based on related tables
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.
Thanks in advance for the help.