Results 1 to 4 of 4

052312, 11:35 #1Registered User
 Join Date
 May 2012
 Posts
 64
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.
The total will only be a number up to 5.
How in the world would I do this?

052312, 12:02 #2Registered User
 Join Date
 May 2005
 Location
 Nevada, USA
 Posts
 2,888
Provided Answers: 6You 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 hardcode them as a value list.
Paul

052312, 12:13 #3Registered User
 Join Date
 May 2012
 Posts
 64
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 03 then the number for the equation is 1, if the size is 36 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.

052312, 12:19 #4Registered User
 Join Date
 May 2005
 Location
 Nevada, USA
 Posts
 2,888
Provided Answers: 6I'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.
Paul