IsNull Question 2

[erikhertzel]

All,

CR 8.5

I have a formula that I am using for a field to calc. an amount in the database.

Here is the formula:

({OrderDtl.UnitPrice} * {OrderDtl.OrderQty}) - {OrderDtl.Discount}

When I add this field onto the formula, it won't return any data and I think it's because in this instance there is no data in the data range that I have , but in the future there could be.

({OrderDtl.UnitPrice} * {OrderDtl.OrderQty}) - {OrderDtl.Discount} - {OrderMsc.MiscAmt}

The {OrderMsc.MiscAmt} is the field giving me trouble. I don't use the Misc. Amount field very much and in this instance it's not being used in any of the records, so it's giving me a blank report. When I take this out, it works fine with the orginal formula and gives me hundreds of records.

How do I write the formula and I think it's something to do with the IsNull so that this will work correctly?

Thanks

 

[midearth]

Try checking for null first such as:

if isnull({OrderMsc.MiscAmt}) then
({OrderDtl.UnitPrice} * {OrderDtl.OrderQty}) - {OrderDtl.Discount} else
({OrderDtl.UnitPrice} * {OrderDtl.OrderQty}) - {OrderDtl.Discount} - {OrderMsc.MiscAmt}

that should do the trick.

 

[erikhertzel]

Hmmm....that still didn't do the trick. When I inserted that formula I went from lots of records to no records.

Any other ideas?

Thanks,

 

[synapsevampire]

Why are you showing 2 different formulas?

Anyway, Crystal 8.5 doesn't include a table in the query until a field is used from that table, and since this is seemingly the 1st instance of a field from that table, I'd guess that you have the joins wrong.

Try changing to a Left Outer.

-k

 

[erikhertzel]

Thanks to both of you, it was the combination of both that did the trick.

 

[synapsevampire]

In lieu of coding around nulls, you can change the File->Report Options to convert null values to default.

-k