Creating automatic calculation off of a "Totals" field, or having the field be a sum of manual inputs.

[Anonymous]

Hi all,

I'm trying to build where a list of tasks has their "hours of effort" calculated automatically based on the input of a "total effort field". At the same time, I also want the user to have the option of manually inputting the "hours of effort" for each task and having the "total effort" field act as a Sum field.

The issue I'm running into is the "This rule creates a circular reference between properties which is not allowed" error if I try to have calculations and Sums through variables and If functions. Otherwise some OnSelect variable updating would have been the trick. My current best idea is having the sum being some global variable, which is then displayed in the total field if any of the other fields have been selected. 

1. Is this the best way to go about what I want, or is there some way simpler?

2. If it is the way to go, can I then make the SumVariable update automatically? I really want to make this function as smoothly as possible, and avoiding to have a "Calculate manual total" button would be greatly appreciated.

[CNT]

@Anonymous Could a share a bit more of u'r screen (not just the corner!) and some existing formulas.

[Anonymous]

Yeah, of course, sorry. I just feared the full thing would cause more confusion.

Currently all the nine sub-fields are calculating from the input to the fourth field (Dev Est Input) and the total field is a simple sum Sum(EstInput1+EstInput2+EstInput3+'Dev Est Input'+EstInput5+EstInput6+EstInput7+EstInput8+EstInput9).

The other fields have the formula Round(Value('Dev Est Input'.Text)*0.6,1) with the corresponding ratio to the Dev Est Input field.

The goal would be having the 9 fields calculate from the total field, while they can also receive manual input, in which case the total field just becomes a sum field.

[Anonymous]

And the Onselect logic I tried to run was having the Total field have the Default= If(ManualEstimate=true,Sum(Est2.Text+Est3.Text),"")

and the other fields having OnSelect= UpdateContext({ManualEstimate:true}) with a Default value of If(ManualEstimate=false,Round(Value(Est1tot.Text)*0.6,1),""). This gives the error "This rule creates a circular reference between properties which is not allowed".

[CNT]

@Anonymous To avoid circular reference, we need to separate the display and input fields for the values. I've done a test with just 2 values,

The formula for the Total is,

Text(If(!IsBlank(Est1.Text),Value(Est1.Text)) + If(!IsBlank(Est1.Text),Value(Est2.Text)))

Hope this helps!

[Anonymous]

I'm not fully sure what you mean. Especially what the "3.6" points to?

If I build

Where Total = 

Text(If(!IsBlank(Est1Input.Text),Value(Est1Input.Text)) + If(!IsBlank(Est2Input.Text),Value(Est2Input.Text)))

 And the Est Input fields are =

Round(Value(TotalInput.Text)*0.6,1)

Then I still get a circular reference error. I also tried with having a Text field besides, mirroring the Input fields, and using that to have a Sum, but that also resulted in a circular reference error.

[CNT · Accepted solution]

@Anonymous You cannot display the results and take input in the same field as you will hit circular reference. That's why I am displaying the value as part of the Label. 3.6 refers to the calculated value.

Formula in Total Text Field

Text(If(!IsBlank(Est1.Text),Value(Est1.Text)) + If(!IsBlank(Est1.Text),Value(Est2.Text)))

Formula in the Label (here Est1)

"Est1: " & Round(Value(EstTotal.Text)*0.6,1)

[Anonymous]

Right, thanks a bunch! I ended up figuring out a pretty smooth, but overengineered, solution with using the HintText instead of a separate label. Here are screenshots of "Automatic calculation mode" first and "manual input mode" second. The labels to the right mirror the text in the input fields, and is used for adding to a collection.

                            

The Total input has this:

Default: 

If(ManualEst=true && (!IsBlank(Est1Input.Text) || !IsBlank(Est2Input.Text)),Sum(Est1Input.Text+Est2Input.Text),"")

OnSelect:

UpdateContext({ManualEst:false});Set(ResetEstFields, true); Set(ResetEstFields, false);

The Est Input fields have

HintText: 

If(ManualEst=false && !IsBlank(TotalInput),Round(Value((TotalInput.Text)/3.85)*0.6,1),"")

Default:

""

OnSelect:

UpdateContext({ManualEst:true})

Reset:

ResetEstFields

Lastly, the labels on the side have, Text:

If(ManualEst=true,Est1Input.Text,Est1Input.HintText)

Thanks a bunch for the help! I've marked your answer as the Solution, as it was the core issue of my problem.

[CNT]

@Anonymous Good job!