Use data from two tables

[djieon]

Hi, I have two tables. {RM_ITEM_INVENTORY_NDT_LATEST_TEST} and {RM_ITEM}

{RM_ITEM} contains a unique ID (UID) and a start date and {RM_ITEM_INVENTORY_NDT_LATEST_TEST} also contains the UID that I join on but can sometimes contain records but sometimes does not.

{RM_ITEM} has a date in it.

I want a forumla that looks like the following:-

if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} = 1 then dateadd ("m",72,{RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE})

else

if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} = 2 then dateadd ("m",36,{RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE})

else

if {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} = 3 then dateadd ("m",12,{RM_ITEM_INVENTORY_NDT_LATEST_TEST.LATEST_TEST_DATE})

else

if isnull ({RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS}) then {RM_ITEM.START_DATE}

++++++++++++

This formula is not working because it is never bringing back any records on the {RM_ITEM} side that DO NOT have a corresponding {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS} record. Is the last bit of my formula wrong?

Thanks in advance.

David.

 

[fisheromacse]

start with putting the null check first in your formula.

 

[IanWaterman]

Also make sure you are using a left join from {RM_ITEM} to {RM_ITEM_INVENTORY_NDT_LATEST_TEST.CLASS}

Ian

 

[Madawc]

As IanWaterman says, you need to check your join. When you take data from two tables, A and B, with a shared account number, an ordinary link will give you just the cases where there are details just on table A.

The standard solution is to make the link a left-outer, meaning that it's OK to show Table A records without a coresponding Table B record. This can be set on the Database expert.

This too can run into problems. If you link table A to table B using a left-outer, but then put a condition on table B, it treats it as if it was an equals join, refused to show table A entries without a table B entry. This is part of Crystal's general habit of stopping whenever it gets a null value. When the table B entry is absent, all of its fields are null.

You get round this by including a test for null value first, followed by the table-B condition.

I'd also suggest you do a test detail line to find out what is and is not showing, before writing formulas for the data.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options. In this case, it probably makes no difference.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP