Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: If Statement with 2 Tables Post Reply Post New Topic
Author Message
JediSkipdogg
Newbie
Newbie


Joined: 14 Apr 2015
Online Status: Offline
Posts: 9
Quote JediSkipdogg Replybullet Topic: If Statement with 2 Tables
    Posted: 23 Feb 2018 at 8:17am
I have two tables linked together. The second table has a column at the end that has a version number for the record based on changes in the state crash reporting system. The formula is doing what I want it to do EXCEPT it is returning two results for a single record. It is returning both an actual Contour (the correct one) AND the N/A.

So {P_ACCID_OH_TAB.ROAD_CONTOUR_CD} returns a blank (assuming null) value or number 1-9. That then links to P_ROAD_CONTOUR_CD_VIEW which looks like this...

column_value     column_value_desc     column_abbr     rollup_column_value     rollup2_column_value     jxdm_rollup_value     status_cd     catg_desc     tree_order     version
1     Straight Level          1          1     L               1
1     Straight Level          1          1     L               2
2     Straight Grade          2          2     L               1
2     Straight Grade          2          2     L               2
3     Curve Level          3          3     L               1
3     Curve Level          3          3     L               2
4     Curve Grade          4          4     L               1
4     Curve Grade          4          4     L               2
9     Unknown          9          9     L               2

I created the following formula so it returns data no matter what. It is that last column where there is a 1 or 2 that is kicking my butt. I only need it to return the description from above if there is something in P_ACCID_OH_TAB.ROAD_CONTOUR_CD and when it returns, it needs to return what version 2 is and not the same record twice if that makes sense.

WhilePrintingRecords;
Local StringVar Contour;

if not isnull({P_ACCID_OH_TAB.ROAD_CONTOUR_CD}) and {P_ROAD_CONTOUR_CD_VIEW.version}=2 then Contour:={P_ROAD_CONTOUR_CD_VIEW.column_value_desc} else Contour:="N/A";

Contour
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Feb 2018 at 7:06am
guessing you are doing some sort of grouping or suppressing here to try and get 'two rows' into 'one row'
Don't confuse how you represent the data in the report with what the data actual is. your formula is going to evaluate on every row of data and return a result for each.
IP IP Logged
JediSkipdogg
Newbie
Newbie


Joined: 14 Apr 2015
Online Status: Offline
Posts: 9
Quote JediSkipdogg Replybullet Posted: 27 Feb 2018 at 2:36am
Let me explain the two tables. Table A contains a field that sometimes is and sometimes isn't filled out. So it may contain a null value. If it returns that null value, I want it to display N/A on my report.

IF there is a value in that field it then goes to another table to give me the full description of that value. I need it to return that, but as noted above, most values have a Version 1 and Version 2. I am only concerned with the descriptors of Version 2.

If I use the Select Expert, unfortunately it totally ignores anything with a null value. If I write my formula as...

WhilePrintingRecords;
Local StringVar Severity;

if isnull({P_ACCID_OH_TAB.CRASH_SEVERITY_CD})
    then Severity:="N/A"
    else Severity:={P_CRASH_SEVERITY_CD_VIEW.column_value_desc};

Severity


Then it works properly on the null parts but gives me two responses back for each record as it's displaying both Version 1 and 2 info. I haven't figured out how to get only Version 2 info to come back.

Edited by JediSkipdogg - 27 Feb 2018 at 3:33am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2018 at 3:10am
are you joining the tables?
IP IP Logged
JediSkipdogg
Newbie
Newbie


Joined: 14 Apr 2015
Online Status: Offline
Posts: 9
Quote JediSkipdogg Replybullet Posted: 27 Feb 2018 at 3:32am
Originally posted by DBlank

are you joining the tables?


Yes, but as joins are not my forte of understanding I'm sure there is an issue here somewhere. So I have..

Join Type - Inner Join
Enforce Join - Not Enforced
Link Type - =
IP IP Logged
JediSkipdogg
Newbie
Newbie


Joined: 14 Apr 2015
Online Status: Offline
Posts: 9
Quote JediSkipdogg Replybullet Posted: 27 Feb 2018 at 3:35am
And like I mentioned above, my Formula Field works and shows my N/A if the field in Table A is blank and shows me the corresponding descriptor in Table B if it's not, but it shows two records instead of one.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2018 at 4:01am
the formula is probably not the issue, it is likely the join that is causing the two records.
Does either table have more than one row per item you are trying to show? If so that is where the 'two rows' are coming from. The formula might make the two rows appear if it is acting as the join enforcement but it is not really the issue. If both tables only have one row per item you are trying to show perhaps you are joining on the wrong field?
IP IP Logged
JediSkipdogg
Newbie
Newbie


Joined: 14 Apr 2015
Online Status: Offline
Posts: 9
Quote JediSkipdogg Replybullet Posted: 27 Feb 2018 at 4:29am
Originally posted by DBlank

the formula is probably not the issue, it is likely the join that is causing the two records.
Does either table have more than one row per item you are trying to show? If so that is where the 'two rows' are coming from. The formula might make the two rows appear if it is acting as the join enforcement but it is not really the issue. If both tables only have one row per item you are trying to show perhaps you are joining on the wrong field?


The second table does. It looks like this...

column_value     column_value_desc     column_abbr     rollup_column_value     rollup2_column_value     jxdm_rollup_value     status_cd     catg_desc     tree_order     version
1     Straight Level          1          1     L               1
1     Straight Level          1          1     L               2
2     Straight Grade          2          2     L               1
2     Straight Grade          2          2     L               2
3     Curve Level          3          3     L               1
3     Curve Level          3          3     L               2
4     Curve Grade          4          4     L               1
4     Curve Grade          4          4     L               2
9     Unknown          9          9     L               2


I am trying to join on that first number but I want to "filter" the join by the last number in the column. That is where I am stuck on is how to filter anything but a 2 in the last column out. And that last column of 1 and 2 is actually the "Version" column in the headings. I couldn't figure out any better way to format it.

Edited by JediSkipdogg - 27 Feb 2018 at 4:30am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2018 at 6:23am
can you write a stored proc or crystal command?
IP IP Logged
JediSkipdogg
Newbie
Newbie


Joined: 14 Apr 2015
Online Status: Offline
Posts: 9
Quote JediSkipdogg Replybullet Posted: 07 Mar 2018 at 3:46am
If I knew how, sure. LOL
IP IP Logged
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.031 seconds.