Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: selecting records Post Reply Post New Topic
Page  of 2 Next >>
Author Message
lorimack
Groupie
Groupie


Joined: 03 Nov 2009
Online Status: Offline
Posts: 48
Quote lorimack Replybullet Topic: selecting records
     Posted: 21 Nov 2012 at 3:13am

I have a report that needs to have the victims name, race sex and DOB.  The only problem is the victim can also be "Society" which has none of those. 

 
when nametype = "Victim" then "Individual" and "Society" can both be selected. however, when I use the following code:
{nmmain.lastname} &","& {nmmain.firstname}&" "&  {nmmain.middlename}&"("&{nmmain.race}&","&{nmmain.sex}&","&{nmmain.age}&")"  to show the vicitms name, race sex and DOB, the records with the victim of Society dissapear from my report.
 
Is there a way I can do something like the following:
 
if nametype = "Victim" then {nmmain.lastname} &","& {nmmain.firstname}&" "&  {nmmain.middlename}&"("&{nmmain.race}&","&{nmmain.sex}&","&{nmmain.age}&")"  else
if nametype = "Society" then "Society"
 
Thank you so much for your help!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Nov 2012 at 4:00am
records disappearing is often symptomatic of a join problem.
Do you have more than one table in the report?
Did you outer join it?
 
Also if you do not handle NULLs inside youtr formula it can cause the fomrual to stop evaluating and return nothing for a row where it hit a null.
IP IP Logged
lorimack
Groupie
Groupie


Joined: 03 Nov 2009
Online Status: Offline
Posts: 48
Quote lorimack Replybullet Posted: 21 Nov 2012 at 4:16am
I think the NULL issue is the problem, I have never built a report that will show a value if its NULL
 
for instance if i have the following
 
case ID   nametype   nameinfo
 
1             individual   bob smith, 25, w, m
2             individual   jane doe 35, b, f
3             society       NULL VALUE
 
They way I have it set up now, caseID #3 does not show because i have a formula to show the name, race, sex, and DOB.
 
Is that why society doesn't show?  How can I get it to show since the formula for the nameinfo is going to be blank for society?
 
 
Thanks!
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Nov 2012 at 4:18am

using the sample data how do you want that to output?

IP IP Logged
lorimack
Groupie
Groupie


Joined: 03 Nov 2009
Online Status: Offline
Posts: 48
Quote lorimack Replybullet Posted: 21 Nov 2012 at 4:27am
I would like it to show as
1    individual   bob smith, w m 25
2    individual   jane doe, b f 35
3    society      
 
the way i have it set up now it shows
 
1 individual bob smith, w m 25
2 individual jane doe, b f 35
 
the society record doesn't show at all, i guess because i used a formula to create the name race sex and age category.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Nov 2012 at 4:29am
is the name data stored int eh same table as the case ID and type data or did youhave to join to another tabloe to get the names?
IP IP Logged
lorimack
Groupie
Groupie


Joined: 03 Nov 2009
Online Status: Offline
Posts: 48
Quote lorimack Replybullet Posted: 21 Nov 2012 at 4:32am
case id, nametype (indivudual, society) and name formula all three came from differenty tables. 
 
caseid   CASE table
nametype  CASENAME table
name    NAME table. 
 
i didn't change any of the joins yet. 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Nov 2012 at 4:36am
Let me take a stab at a process here.
in the report preview mode...
If you take the formula field off the report you get all the records types (including society).
if you add the formula back onto the report it makes the Society field disappear.
Correct.
IP IP Logged
lorimack
Groupie
Groupie


Joined: 03 Nov 2009
Online Status: Offline
Posts: 48
Quote lorimack Replybullet Posted: 21 Nov 2012 at 4:38am
that is correct
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Nov 2012 at 4:52am
it is your join.
unless you explicitly set the join enforcement in each table join it only gets "enforced" when you use fields from both tables in the join (being used can be mean in a formula field too).
This is why your data set changes when you add or remove a field from the report.
If you fix your join it will keep your data static and keep all your records available.
IP IP Logged
Page  of 2 Next >>
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