Null statement

[max274]

I have a field that has a long description in it. My problem is when i plug in my formula all data in the long description populates fine. But the fields that does not have long descriptions will not even show up on the report because there is not a long description. When I take out my formula the line(poline.description) reappears. Here is what I have:

if not isnull(({LONGDESCRIPTION.LDTEXT }))
then {POLINE.DESCRIPTION}+{LONGDESCRIPTION.LDTEXT}
else {POLINE.DESCRIPTION}

the above statement when in will not show the
poline.description when there is no longdesription.ldtext

can anyone expand on this for me

 

[IanWaterman]

Its likely to be the join to your table LONGDESCRIPTION. Make sure its a left outer.

In your select statement is there a condition on LONGDESCRIPTION if there is this will over ride the Left outer join.

Ian

 

[Turkbear]

Hi,
If that is not the problem, maybe not a NULL but a blank ( not always treated the same, depending on the datbase);anyway try reversing the logic:

if (isnull({LONGDESCRIPTION.LDTEXT }) or Trim({LONGDESCRIPTION.LDTEXT} = "") 
Then 
{POLINE.DESCRIPTION}
else
{POLINE.DESCRIPTION}+{LONGDESCRIPTION.LDTEXT}

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[max274]

the field im using to populate my data does NOT show on the Table(under the linking section) but does show up as a feild when the table is on my report.

As far as the left outer join- is it set = , <= not sure which one to use?

 

[IanWaterman]

Which version of Crystal are you using.

Sounds like the LDTEXT field is a memo, some older versions of Crystal did not handle MemoFields very well.

Ian

 

[max274]

using version: crystal 2008 -version 12 i think it is

 

[IanWaterman]

Can you show both select statement and SQL statement created bt Report.

Ian

 

[max274]

Here are the only statements I have for the report:

{PO.PONUM} = {?PO number}
and the 1st one I posted above I wrote as a formula but also works as a select statement:
if not isnull(({LONGDESCRIPTION.LDTEXT }))
then {POLINE.DESCRIPTION}+{LONGDESCRIPTION.LDTEXT}
else {POLINE.DESCRIPTION}




here is the sql:
SELECT "PO"."PONUM", "PO"."STATUS", "PO"."VENDOR", "COMPANIES"."NAME",
"COMPANIES"."ADDRESS1", "COMPANIES"."ADDRESS2", "COMPANIES"."ADDRESS3",
"COMPANIES"."ADDRESS4", "COMPANIES"."PHONE", "COMPANIES"."FAX",
"COMPANIES_1"."NAME", "COMPANIES_1"."ADDRESS1", "COMPANIES_1"."ADDRESS2",
"COMPANIES_1"."ADDRESS3", "COMPANIES_1"."ADDRESS4",
"COMPANIES_1"."CONTACT", "COMPANIES_1"."PHONE", "COMPANIES_1"."FAX",
"PO"."PURCHASEAGENT", "PO"."ORDERDATE", "PO"."REQUIREDDATE",
"PO"."PAYMENTTERMS", "PO"."SHIPVIA", "PO"."FREIGHTTERMS", "PO"."FOB",
"POLINE"."DESCRIPTION", "POLINE"."ITEMNUM", "POLINE"."ORDERQTY",
"POLINE"."UNITCOST", "POLINE"."LOADEDCOST", "POLINE"."POLINENUM",
"PO"."SHIPTOATTN", "LONGDESCRIPTION"."LDTEXT"

FROM "MAXIMO"."PO" "PO", "MAXIMO"."LONGDESCRIPTION" "LONGDESCRIPTION",
"MAXIMO"."POLINE" "POLINE", "MAXIMO"."COMPANIES" "COMPANIES_1",
"MAXIMO"."COMPANIES" "COMPANIES"

WHERE ("LONGDESCRIPTION"."LDKEY"="POLINE"."LDKEY") AND
("PO"."PONUM"="POLINE"."PONUM") AND
("PO"."VENDOR"="COMPANIES_1"."COMPANY") AND
("PO"."SHIPTO"="COMPANIES"."COMPANY") AND "PO"."PONUM"='3386-053'