Calculation field with Case function

[YuChieh]

I have a claculation field. I need to put over 100 conditions using Case(test1, result1, test2, result2,...)

(unforuntally, there is no "regular rules" for these and I cannot use a formula.)

After I put in all the possibilities, a message "too many operators...". I guess the program does not take that many conditions.

What other options do I have to make this work?

Thank you.

[Genx]

... im not sure as to what the limit is... but remember that the calculation also has space for a default value at the end incase none of the case values are met... so what you could potentially do is point to another case testing field...i.e.

calc1:

case(test 1; result 1; test 2; result 2; calc2)

calc2:

case(test 3; result 3; test 4; result 4; calc3

calc3:.. and so on... all the final results will return to the original calc1 field

genx

[Lee Smith]

You are probably over complicating your "Case Statement" if you are using that many conditions. This is something I do too.

The messages "too many operators" can mean you have an error in one (or more) of your test. You cursor should be stopping at or near the error.

Why not post a sample of your statement, or tell us what you have and what you want it to do.

Lee

[Ender]

Yes, there is probably either a more efficient way to test for the cases, or another method that would be easier to manage.

[Genx]

...sigh they're probly right

[Genx]

... for sake of formatting your calculation try and do it this way.. it will help you make sense of it all later.. it helps make sure you have everything where it should be and also ensures that you can make sure you can easily locate all tests or results...

Case(

test 1; result 1;

test 2; result 2;

test 3; result 3;

default)

genx

[Ender]

It's nice that you corrected your case() description, but clearly YuChieh already knew the syntax for that. I think when YuChieh tells us what this case() statement is supposed to be doing, we'll be able to offer a better solution.

[Edit:] Sorry, formatting a case statement like that IS a good practice, and may help identify any syntax errors. But a hundred cases is still unwieldy, and it should be rewritten or another technique used if possible.

Edited February 2, 2006 by Guest

[YuChieh]

I attached a FM file. It's the field "weight by ounces_congress". I can do up to weight = 65.5. I need to put in 10 more conditions, up to 71.5 (increment by 0.5).

Thank you!

Test.zip

[Ender]

Hi YuChieh,

In a case like this, where you have a big list of choices that are selected based on some criteria, a lookup table is a good way to go. By putting those rates in another file and pulling in the right one based on a relationship, it makes it simpler to understand and maintain (you can edit the rates directly in the Rate file.)

I have adjusted the structure in your example to use a Rate file, and entered a few of the rates so you could see how it works. I have also removed the unnecessary duplication of fields in your example, so that the same fields hold either Senate or Congress info, where you enter the appropriate Type for each record.

See the attached.

Rates.zip

[YuChieh]

Actually, they are not duplicate fields. We need to separate congress and senate lists, and they are linked via another db. Therefore, I do it this way.

I will try your way - putting the rate in a lookup table (we are still using version6, so it's another DB). We already have way many databases; so I would like to avoid creating too many databases and try to have everything in one if possible.

Thank you for the input.

[YuChieh]

Hi,

I used the look up table and it works fine, except one problem.

10 Sheets of paper = 1 OZ.

If I had 80 sheets = 8 OZ, or 85 sheets = 8.5 OZ, I could find the Postage $ in the table.

If I had 87 sheets of paper, weight = 8.7 OZ. The postage $ is not in the lookup table.

I would need to do

if weight >= 8 and weight < 8.5,

if weight >= 8.5 and weight < 9,

How do I do that?

Thank you.

rate.zip

Edited February 2, 2006 by Guest

[comment]

Just a side note here: it's quite obvious that these rates are calculated by a very simple formula of a + x * b. For each half-pound, the rate increases by slightly less than 0.018. The result is rounded, so some steps are 0.018 and some 0.017.

[YuChieh]

Yes I know that.

like record 1, 0.017, next 3 records is 0.018. then go aback to 0.017, next 3 records is 0.018 again. a few of them in the middle are 0.0175.

I am not too sure how to write this kind of formula.

Thanks

[comment]

I don't think you need to write the formula. I think you should get the formula from whomever has written it.

[YuChieh]

It's in Excel. So the formula changes by each cell.

[comment]

I am afraid you're missing my point: someone calculated those numbers using a formula. He gave you the results. He should give you the formula instead, so you can repeat the calculation whenever you need to.

If you can't get the original calculation, then go with Ender's suggestion and use a lookup table. Note that you need to check "lookup higher" in the field definition, so that it works for ranges.

I don't want to think what will happen when the rates change.

[YuChieh]

I use the look up table and check "lookup higher" and it works for ranges.

I think everything works out fine now.

Thank you all for the ideas and solutions!!