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

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

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 - =

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?

can you write a stored proc or crystal command?