10 Replies Latest reply on Nov 14, 2015 1:57 AM by greatgrey

# lookup table/field possible solution for a range of numbers?

so i have a db i am working on for helping track my diabetes.  i want to be able to enter a field for my sugar level which will be any number between 100 and 500.  I would like to have a field that will return a number to a field bases on a set of ranges for how much insulin to take.  what i mean is, if he number i enter into the Reading field is between 100 and 150 i want the Injection field to read 10, if the Reading field is between 151 and 200 i want the injection field to read 11.  etc.

I am thinking this will be a calculation or lookup table, but i am unfamiliar with the look table and how to design it.  any help would be appreciated.

• ###### 1. Re: lookup table/field possible solution for a range of numbers?

If you haven't already created a table with the ranges, you can solve this with a relatively simple calculated field as shown below.

Make the calculation something like this.

Case(

sugarLvl ≥ 100 and sugarLvl  ≤ 150; 10;

sugarLvl > 150 and sugarLvl  ≤ 200; 11;

/* continue in this manner for the differrent ranges you have. */

""

)

• ###### 2. Re: lookup table/field possible solution for a range of numbers?

If all of your ranges are exactly 50 wide (except for the first that you describe as 51), you can write a calculation like this.

Case(

sugarLvl = 100; 1;

Floor((sugarLvl - 101)/50) + 10

)

• ###### 3. Re: lookup table/field possible solution for a range of numbers?

1

Make your lookup table i.e. "Dose" with two fields, "Sugar Level" and "Dosage"

2

Make a relationship between your input table Sugar Level and Dose::Sugar Level

3

on the input table make the Dosage a lookup chose the input table in the first box and in the Dose table in second box and chose "copy next lower value" and copy value from field Dosage

if data  is setup like this

100     10

151     11

201     12

any value between 100 and 150 will return 10

151 to 200 returns 11

in this example any value of 201 or greater will return 12

• ###### 4. Re: lookup table/field possible solution for a range of numbers?

I like this idea, seems easy enough.  BUT... its never that easy for me. I am formatting the equation wrong i guess.  is the Case supposed the be the name of the field?  i have my field named Inject and the field that it calculating based on is called Reading.

• ###### 5. Re: lookup table/field possible solution for a range of numbers?

In my example the value, e.g. 155, being measured is in a field named sugarLvl.

"Case( ... )" is the calculation that you enter in to the Specify Calculation dialog box of a field of type Calculation.

• ###### 6. Re: lookup table/field possible solution for a range of numbers?

no Script or Case statement needed

see attached file FM13 can do it in 7, 5, or 3.

• ###### 7. Re: lookup table/field possible solution for a range of numbers?

greatgrey!  thats perfect!  i want to lift out what you have shown me here and incorporate it into a design i want to make for my phone to use with fmgo.  i have looked at the lookup but i cant see how it works?  how can i change the intervals?  would like to pm you about this if possible?

• ###### 8. Re: lookup table/field possible solution for a range of numbers?

RELATED: on a related note... i have these entries (i got the formula to work1 thanks!) and now i want to make a field that calculates an average for the level field for this week, this month, and this year.  Just testing I tried to just use the average operator and it doesn't seem to calculate anything.  I assume the ability to do the average for this week, this month, and this year will be much more complicated so i wanted to just test with the operator to see it work and it doesn't work.  I nay be missing something simple?