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.
sugarLvl ≥ 100 and sugarLvl ≤ 150; 10;
sugarLvl > 150 and sugarLvl ≤ 200; 11;
/* continue in this manner for the differrent ranges you have. */
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.
sugarLvl = 100; 1;
Floor((sugarLvl - 101)/50) + 10
Make your lookup table i.e. "Dose" with two fields, "Sugar Level" and "Dosage"
Make a relationship between your input table Sugar Level and Dose::Sugar Level
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
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
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.
In my example the value, e.g. 155, being measured is in a field named sugarLvl.
Change sugarLvl to Reading.
"Case( ... )" is the calculation that you enter in to the Specify Calculation dialog box of a field of type Calculation.
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?
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?
Sorry I took so long to get this to you. I updated with some comments and added a PDF with more info.
Edit I forgot the text file is tab separated. Also I put a note on the Lookup layout.
Diabetes ver2.zip 707.3 K
I have not need to do this so not sure of details. Use Find and Summary fields to get the average, etc. of the records you want the info for, you will need to copy and store in another field (repeating) or (table) or var. to display data from multiple periods at the same time.