Form Data Validation that compares fields to each other
Options
I'm hoping for a custom validation option on form fields that compares them to one another. Specifically this is in regards to numeric fields: one field cannot be greater than the other field. To take that a step further, what I really need is the option to prevent the sum of certain fields from (1) exceeding the sum of certain other fields and (2) being outside a specified range, in this case not greater than 1.
My form in question is for requesting changes to which cost center(s) an employee rolls up to. Employees can be split between multiple cost centers, so the form allows for up to 3 cost centers for the current state and up to 3 cost centers for the future state. For each cost center, the user is to enter the employee's FTE value (Full Time Equivalent, i.e. 40 hours/week=1, 20 hours/week=0.5, etc). No employee can be greater than 1 FTE, and the request may not net to an increase in their FTE value.
Let's identify the form fields A-F as follows for example.
A: FTE value currently in Cost Center #1
B: FTE value currently in Cost Center #2
C: FTE value currently in Cost Center #3
D: Requested Future State FTE value in Cost Center #4
E: Requested Future State FTE value in Cost Center #5
F: Requested Future State FTE value in Cost Center #6
(the cost centers themselves are selected in a drop-down box in separate fields, but that's irrelevant to this suggestion)
The logic I'm requesting would be: D+E+F must be equal to or less than A+B+C
Furthermore, I'd also like to implement this logic, along the same lines: A+B+C must be between 0 and 1, and also D+E+F must be between 0 and 1.
One option that may assist in implementing the above is to allow us to create additional fields that are auto-populated by the entries in A-F, eg: "Total Current State FTE" = A+B+C, and "Total Future State FTE" = D+E+F. Then the new logic would only have to apply to those two new fields: Each of them must be between 0 and 1, and "Total Future State FTE" must not exceed "Total Current State FTE"
