A string is require here?

[AlreadyLost]

I have a field with this formula:

If {CD_EMPLOYEE.DEPT} = '012'
then "N/A"
else (if ({TB_DAILY.ACTIVITY_DATE}in {?Fiscal Year}to {?End Fiscal Year})
and {TB_DAILY.SERVICE} in ['013','036','037']
then {TB_CORE_FACILITY.FACILITY_ID})

Problem: My report was able to display NA for dept 12 but the "else" part of the formula is blank in the report.
Why? how can I fit is so that i will display data?

 

[synapsevampire]

Try:

If {CD_EMPLOYEE.DEPT} = '012'
then "N/A"
else (if ({TB_DAILY.ACTIVITY_DATE}in {?Fiscal Year}to {?End Fiscal Year})
and {TB_DAILY.SERVICE} in ['013','036','037']
then
totext({TB_CORE_FACILITY.FACILITY_ID}),0,"")

Data types returned by a formula have to be the same, you were attempting to return a string or a numeric.

-k

 

[bdreed35]

Try removing the extra parens, like this:

If {CD_EMPLOYEE.DEPT} = '012' then
    "N/A"
else if ({TB_DAILY.ACTIVITY_DATE} in {?Fiscal Year} to {?End Fiscal Year} and 
         {TB_DAILY.SERVICE} in ['013','036','037']) then 
    {TB_CORE_FACILITY.FACILITY_ID}

~Brian

 

[AlreadyLost]

I am attempting to return a numeric. I would probably need to do a sum or distinct count for this field in my group header. I am using Crystal 10.

 

[AlreadyLost]

I try both suggestion and still did not work.

When I try this formula:
Try:

If {CD_EMPLOYEE.DEPT} = '012'
then "N/A"
else (if ({TB_DAILY.ACTIVITY_DATE}in {?Fiscal Year}to {?End Fiscal Year})
and {TB_DAILY.SERVICE} in ['013','036','037']
then
totext({TB_CORE_FACILITY.FACILITY_ID}),0,"")

I keep getting a error in the comma next to the zero: "The ) is missing".

--
The second suggestion I try and still get blank as the result.

This field is on my Group Header.. I need to count the number of facility.

 

[AlreadyLost]

I need to display "N/A" as the result and also count the number of facility Id base on the formula:

If {CD_EMPLOYEE.DEPT} = '012'
then "N/A"
else (if ({TB_DAILY.ACTIVITY_DATE}in {?Fiscal Year}to {?End Fiscal Year})
and {TB_DAILY.SERVICE} in ['013','036','037']
then {TB_CORE_FACILITY.FACILITY_ID})

My result should look like this:
Dept 12 Employee A N/A
Dept 12 Employee B N/A
Dept 13 Employee C 30 (this would the count of facility id)
Dept 14 Employee D 67
Dept 35 Employee E 2

 

[synapsevampire]

Sorry, use:

If {CD_EMPLOYEE.DEPT} = '012'
then "N/A"
else
if {TB_DAILY.ACTIVITY_DATE}in {?Fiscal Year}to {?End Fiscal Year}
and
{TB_DAILY.SERVICE} in ['013','036','037']
then
totext({TB_CORE_FACILITY.FACILITY_ID},0,"")

You had extra parens in there, I should have checked it out more closely.

-k

 

[lbass]

Try this in two steps. First insert a group on department (you can suppress the group header and footer if you wish) and create a formula like this {@facilities}:

If {CD_EMPLOYEE.DEPT} <> '012' and
{TB_DAILY.ACTIVITY_DATE} in {?Fiscal Year}to {?End Fiscal Year} and
{TB_DAILY.SERVICE} in ['013','036','037']
then 1

Then create a second formula:

if {CD_EMPLOYEE.DEPT} <> '012' then "N/A" else
totext(sum({@facilities},{table.dept}),0,"")

I can't tell whether the count is supposed to be for each department or for each employee. If it is meant to be for each employee, then you need a second group on employee, and change {table.dept} to {table.employee} in the second formula.

-LB

 

[AlreadyLost]

Still does not work.. Got this error message.

"Too Many arguments have been to this function". 0,"".

What if I need to convert Facility ID to number.

 

[lbass]

Please check your work compared to the suggestions and also indicate who you are responding to.

-LB

 

[AlreadyLost]

Thanks all for helping. It worked!

I appreciated everyone for helping.

Thanks soooooo much. I been trying to figure it out for weeks!!.