AnsweredAssumed Answered

finding only records with related ones in a related table

Question asked by med on Jun 12, 2012
Latest reply on Jun 12, 2012 by med

Title

finding only records with related ones in a related table

Post

I am having a problem finding records in table 1 which have related records in tbale 2 (using fm12)

I actually wanted to find the number of customers of a certain type who had service in a date range.

Here is what I did

table 1 customer has customerid and customertype

table 2 service has customerid and servicedate

 

I created two glabal fields in customer datefilter1 and datefilter2

I related the tables as follows 

service::date <= customer::datefilter1

service::date >= customer::datefilter2

service::customerid = customer::customerid

then I wrote the following script

goto layout "customer"(customer)

show all records

set field customer::datefilter1 value:$date1

set field customer::datefilter2 value:$date2

set error capture on

perform find as follows      find  customer::customertype=$type

                                      omit  service::date =       (I also tried service::date ?)

Set variable $total value:get(foundcount)

THAT DID not work for me itr always gave the total students of that type regardless of having service or not

I ENDED UP writing the following script which worked for me 

With same relationships I wrote

goto layout "customer"(customer)

show all records

set field customer::datefilter1 value:$date1

set field customer::datefilter2 value:$date2
set variable $total value:0
go to record first
loop
if not isempty(service::date)
set variable $total value:$total+1
go to record next exit after last
endloop
 
NOW THIS is an inefficient way of doing things and although I have a solution I really want to know if
I am missing a basic knowledge of the meaning of relationships

Outcomes