Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: WHERE clause not recognized in Formula Field. Post Reply Post New Topic
Page  of 2 Next >>
Author Message
Gnibmilc
Newbie
Newbie


Joined: 26 Sep 2011
Online Status: Offline
Posts: 8
Quote Gnibmilc Replybullet Topic: WHERE clause not recognized in Formula Field.
    Posted: 26 Sep 2011 at 7:08am
Please help. I am trying to use a WHERE clause in a formula field to narrow down my results and Crystal does not seem to recognize it. I am using Crystal 2008.

Here is an example but the "WHERE" does not turn blue. It gives me an error stating the WHERE clause does not seem to be part of the formula.

IF {result.entry} = ["H7", "H8"]
THEN {result.entry} WHERE {result.name} = "Ct"

Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Sep 2011 at 8:04am
not fiollowing what you are doing here...are you using this in the select expert?
this is more likely an and statement...?
{result.entry} in ["H7", "H8"] and {result.name} = "Ct"
IP IP Logged
Gnibmilc
Newbie
Newbie


Joined: 26 Sep 2011
Online Status: Offline
Posts: 8
Quote Gnibmilc Replybullet Posted: 26 Sep 2011 at 9:36am
This is in the Formula fields.

We have several entries for {result.name} and each one of those has an entry for {result.entry}. The output I need is the {result.entry} when the {result.name} = "Ct" but I only want this when the {result.entry} is "H7 or H8" when the corresponding {result.name} = "Well ID".

I hope this makes mores sense.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Sep 2011 at 9:47am
you are referencing only one table here, correct?
1. FOmrula fields do not have any impact on what rows are selected for inclusion in the report. I oftne see people try to write if-then formulas that look like they are trying to convert rows of data using a where clause. Crystal uses the select expert to deterrmine what rows need to be included (TRUE) or excluded (FALSE) from the report. (You can also do group conditionas but that is a little different and just mentioning it here as an FYI).
you want to include a row of data in your report if:
{result.name} = "Ct" and {result.entry} is "H7 or H8" and {result.name} = "Well ID"...
The first and last conditions are not making sense since they reference the same field?
Can you clarify that, please.
 
 
IP IP Logged
Gnibmilc
Newbie
Newbie


Joined: 26 Sep 2011
Online Status: Offline
Posts: 8
Quote Gnibmilc Replybullet Posted: 26 Sep 2011 at 10:03am
To give a very simple example. Imagine we have a kernel of corn and we want to record two results for the same sample (kernel), size and color. There is a field {result.name} that can be either "color" or "size". The corresponding {result.entry} fields would be "1.3" and "Yellow" respectively. I want to display the size of a kernel but only when the color is yellow. I hope this helps clarify.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Sep 2011 at 10:05am
so you have 2 rows of data for each 'kernal' and you need to do select statment evlauating both rows for a sepcific condition, correct?
IP IP Logged
Gnibmilc
Newbie
Newbie


Joined: 26 Sep 2011
Online Status: Offline
Posts: 8
Quote Gnibmilc Replybullet Posted: 26 Sep 2011 at 10:17am
correct
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Sep 2011 at 10:25am

Are you allowed to write a command for the report?

IP IP Logged
Gnibmilc
Newbie
Newbie


Joined: 26 Sep 2011
Online Status: Offline
Posts: 8
Quote Gnibmilc Replybullet Posted: 26 Sep 2011 at 10:27am
I suppose I can. I am not to sure how.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Sep 2011 at 10:32am
Here is a non-command way.
group on the Primary Key (the sample identifier)
create a flag to check if the grouping has the value you want to show.
//Flag example
if result.name = 'color' and result.entry='yellow then 1
Sum this flag at the grou level
SUM(@flag,result.pkfield)
if your SUM>0 then youwant to show this group.
go into the select epert
show the formula
change the fomrula to 'group selection'
add your criteria here
SUM(@flag,result.pkfield)>0
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
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



This page was generated in 0.016 seconds.