(Forms version: 11.0.2201.20436)
Edited to add: problem solved
The issue appears to be related to the fact that formulas used within the Risk Registry collection do not seem to communicate to those used in the two fields outside the collection ('Risk Category Count' and 'Risk Percentage Value Sum').
The solution was to edit the DIV formula used in 'Overall Risk Percentage' and rather than referencing the field variables for the 'Risk Category Count' and 'Risk Percentage Value Sum', use the formulas from each of those fields in the DIV:
'Overall Risk Percentage' before:
'Overall Risk Percentage' after:
The 'SUM' formula was used in the 'Risk Percentage Value Sum' field.
The 'MAX' formula was used in the 'Risk Category Count'.
This change produces the expected results:
So, the 'Risk Category Count' or 'Risk Percentage Value Sum' fields aren't needed as the Percentage field can calculate the value directly.
----------------------------------------------
I've been working on this for awhile this morning and cannot seem to sort out what the issue is. It's the same problem as noted here: https://answers.laserfiche.com/questions/174570/Divide-calculation-not-populating. When I manually input numbers, e.g. =DIV(0.10/2), I get a result in the field. When I substitute the numbers with the field names that hold those numbers, the field is just blank.
(Edited to add:
I'm translating an Excel table to this form and the column in question is essentially calculating an average percentage based on weight values given to the likelihood of a risk happening and its consequence.
Each Risk Category that's entered by the user will have a weighting of these two factors expressed as a %. The Excel table formula that calculates the average of all risk categories entered sums the rows entered then divides by the number of rows. This is the 'Percent Based on Risk Register' value on the form (which is a Single Line field in this particular iteration).
Risk Register Excel table:
- the formula that calculates the Percentage for each row is highlighted in the bar
- the formula that calculates the overall 22.5% is =IFERRORS(SUM(M10:M18)/COUNT(M10:M18),0)
How I've done this calculation is:
- 'Risk Percentage Value Sum' = the sum of each 'Risk Percent Value'
- =SUM(feasibilityRiskRegister.riskPercentageValue), e.g. the sum of 0.25 and 0.20 in the above screen shot
- 'Risk Category Count' = count of the risk categories entered
- =MAX(feasibilityRiskRegister.rowNumber), e.g. 2
- 'rowNumber' is the name of the 'Risk Category Row' field, and its formula is =ROW()
- 'Overall Risk Percentage' should simply divide the sum by the count
- =feasibilityRiskContingencySummary.riskPercentageValueSum/feasibilityRiskContintencySummary.riskCategoryCount
- e.g. (0.25 + 0.20)/2 = 0.225 or 22.5%
But nothing shows in the 'Overall Risk Percentage' field as soon as I start using variables.
The fields in question are three summary fields situated outside of a collection:
- Risk Category Count
- Risk Percentage Value Sum
- Overall Risk Percentage
(Note that only the overall percentage would be shown to the user; also, I added the Count and Value Sum fields so I could track if their formulas were producing the correct results).
Summary fields:
(note: all three below are Number type fields)
'Overall Risk Percentage' contains the division calculation:
(Note: I also tried this using DIV and had the same issue, i.e. no results in 'Overall Risk Percentage')
When I manually input, say, =DIV(0.10/2), 'Overall Risk Percentage' displays the answer. But as soon as I substitute one or both of the other two fields as variables, 'Overall Risk Percentage' remains empty.
I've confirmed the 'Count' and 'Sum' fields have correct values. But the formula is not populating 'Percentage':
'Risk Category Count' and 'Percentage Value Sum' references a collection within which there are two fields (both of Number type):
- Risk Percentage Value
- Risk Category Row
'Risk Percentage Value' sets a value between 0.05 and 0.25 using an IF(AND(INDEX...) construction to evaluate the options selected by the user with each collection set entered. This formula produces the correct result.
'Risk Category Row' is a simple =ROW() formula to count the number of sets input by the user.
I've confirmed that these two fields are populating correctly for every set entered, and that the 'Count' and 'Value Sum' fields outside the Risk Register collection are also producing the correct values.
I don't know why the variables don't appear to be working for what seems a pretty simple calculation. I'm sure it's just something obvious and simple I've missed. Any help would be appreciated!