Reporting Populated & Null Values

[Meg09]

Hi,

I am trying to include phone numbers on an associate listing. However, not all associates have a phone number listed in the database. Therefore when I add in the phone number field, only those who have a phone number are displayed on the report. Therefore, I tried creating an ISNULL statement to provide the phone numbers for those who have one and Unknown for those who do not, however my formula seems to have an error. The error states that the first part of my formula is not recognized. Any ideas would be appreciated!

Thanks, Meg

Iif( {'emp_phones.phone_no'} Is NULL, Unknown, {'emp_phones.area_code + emp_phones.phone_no'} where emp_phones.phone_code = 'Work')

 

[fisheromacse]

Possibly use this formula.

//{@workphone}
if(isNull({emp_phones.phone_no}) or TRIM(totext({emp_phones.phone_no})) = ""
THEN "Unknown"
ELSE
(IF {emp_phones.phone_code} = "Work"
THEN {emp_phones.area_code} & {emp_phones.phone_no}
)

Alternatively, if you need only work phones in the report, you could use the selection expert to choose only phone_code equal to work, and remove this (IF {emp_phones.phone_code} = "Work" THEN from above.