Formula for who has not completed task

[FredGB]

Hi
I am using CR9 with a Foxpro Database.
I am looking at two tables that share a value - "personid"
Customer and Customertrain
I would like a formula that selects from Customer table customerid that is not in Customertrain
Any help would be appreciated.

Regards
Fred

 

[lupins46]

I have no experience with Foxpro at all so I can only speak from a CR point of view.
This type of question would normally be answered using a query in the source database and CR would just be used to print the result.

You can create a sql query in CR9 (if this would work for Foxpro) which would be something like:

Select * from Customer left join Customertrain
on customer.personid = customertrain.personid
where customertrain.personid is null

If none of this is possible then you would add both tables to the report and link them on personid. Use the Link Options to set a join type of Left outer Join (assuming the Customer table is on the left) and create a detail line which includes fields from both tables.
You can then use conditional suppression on the detail section which suppresses the section if the customertrain.personid field is not null.

 

[FredGB]

Hi Lupins46
Unfortunately I have to run the report outside of the Foxpro environment and therefore copuld not use the query as a solution.
I will try your suggestion
many thanks
Fred

 

[lupins46]

Re-reading:
you want the records where:
customertrain.personid field is null
(I had Not null before)

 

[lbass]

To expand on lupins46 suggestion, after creating the left join from Customer to CustomerTrain, the record selection formula should be:

isnull({customertrain.personID})

-LB