You are viewing limited content. For full access, please sign in.

Question

Question

Forms - Calculation for removing values

Forms
Updated January 23, 2020
asked on January 14, 2020

I need a some guidance on a formula to remove values maybe added by mistake. I have Field Rules to hide Tables until a box is checked. Once values are entered, they stay regardless of unchecked the box.  

The following formula is where I'm trying to place a zero in the Verbal box under Disorderly Conduct if I was to uncheck the above box.

=IF(Description_of_Contact_1.Disorderly_Conduct = "FALSE",(0))

Any help is appreciated.

 

0 0

Answer

SELECTED ANSWER
replied on January 23, 2020 • Show version history

What you need to do is remove the ="Checked" and it will work.

IF(Description_of_Contact_1.Disorderly_Conduct,1,0)

 

So what you want ultimately is:

=SUM((IF(Description_of_Contact_1.Verbal_Altercation,Verbal_Altercation.Verbal,0)),
(IF(Description_of_Contact_1.Weapons,Weapons.Verbal,0)),
(IF(Description_of_Contact_1.Physical_Altercation,Physical_Altercation.Verbal,0)),
(IF(Description_of_Contact_1.Parking,Parking.Verbal,0)),
(IF(Description_of_Contact_1.Drugs,Drugs.Verbal,0)),
(IF(Description_of_Contact_1.Disorderly_Conduct,Disorderly_Conduct.Verbal,0)),
(IF(Description_of_Contact_1.Assault,Assault.Verbal,0)),
(IF(Description_of_Contact_1.APC,APC.Verbal,0)),
(IF(Description_of_Contact_1.Alcohol,Alcohol.Verbal,0)),
(IF(Description_of_Contact_1.Other,Other.Verbal,0)),
(IF(Description_of_Contact_1.Theft,Theft.Verbal,0)))

 

1 0

Replies

replied on January 14, 2020

Have you considered changing the field rule setting to Ignore values when the field is hidden? That way, if all the checkboxes are deselected and the table is hidden, it will return blanks. 

6.5.0
0 0
View 4 previous replies
replied on January 15, 2020

Yeah it defaults when creating the rules.  Apparently, it's not working the way I think it should.

 

0 0
replied on January 15, 2020

For the target section of your rule (Show ______ and ignore data...), can you include the fields as well? 

Show Disorderly Conduct and ...
Show Value and Ignore...
Show Citation and Ignore...
 

If you specify the section and fields that are inside it, the ignore piece may work better. 

0 0
replied on January 16, 2020

Ok, i add those fields in the target area.  Still does not ignore the data.

 

 

0 0
replied on January 16, 2020

Ah, yes, this is a bit more complicated since it's being used in a calculation. If you look at the value of Verbal in the monitor page on your submitted forms, it should be blank if the checkbox is deselected. However, that doesn't help you for calculations which still include the hidden value. 

You should look at the solution Rui provides here:https://answers.laserfiche.com/questions/135143/Have-calculation-ignore-hidden-fields#155152

Your calculation will need to include the sumif formula to only include the sum of Verbal if the checkbox Disorderly Conduct is checked. You could also use basic if statements like below. Take the sum of, if disorderly conduct is checked, include the value of disorderly conduct verbal, otherwise use 0 and so on. 

sum(

if( DisorderlyConduct="Checked", DisorderlyConduct.Verbal, 0), if(Alchohol="Checked", Alcohol.Verbal, 0),
...)

0 0
replied on January 16, 2020

This is the formula with your help.  Now it's not producing the correct value in Sum of Verbals field.

 

=SUM(IF(Description_of_Contact_1.Disorderly_Conduct = "Checked",Disorderly_Conduct.Verbal,0),
IF(Description_of_Contact_1.Alcohol = "Checked",Alcohol.Verbal,0))

 

0 0
replied on January 16, 2020

Can you check it in pieces? Add a Sub-total field and set it to 

IF(Description_of_Contact_1.Disorderly_Conduct = "Checked",1,0)

See if you can get the checkbox to toggle that value from 1 to 0. 

Then add the value part

IF(Description_of_Contact_1.Disorderly_Conduct = "Checked",Disorderly_Conduct.Verbal,0)

See if the checkbox will toggle from some input number and 0. 

Then go for the full thing. 

0 0
SELECTED ANSWER
replied on January 23, 2020 • Show version history

What you need to do is remove the ="Checked" and it will work.

IF(Description_of_Contact_1.Disorderly_Conduct,1,0)

 

So what you want ultimately is:

=SUM((IF(Description_of_Contact_1.Verbal_Altercation,Verbal_Altercation.Verbal,0)),
(IF(Description_of_Contact_1.Weapons,Weapons.Verbal,0)),
(IF(Description_of_Contact_1.Physical_Altercation,Physical_Altercation.Verbal,0)),
(IF(Description_of_Contact_1.Parking,Parking.Verbal,0)),
(IF(Description_of_Contact_1.Drugs,Drugs.Verbal,0)),
(IF(Description_of_Contact_1.Disorderly_Conduct,Disorderly_Conduct.Verbal,0)),
(IF(Description_of_Contact_1.Assault,Assault.Verbal,0)),
(IF(Description_of_Contact_1.APC,APC.Verbal,0)),
(IF(Description_of_Contact_1.Alcohol,Alcohol.Verbal,0)),
(IF(Description_of_Contact_1.Other,Other.Verbal,0)),
(IF(Description_of_Contact_1.Theft,Theft.Verbal,0)))

 

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...