Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Formula Field Limiting Records Post Reply Post New Topic
Author Message
Schugs
Newbie
Newbie


Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
Quote Schugs Replybullet Topic: Formula Field Limiting Records
     Posted: 08 Aug 2012 at 7:52am
Hello,
Please bare with me as i am very new to crystal reports.

I am making a report that prints mail labels to our clients. Some clients are in prison, which will need to print the prison address. While some clients are not. The issue is that any client that is not in a prison is not getting put onto the report at all. I am using a basic formula field to determine if the prison address or the personal address should be used ->

if 'Not in prison" then
(
{@ApplicantAddress}
)
else
(
{@PrisonAddress}
)

The issue is that all applicants have a field "IncarceratedLocation" which holds an ID that links to the table that has all the prison address in it. For clients that are not in prison, this field is null. and even though it is reading the 'ApplicantAdress" field, they never populate into the report.

InShort: How do i have a formula field not limit the report records?

I already have all formulas set to "Null values to default"


IP IP Logged
Schugs
Newbie
Newbie


Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
Quote Schugs Replybullet Posted: 08 Aug 2012 at 8:11am
This might explain it more clearly...

I’m making a mailing label report that will need to mail to prison address or applicant address.

Here are my formula fields:
{@address}:
if {Applicant1.ApplicationCompleted}="Out" then
(
{@ApplicantAddress}
)
else
(
{@PrisonAddress}
)

{@ApplicantAddress}:
if NOT({Applicant1.ApplicantAddress}="" or {Applicant1.ApplicantCity}="" or {Applicant1.ApplicantState}="" or {Applicant1.ApplicantZip}="") then
(
{Applicant1.ApplicantAddress}
& chr(10) &
{Applicant1.ApplicantCity} & ", " & {Applicant1.ApplicantState} & " " &
Replace(Replace(totext({Applicant1.ApplicantZip}),",",""),".00","")
)
Else
"Missing Info"


{@PrisonAddress}:
If NOT({InstitutionLookUp1.InstitutionAddress}="" and {InstitutionLookUp1.InstitutionCity}="" and {InstitutionLookUp1.InstitutionState}="" and {InstitutionLookUp1.InstitutionZip}=0) then
(
{InstitutionLookUp1.InstitutionDescription}
& chr(10) &
{InstitutionLookUp1.InstitutionAddress}
& chr(10) &
{InstitutionLookUp1.InstitutionCity} & ", " & {InstitutionLookUp1.InstitutionState} & " " &
Replace(Replace(totext({InstitutionLookUp1.InstitutionZip}),",",""),".00","")
)
ELSE
"Missing Info"


The problem arises when an outside client does not have and prison info. When they have no prison info they do not get put into the report at all even though it should(and does) be putting in the applicantaddress. It appears that because the prison field is null/empty the whole formula field can not complete. Is there a way in crystal to only validate the prisonaddress fields if the prisonfield on the applicant is not null?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Aug 2012 at 8:18am
did you outer join the tables?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Aug 2012 at 9:05am
you also have to set each formula to use 'default values for null'.
crystal will not evaluate a formula on a row where it hits a a null on a field that is referenced in the formula unless you explicitly tell it how to deal with the null (either by the overall formula setting or by a specific code).
if done via code the NULL portion must be written first.
 
example:
//this formula will erturn blanks for any record where table.number is null
if table.number>5 then 'Over 5' else if table.number<='5 and under' else if isnull(table.number) then 'Missing'
 
//this formula will return text for any record where table.number is null
if isnull(table.number) then 'Missing' else if table.number>5 then 'Over 5' else if table.number<='5 and under'


Edited by DBlank - 08 Aug 2012 at 9:17am
IP IP Logged
Schugs
Newbie
Newbie


Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
Quote Schugs Replybullet Posted: 08 Aug 2012 at 9:35am
Thanks for the reply.
1.) Im not sure what out join the tables means? Sorry very new at crystal reports(2nd day)
2.) i have tried this and i am still getting the same results. Is this not doing what u ment by handling the null?

if {Applicant1.ApplicationCompleted}="in" then
(
if isnull({Applicant1.IncarceratedLocation}) then
"Missing Info"
else
{@PrisonAddress}
)
else
{@ApplicantAddress}

putting a similar Null check in the {@prisonaddress} is also not working.

Edited by Schugs - 08 Aug 2012 at 9:36am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Aug 2012 at 9:44am

you have (at least) 2 tables in this report.

One holds demographics (table1) with addressess, the other holds only prison addresses (table2), correct?
go into
Database
Database Expert
select the Links Tab
 
table1 should be joined to table2 likely on a clientid field.
double click on the join (connecting line)
look at the join type
it needs to be outer (try left outer first but it depends on which direction you joined the tables)
 
if it is inner you only get records where there is a matching row in both tables.
if it is outer you will get all records from 1 table (table1/Demographics) and the matching records form the other (table2/prison addresses)
 
a warning Note that select statements can make outer joins act like inner joins.


Edited by DBlank - 08 Aug 2012 at 9:46am
IP IP Logged
Schugs
Newbie
Newbie


Joined: 08 Aug 2012
Online Status: Offline
Posts: 36
Quote Schugs Replybullet Posted: 08 Aug 2012 at 9:49am
THANK YOU!!!!
a mix between both worked. First it was set as inner joined. i fully understand now why it wasn't working that way. Second, i guess the people that made the database allow a lot more nulls then id like. turns out it wasn't just the one null field, but also fields inside that were nulling out the records. Thank so much for the quick and intelligent responses!
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.