Proper use of 'Case'

[forskare]

Not sure if I'm using the case function properly but this is what I have:

CASE(isnotblank(t_form_dates->CAF_Date1), Name+" signed a form dated "+t_form_dates->CAF_Date1+" acknowledging all "+Category+" must be reported.",.t.)

When working properly, it should read:

John signed a form dated 1/12/10 acknowledging all income must be reported.

The values of the Name, CAF_Date1 and Category fields are chosen by the user.

Thanks for the suggestions!

kenn

[Stan Mathews]

Re: Proper use of 'Case'

Almost.

Isnotblank() requires that the field name be quoted and you're missing the return value for the .t. section.

CASE(isnotblank([COLOR="red"]"[/COLOR]t_form_dates->CAF_Date1[COLOR="red"]"[/COLOR]), Name+" signed a form dated "+t_form_dates->CAF_Date1+" acknowledging all "+Category+" must be reported.",.t.[COLOR="Red"],""[/COLOR])

I've never used isnotblank() supplying the field name with the tablename-> syntax so I'm not sure how that will work.

testcaf_date1 = {01/01/2010}
name = "Ken Nordin"
category = "malfeasance"

? CASE(isnotblank("var->testcaf_date1"), Name+" signed a form dated "+testcaf_date1+" acknowledging all "+Category+" must be reported.",.t.,"")
= "Ken Nordin signed a form dated 01/01/2010 acknowledging all malfeasance must be reported."

[Steve Andrews]

Re: Proper use of 'Case'

You have two conditions but only one value. Every condition listed in a CASE() statement must have a value to follow:

CASE(isnotblank(t_form_dates->CAF_Date1), Name+" signed a form dated "+t_form_dates->CAF_Date1+" acknowledging all "+Category+" must be reported.",.t.,"")

Steve

[G Gabriel]

Re: Proper use of 'Case'

An IF() statement is more appropriate here. You are only testing for one condition.
And rather than isnotblank() use isblank() or better off ="" and reverse the true/false arguments, i.e.:
if(field="","",...the other statement..)

[forskare]

Re: Proper use of 'Case'

Thanks for the quick replies.

Stan: The table name was used because the field is in a child table, Alpha automaticly included the table name.

Steve: I tried adding ,"" but no go.

Gabe: I'm using the case statement here because once this part is working, I will be adding other parts as explained below.

The idea is to simplify as much as possible and the user fills in only the fields and while the form will contain other fields, these 3 are for this part of the report. These are the fields and values where the bottle neck is:

Forms: CAF; HRF; CRF; MED
Categories: Income; Assets, Persons

It is possible for a person to have completed more than one form and it could effect more than one Category. The best way I've thought of to create this is with a calculated field. The end result would be for the sentence to read something like:

John signed CAF forms dated 1/12/09 acknowledging all income must be reported.

If the issue is assets instead of income, then the sentence would read the same except the word 'assets' would be used instead of 'income'.

To add to the mess, both income and assets could be a factor so I would need to have one sentence for each or a sentence that would read something like:

John signed CAF forms dated 1/12/09 acknowledging all income and assets must be reported.

OK, to complicate things a bit more, it is possible to use multi[ple forms and categories so....

John signed CAF forms, HRF forms and CRFforms dated 1/12/09, 2/12/09 and 4/1/09 acknowledging all income and assets must be reported.

I'm trying to start 'small with this and get it working a little at a time.

Thank you for your help.

kenn

[Stan Mathews]

Re: Proper use of 'Case'

It'll be a lot easier to answer questions and provide solutions if you attach a small sample set here with a few dummy records. Steve's solution should work with the addition of the quotes around the field name in the isnotblank() - actually the same thing I suggested.

[forskare]

Re: Proper use of 'Case'

It'll be a lot easier to answer questions and provide solutions if you attach a small sample set here with a few dummy records. Steve's solution should work with the addition of the quotes around the field name in the isnotblank() - actually the same thing I suggested.

Hi Stan,

I won't be able to send it until this evening as I have no internet access for my laptop at work and my work computer only has the RT.

I did get this to work per your suggestion as well as Gabe's but using iif( , ,"").
Using case( , , .t.) does not work.

Thanks, kenn

[Stan Mathews]

Re: Proper use of 'Case'

Using case( , , .t.) does not work.

No one suggested

Using case( , , .t.) because the case() function requires pairs of paramaters.

The syntax is

case(logical_test,result,anotherlogicaltest,result,.t.,lastchanceresult)

the .t.,lastchanceresult

says return lastchanceresult if all the previous logical tests failed.

Note: .t. can be considered a logical test that always returns true.

[forskare]

Re: Proper use of 'Case'

No one suggested

Using case( , , .t.) because the case() function requires pairs of paramaters.

So, I should use the IF ... THEN ... ELSE function; correct?

kenn

[Stan Mathews]

Re: Proper use of 'Case'

Not if then else.

If(logical_test,true_result,false_result) works for one test. You can nest if()s like
If(logical_test,true_result,If(logical_test2,true_result,If(logical_test3,true_result,false_result)))

but pretty soon that becomes hard to maintain and edit.

Stick with the case() function and you expand from

case(logical_test,result,.t.,final_result)
to
case(logical_test,result,logical_test2,result2,.t.,final_result)
case(logical_test,result,logical_test2,result2,logical_test3,result3,.t.,final_result)
etc

The .t.,final_test is just a fail safe to make sure the case() function returns some value in every instance.

[forskare]

Re: Proper use of 'Case'

Thank you, Stan!!

kenn