Question

How to stop hidden values from adding to total calculations?

Forms

Updated November 3, 2021

asked on November 2, 2021

I have several tables that are totaled together for capturing weekly timesheet hours. If the user enters time on one table, then decides they didn't want that table so they uncheck the table option, how can I automatically Clear the Values they previously entered so they will not reflect in the totals.

So in example #1 below, obviously Mon and Tues time entry is a mistake. That can be easily corrected.

Selection boxes: (these are used for routing tasks)

Here is the problem, when I go to the checkbox and remove the Hours Worked (to only show leave). If I don't manually remove the hours it will still adds to my total calculations, how can I tell it to clear the values if Unchecked?

Example #2

0 0

Answer

SELECTED ANSWER

replied on November 3, 2021

Try this:

=IF(LeaveChoice.HoursWorked,SUM(HrsWrk.SUN, HrsWrk.MON, HrsWrk.TUE, HrsWrk.WED, HrsWrk.THU, HrsWrk.FRI, HrsWrk.SAT),0)

1 0

Replies

replied on November 3, 2021

I've run into this before. You can wrap your formula in an IF statement.

=IF(Leave_Type.Hours_Worked,"Your current formula",0)

Leave_type.Hours_worked is the variable for the checkbox. With this it should only sum your data if the box is checked, else the total will be 0. Hope this makes sense.

2 0

View 4 previous replies

replied on November 3, 2021

Thanks! I'll try this and let you know. I appreciate your help greatly!!!

0 0

replied on November 3, 2021

For the variable for the check box, do I enter the "Value" or the actual check box choice listed that they check. I'm getting a validation error so I'm missing something.

0 0

replied on November 3, 2021

Here is my formula:

=IF LeaveChoice.HoursWorked,=SUM(HrsWrk.SUN, HrsWrk.MON, HrsWrk.TUE, HrsWrk.WED, HrsWrk.THU, HrsWrk.FRI, HrsWrk.SAT),0

I'm getting a syntax error. I'm sure there are more ()'s that are needed ? I filled in the Leave choice with the variable > button to see what it added and this is what shows in the value for that selection. "HoursWorked"

0 0

SELECTED ANSWER

replied on November 3, 2021

Try this:

=IF(LeaveChoice.HoursWorked,SUM(HrsWrk.SUN, HrsWrk.MON, HrsWrk.TUE, HrsWrk.WED, HrsWrk.THU, HrsWrk.FRI, HrsWrk.SAT),0)

1 0

replied on November 3, 2021

That worked!!! Thanks! Now I will do the other formulas.

In the IF Statement can I have an OR for multiple checked items?

0 0

replied on November 3, 2021

You can. These formulas work very similarly to Excel formulas. The formatting is just a little different. This reference is helpful:

https://doc.laserfiche.com/laserfiche.documentation/english/docs/Subsystems/ProcessAutomation/Content/Forms-Current/Formulas.htm

1 0

replied on November 3, 2021

Thanks again for all your help!

Robin

0 0

replied on November 2, 2021

I'm assuming you're using Field Rules to hide the table when the box is unchecked. For the rule you need to choose "Ignore the data when the field/section/page is hidden." This won't clear the table, but it should ignore the data used in the calculations.

0 0

replied on November 2, 2021

Steve,

Yes, that is selected within the field rules, and I thought that too. But it is still adding to the totals. The totals are separate fields and not part of the Table.

thanks!

0 0

replied on November 2, 2021

Are you using a formula on the "Advanced" tab of the "Total Hours Worked" field to sum the table, or using Javascript?

0 0

replied on November 3, 2021

I'm using formulas on the Advanced tab.

0 0

You are not allowed to follow up in this post.

Question

Question

How to stop hidden values from adding to total calculations?

Answer

Replies

Sign in to reply to this post.