asked on

exclude null values (or not isnull values) from formula results

Okay, here's my formula, trying to find the phone descriptor values that don't conform to a set list. Problem is I'm also pulling values that are null. Tried the not isnull section but now I'm pulling all the values that are not null across all four phone descriptors. Thanks in advance for the help!

isnull({Q_CLIENTS.DateClosed}) and

(not ({Q_CLIENTS.PhoneDesc1} in ["Assistant", "Cell", "Cell2", "Home", "Home Fax", "Home2", "Other", "Other Fax", "Pager", "Work", "Work Fax", "Work2"]) or
not ({Q_CLIENTS.PhoneDesc2} in ["Assistant", "Cell", "Cell2", "Home", "Home Fax", "Home2", "Other", "Other Fax", "Pager", "Work", "Work Fax", "Work2"]) or
not ({Q_CLIENTS.PhoneDesc3} in ["Assistant", "Cell", "Cell2", "Home", "Home Fax", "Home2", "Other", "Other Fax", "Pager", "Work", "Work Fax", "Work2"]) or
not ({Q_CLIENTS.PhoneDesc4} in ["Assistant", "Cell", "Cell2", "Home", "Home Fax", "Home2", "Other", "Other Fax", "Pager", "Work", "Work Fax", "Work2"])) and

not isnull({Q_CLIENTS.PhoneDesc1}) and
not isnull({Q_CLIENTS.PhoneDesc2}) and
not isnull({Q_CLIENTS.PhoneDesc3}) and
not isnull({Q_CLIENTS.PhoneDesc4})

peter57r

Hi rmoelders,

What are you using this formula to do?

Pete

rmoelders

ASKER

I'm trying to find the phone descriptors (phoneDesc1, etc.) that don't conform to a set list of values. I can't control values on the data entry side, so I'm using the reporting to correct entries after the fact.

peter57r

So in English, I interpret this as producing a value of True if :

( Dateclosed is null ) AND (there is an unlisted entry in one of the PhoneDescX entries ) AND (none of the PhoneDescX entries is null).

In any other situation the expression will produce False. (So any empty PhonedescX will produce a False)

Is that what you intend?

Maybe you want to look at:

(isnull({Q_CLIENTS.PhoneDesc1}) Or {Q_CLIENTS.PhoneDesc1} in ["Assistant", "Cell", "Cell2", "Home", "Home Fax", "Home2", "Other", "Other Fax", "Pager", "Work", "Work Fax", "Work2"]) )
Or
(isnull({Q_CLIENTS.PhoneDesc2}) Or {Q_CLIENTS.PhoneDesc2} in ["Assistant", "Cell", "Cell2", "Home", "Home Fax", "Home2", "Other", "Other Fax", "Pager", "Work", "Work Fax", "Work2"]) )
Or ........

But i'm not clear where you are trying to use this formula.

Pete

rmoelders

ASKER

This part is correct:

( Dateclosed is null ) AND (there is an unlisted entry in one of the PhoneDescX entries )

but there can be null values, because not all clients have 4 phone numbers. So I want to exclude the null entries from my results of unlisted entries.

peter57r

Will you please explain what this formula is supposed to do...
(Not what your report is supposed to do) The formula produces a result of True or False - how is that result used?

Pete

rmoelders

ASKER

I want the formula to display clients for which there is a phone number descriptor field that doesn't conform. Sample results:

PhoneDesc1 PhoneDesc2 ...
John Doe Primary Mom's house
Jane Johnson House Call in car

I don't want (and am getting) clients who are not null but the descriptors conform for all 4 fields - if I use the not isnull on all 4 fields

I don't want (and am getting) clients who are null if I don't add the null catch, because null is not one of the accepted values in the formula. I could add it like this

not ({Q_CLIENTS.PhoneDesc1} in [NULL, "Assistant", "Cell", "Cell2", "Home"... - see the added null at the front of the list, but this won't work. But that is essentially what I need to accomplish.

ASKER CERTIFIED SOLUTION

moderndist

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rmoelders

ASKER

Does the trick, thank you very much moderndist!