Solved

Flagging if a record doesn't exist

Posted on 2009-05-17
5
341 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

17 Experts available now in Live!

Get 1:1 Help Now