Custom Form Code - AR Aging - Exclude Customers with a value in a UDF

I have a client that has a UDF in customer maintenance for customers that have a portal. They want the AR Aging to include either the customers with a value in the UDF or the customers where the UDF is blank. 

I linked the AR_Customer table to the aging work table. I can print the aging for customers that have a value in the UDF. My problem if I try and include customers where the UDF is blank, I get no data in my report. I have tried:

{AR_Customer.UDF_PORTAL} = "" AND isnull({AR_Customer.UDF_PORTAL}) and I've tried the 'Exceptions for Nulls' and 'Default Values for Nulls'. Nothing works. I just get the headings and footers and no data whatsoever!

Any ideas? All help is appreciated.

Suzanne.

  • 0

    Always do the NULL check before testing for a value.  Also, your selection criteria should be "OR" because a value can never be "" and NULL at the same time.

    Or:

    Pass your UDF through to the work table as a UDF (instead of linking the table), which should clean the NULL values for you.

    Or:

    Set a blank default value for your UDF, and initialize existing rows to "" (instead of NULL).  Then you can use the Selection grid instead of a customized report.

  • 0 in reply to Kevin M

    Not sure what you mean by 'Always do the NULL check before testing for a value.' 

    Guess my post was unclear as I tried each option individually (= "" and isnull) in the selection criteria, not both at the same time.

    It looks like the best way to go is to add the UDF to the work table. That way I don't join the customer table for one field.

    Thanks for you response.

  • 0 in reply to suzmwatson

    I meant this:

    isnull({AR_Customer.UDF_PORTAL}) or {AR_Customer.UDF_PORTAL} = ""

    If the value truly is null, testing for "" first will fail. The boolean checks are done one at a time.  Crystal Reports chokes on the null, so in any formula requiring a null check, always do that as the absolutely first reference for the potentially null field.

    To make it easier to handle I often do a formula field to clean the data value of nulls, then use that formula wherever I wish without having to handle nulls... because that is already done by the formula.

    if isnull(fieldname) then "" else fieldname

    (Use a 0 for numeric fields).