# Thread: Advanced Formula Field to get total - Correlate entry with number to get total

1. Registered 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?

2. Registered User
Join Date
May 2005
Location
Posts
2,888
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.

3. Registered 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 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.

4. Registered User
Join Date
May 2005
Location
Posts
2,888