Unanswered: Advanced Formula Field to get total - Correlate entry with number to get total
I have several fields that need to be totaled on my form. The problem is the entries on the fields won't match the number that is added to the equation. For instance: One of the fields has two options to select (superficial & deep) that correlate to 0 and 1, which are the numbers that will be added to the equation. How do I have the person select the word but then have the correlating number be added to the equation?
Do I need to have a table with just those two entries in it and another column with the number?
Another field within this equation will have users input a size but that number isn't used in the equation. The number used in the equation depends on the size entered where 0 through 3 = 1, 3 through6 = 2 and greater than 6 = 3.
The last field is a yes/no field where yes equals 1 and no equals 0.
You can use a combo box with two columns, one for the number and the other for the word. The number column would be the bound column but hidden. You could base the combo on a table, which I would likely do, or hard-code them as a value list.
that makes sense, but how would I do that with the field where the user inputs the size of something (this could be any size with any number of decimals). Where the number for the equation is dependent upon the size?
Each number for the equation is like this: if the size is 0-3 then the number for the equation is 1, if the size is 3-6 then the number for the equation is 2, and if the number is greater than 6 then the number for the equation is 3. However, I can't just give the user those three size options. They need to be able to enter any size.
I obviously can't have a row of data for every imaginable size.... The only thing I can think of is to have two fields - one where one of the three size options are selected and another where the actual size is put in. However, this is an extra step for the user and I would rather not do that.
I'd likely still use a table, but with 3 fields; the lower and upper values of the range, and the resulting values. That one I wouldn't do with a combo, but look up the value after the user enters the size into a textbox and put it in another textbox.