Two fields need to equal 100%

[deego55]

Hi. I have two fields that I would like to allow a user to enter a percentage in. The total of the value of the two fields needs to equal 100%. Is there a way to allow values to be entered in either field and have the other field automatically change to the remaining percentage?

I could only figure out how to make one a calc as the remainder, but i need to allow values to be entered in either field, so this wont work.

Thanks.

[John Mark Osborne]

Use an auto-enter calculation on both fields:

Field1:

Case(IsEmpty(field2); field1; 100 - field2)

Field2:

Case(IsEmpty(field1); field2; 100 - field1)

Make sure you keep the option to "do not replace existing value if any" checked. Otherwise, once a value is entered into one of the fields, you won't be able to override it.

[comment]

I don't think a circular definition like that is going to work (at least not for both fields).

See here for a possible solution. Note however, that it requires hard-coding the fields' names into a text string. If you ever change the names, you will need to update the calcs manually.

[John Mark Osborne]

The technique works fine as long as you enter data once because the "do not replace" option is checked. However, you need to clear the fields in order for it to evaluate once the fields have been populated. I looked at the thread you reference and used the technique to come up with these formulas that seem to work pretty well:

Field1:

Case(Get(ActiveFieldName) = "field2"; field2; 100 - field1)

Field2:

Case(Get(ActiveFieldName) = "field2"; field2; 100 - field1)

The nice thing about this technique over what I posted earlier is that the values update when you change either field. But, I still don't like the static reference to a field name. I worked on a workaround but couldn't get anything to work. Maybe somebody else will find something that isn't dependent on a field name.