Print Page | Close Window

If Statement with 2 Tables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22554
Printed Date: 26 Apr 2024 at 2:22am


Topic: If Statement with 2 Tables
Posted By: JediSkipdogg
Subject: If Statement with 2 Tables
Date 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


Replies:
Posted By: DBlank
Date 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.

Posted By: JediSkipdogg
Date 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.

Posted By: DBlank
Date Posted: 27 Feb 2018 at 3:10am
are you joining the tables?

Posted By: JediSkipdogg
Date 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 - =

Posted By: JediSkipdogg
Date 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.

Posted By: DBlank
Date 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?

Posted By: JediSkipdogg
Date 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.

Posted By: DBlank
Date Posted: 27 Feb 2018 at 6:23am
can you write a stored proc or crystal command?

Posted By: JediSkipdogg
Date Posted: 07 Mar 2018 at 3:46am
If I knew how, sure. LOL


Print Page | Close Window