Flagging if a record doesn't exist

I have client records and attached to them I have 0 to many achievement records.

I want to flag any client records where there is no achievement record attached that meets my criteria or no achievement record at all.

Criteria
"      category ID = 1
"      date  > 01/05/09 and < 31/05/09


I thought I would simply group my report by client and put an if statement field in the group header. The If statement would then give me a True if it found an achievement record that met the criteria and False if NO record was found that met the criteria. False being the flag. I have since discovered that a formula will only consider the first record if it is placed in the group header(and the last record if in the footer). If I place my "if statement" field in the details section it does work but it then creates a line for every achievement record belonging to that client. This is not what I'm after.

What I really want is a list of clients with a simple true or false to flag if that client has an achievment record that meets the criteria or not.
if {vwAchievements.DateSubmitted} in DateTime (2009, 05, 01, 00, 00, 00) to DateTime (2010, 05, 1, 00, 00, 00) and {vwAchievements.PaymentTypeID} = 1
then "1"
else "0"

Open in new window

DanijelPAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
Use your formula field on each record.

In the group header use formula field:

if Sum ({@firstformula},{table.groupfield} ) >0 then
"TRUE"
else
"FALSE"
0
 
mlmccConnect With a Mentor Commented:
To use peter's idea you will have to change your formula to return numbers and not strings.

mlmcc
0
 
DanijelPAuthor Commented:
Excellent, thank you.

but..

How can I ensure Client records with no Achievement records attached are not eliminated

I have used a left outer join to Achievements.

Do I have to add something to my original "if"?
0
 
James0628Connect With a Mentor Commented:
Your formula might be OK and it might not.  I suspect "not", but I'm not always sure what CR will do when it encounters a null in a formula.  The safest thing is to explicitly check for them.

if not IsNull ({vwAchievements.DateSubmitted}) and
 {vwAchievements.DateSubmitted} in DateTime (2009, 05, 01, 00, 00, 00) to
 DateTime (2010, 05, 1, 00, 00, 00) and
 not IsNull ({vwAchievements.PaymentTypeID}) and {vwAchievements.PaymentTypeID} = 1
then 1
else 0



 James
0
 
mlmccConnect With a Mentor Commented:
Assuming your SQL is like

SELECT somefields
FROM Client LEFT OUTER JOIN Achievments
ON Client.Id = Achievements.ClientId

If that is the case you will select all client records and the matching records form achievements.  You will get all clients regardless if they hav eachievements or not.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.