SQL lookup table

Henrik Lyder

I need a lookup table function in a Matster-Detail report. In an SQL I might create the detail table as

SELECT T0.Index,T1.CodeName FROM Table0 T0
LEFT JOIN Table1 T1 ON T0.Index=T1.Index

Or I could create a lookup

SELECT DISTINCT CodeName From Table1
WHERE Index="insert index from detail table here"

In the first example I have a problem getting the link to the master table to work.
In the second I do not know how to create the report element to execute the query and insert the value into the memo field.

I was considering a Master-Detail-Detail report where the detail-detail is linked on the index fields, but here I run into a problem that the last Detail may not contain any data, thus I need to test for a NULL field something in a memo field like

[IF([tblDetailDetail."CodeName"]=NULL,'NO NAME',tblDetailDetail."CodeName")]

The CodeName is an integer field and when I execute the above solution I get 0 in place of NULL data. 0 is a valid value so I cannot test for 0, I have to test for NULL.


Regards

Henrik Lyder

gordk

Henryk i havent worked with version 2.xx in a long time now, but IIRC you can set the report component options
convertnulls to false then you can test for nulls