Solved

Flagging if a record doesn't exist

Posted on 2009-05-17
5
339 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:DanijelP
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 24409560
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 24411978
To use peter's idea you will have to change your formula to return numbers and not strings.

mlmcc
0
 

Author Comment

by:DanijelP
ID: 24417379
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
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 100 total points
ID: 24417759
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 150 total points
ID: 24418257
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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now