If statement with IsNull not working correctly.

[Jeremyjm]

Hi,

I'm trying to pull the promise date on the order if there isn't a promise date for each line item. I've used this type of formula with ship dates, but I can't pull the promise date from the order.

If IsNull ({PURC_ORDER_LINE.PROMISE_DATE}) then {PURCHASE_ORDER.PROMISE_DATE} else {PURC_ORDER_LINE.PROMISE_DATE}

Thanks,

Jeremy
Crystal 8.5 Developer

Regards,

Jeremy M.
Systems Administrator

Disclaimer:This person recently stepped in a pile of $#!^ and inherited a sys. admin. position. Although he has a limited knowledge base in some areas, he promises to do as much research as possible before posting. Please work with him, as he will appreciate any

 

[dgillz]

Are your 2 tables linked properly?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Jeremyjm]

Yes, I've even created a smaller version with just those two tables.

There's only one difference between my sales and purchase reports. On the sales report, either the line or order date was always entered. On the purchase report both of the fields on some orders are blank.

Do I have to take in account for that in my formula?

Regards,

Jeremy M.
Systems Administrator

Disclaimer:This person recently stepped in a pile of $#!^ and inherited a sys. admin. position. Although he has a limited knowledge base in some areas, he promises to do as much research as possible before posting. Please work with him, as he will appreciate any

 

[lbass]

Try something like:

If IsNull ({PURC_ORDER_LINE.PROMISE_DATE}) and
isnull({PURCHASE_ORDER.PROMISE_DATE}) then
date(9999,09,09) else
//to identify those with no date or use date(0,0,0) to create
//a blank field
if isnull({PURCHASE_ORDER.PROMISE_DATE}) then
{PURC_ORDER_LINE.PROMISE_DATE} else
if isnull({PURC_ORDER_LINE.PROMISE_DATE}) then
{PURCHASE_ORDER.PROMISE_DATE} else
{PURC_ORDER_LINE.PROMISE_DATE}

-LB

 

[Jeremyjm]

Maybe I do have a linking issue, seems no matter what I try to insert into that field, it will only pull in the purc_order_line.promise_date.

Regards,

Jeremy M.
Systems Administrator

Disclaimer:This person recently stepped in a pile of $#!^ and inherited a sys. admin. position. Although he has a limited knowledge base in some areas, he promises to do as much research as possible before posting. Please work with him, as he will appreciate any

 

[lbass]

If we assume that there are only purchase order lines if there are purchase orders, then you need a left join FROM the Purchases table to the Purchases line item table, and the tables should be linked on some kind of purchases ID.

-LB

 

[Jeremyjm]

Thanks for your help lbass. I originally had the po lines table linked to the po, so I even tried reversing the tables and used a left join and I still have the same issue. I'm wondering if has to do with how I'm manipulating the time and date field. I'll have play around a little bit more, when I have time.

I just decided to put both columns in report and did a formula to hide the PO promise date if the PO line promise date is present.

Thanks again.

Regards,

Jeremy M.
Systems Administrator

Disclaimer:This person recently stepped in a pile of $#!^ and inherited a sys. admin. position. Although he has a limited knowledge base in some areas, he promises to do as much research as possible before posting. Please work with him, as he will appreciate any