Trying to get Total Family Size and Sub Total to an amount to = a text

My Field are Total Family Size, Sub Total

Category=

Total Family Size= 2 and "Sub Total"< "$16,460.00" = "( Eligible)"

Trying to get Total Family Size and Sub Total to an amount to = a text

My Field are Total Family Size, Sub Total

Category=

Total Family Size= 2 and "Sub Total"< "$16,460.00" = "( Eligible)"

Do you have a "table" of values that would make the final result as "Eligible" or other value?

If you only have a few "rules", a Case() calculation would work. If you have many, it might be easier to have a "lookup table" with the first two and then be able to test your fields against the "lookups" to get back the "Eligible" (or other value).

Beverly

- 1 person found this helpful
*beverly has an amazing way of figuring out the real problem when the information is scarce.**The rest of us might need more explanation and the title in the form of a question.**Please give us more more description of what you want the result to be.*If I take your calculation literally,

the result has only been boolean (1 or 0),

because the calculation is only the result of comparisons (=, <, =).

Even if Total Family Size is equal to 2 and Total Family Size= 2 is true,

"Sub Total" < "$16,460.00" = "( Eligible)" will always be false,

because "Sub Total" is a constant text string being compared to the boolean result of another comparison,

which is also the comparison of two constant text strings "$16,460.00" = "( Eligible)",

which will also always be 0.

If Category is a field, and if you want the result to be either "( Eligible)" or empty, then it should be written as follows:

Set Field [ Category;

If

(

Total Family Size = 2

and

Sub Total < 16460.00;

"( Eligible)";

""

)

]

*(With my formatting added for emphasis)* *In the calc are you using quotes around a field named***Sub Total**and around number values like "$16,460.00"?Then you want the Case statement suggested by beverly and philmodjunk or just a bunch of "or"s like this

Set Field [ Category;

If

(

(

Total Family Size = 2

and

Sub Total < 16460.00

)

or

(

Total Family Size = 3

and

Sub Total < 20784.00

);

"( Eligible)";

""

)

]

- 1 person found this helpful
I'd use the "Case()" as it is easy to add/remove/edit the conditions that would be TRUE. However, if it gets overly long, a table of these "rules" may be better.

Case (

Total Family Size = 2 and Sub Total < 16460 ; "Eligible" ;

Total Family Size = 3 and Sub Total < 20784 ; "Eligible" ;

// add conditions here

"Not Eligible" // default

)

Beverly

EDIT phil is correct. comparisons should be on numbers and not quoted text. I simply copied the text by OP to present the idea that Case() can be flexible for many conditions.

Careful, we have a case function with some extra quotes that shouldn’t be there. One, after the word total, will cause an error when you commit the calculation and the other set, enclosing a dollar amount, will keep number comparison from producing correct result.

Easy to do—I made a similar error earlier in this thread and had to go back and fix it.

But it consider what happens when eligibility requirements change and the sub total is no longer 16460 for size = 2. With the expression as written you then have to find the case function in your solution and update it—possibly with a brief shut down of user access while you update.

If, on the other hand, you use size to look up max eligibility level from a record, you can simply update the value in that record—a task that does not require developer access and skill to do as it is now a basic data entry task.

Don't forget to mark this question Answered.