### Title

Case function vs look up table(s)

### Post

Hi there. I am using some long Case function statements to calculate Standard test scores based on two conditions: test age and raw score. The Case statements work, but I have bumped up against number of character limits and some unidentifiable limit (seems based on length of statement) that require me to edit the formulas for each test period. I wondered if a look up table would work, but I don't know how I could structure that with the age ranges and raw scores. Any help or other ideas/solutions would be greatly appreciated! :)

Here's the beginning of one of the statements with a couple of notes:

Let ( [

f1 = Y2AP1_TestingDetail::Y2AP1_B1_Test_Age ;

f2 = Y2AP1_WIATRC_W_RAW ;

a4 = f1 ≤ 7.03 ;

a5 = f1 ≤ 7.07 ;

a6 = f1 ≤ 7.11 ;

a7 = f1 ≤ 8.03 ;

a8 = f1 ≤ 8.07 ;

a9 = f1 ≤ 8.11 ;

a10 = f1 ≤ 9.03 ;

a11 = f1 ≤ 9.07 ;

a12 = f1 ≤ 9.11 ;

a13 = f1 ≤ 10.03 ;

a14 = f1 ≤ 10.07

// WIATWR SS calc uses additional range of 10.08 - 10.11

] ;

Case (

f1 ≤ 6.11 ; "out of range" ;

f1 ≥ 10.08 ; "out of range" ;

a4 and f2 ≤ 0 ; 56 ; a4 and f2 ≤ 1 ; 60 ; a4 and f2 ≤ 2 ; 63 ; a4 and f2 ≤ 3 ; 65 ; a4 and f2 ≤ 4 ; 67 ; a4 and f2 ≤ 5 ; 69 ; a4 and f2 ≤ 6 ; 70 ; a4 and f2 ≤ 7 ; 71 ; a4 and f2 ≤ 9 ; 72 ; a4 and f2 ≤ 10 ; 73 ; a4 and f2 ≤ 11 ; 74 ; a4 and f2 ≤ 12 ; 75 ; a4 and f2 ≤ 14 ; 76 ; a4 and f2 ≤ 15 ; 77 ; a4 and f2 ≤ 16 ; 78 ; a4 and f2 ≤ 17 ; 79 ; a4 and f2 ≤ 19 ; 80 ; a4 and f2 ≤ 20 ; 81 ; a4 and f2 ≤ 21 ; 82 ;

ETC -- tests for this age range continue for a while.

a5 and f2 ≤ 0 ; 49 ; a5 and f2 ≤ 1 ; 53 ; a5 and f2 ≤ 2 ; 56 ; a5 and f2 ≤ 3 ; 58 ; a5 and f2 ≤ 4 ; 59 ; a5 and f2 ≤ 5 ; 60 ; a5 and f2 ≤ 6 ; 61 ; a5 and f2 ≤ 7 ; 62 ; a5 and f2 ≤ 8 ; 63 ; a5 and f2 ≤ 9 ; 64 ; a5 and f2 ≤ 10 ; 65 ; a5 and f2 ≤ 11 ; 66 ; a5 and f2 ≤ 12 ; 67 ; a5 and f2 ≤ 13 ; 68 ; a5 and f2 ≤ 14 ; 69 ; a5 and f2 ≤ 15 ; 70 ;

ETC, ETC for each age range.

Thank you!

This looks somewhat familiar. Have you asked about this in the past?

A look up table could be constructed for this and it would be much, much easier to manage and allows you to change the criteria by editing values in a table instead of redefining a case function calculation.

If I understand the logic correctly here, you have different results to return for values of f1 and f2 that fall in specific ranges of values. such as

f1 values of 6.12...7.03 that also have f2 values of

<0 should return a value of 56Same f1 range and f2 > 0 and f2

<1 returns 60and so forth...

If I have parsed this function correct, then you could set up a lookup table of values that looks like this for the first few entries:

f1 | f2 | Result

7.03| 0 | 56

7.03| 1 | 60

7.03| 2 | 63

and so forth for the first group...

The next group:

f1 | f2 | Result

7.07| 0 | 49

7.07| 1 | 53

7.07| 2 | 56

and so forth....

Let's call that table Results. If you then defined f1 and f2 as fields instead of let variables, you could use those fields as match fields in a relationship:

YourTable::f1

<Results::f1 ANDYourTable::f2

<Results::f2specify a sort order in the relationship that sorts the related records into ascending order by Result.

And then you can use a looked up value setting or auto-enter calculation to copy the value of Results::Result into a field of YourTable instead of using this complex case function.

PS. the data shown here looks like data derived from some kind of formula. If you can research the formula used to produce these values, it may be possible to use that formula in a calculation and that will be simpler than either this look up table or the Case function.