Forms - OpenFormula - Laserfiche Answers

asked on October 14, 2016 • Show version history


=IF(

AND(Salary_Calculation="Y",SUM(salary_tbl10.Actual_Salary_New_Value)=0,SUM(salary_tbl08.Checks_Remaining_New_Value)=0),

SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining),

IF(

AND(Salary_Calculation="Y",salary_tbl10.Actual_Salary_Change_Value.Yes,SUM(salary_tbl10.Actual_Salary_New_Value)>0,SUM(salary_tbl08.Checks_Remaining_New_Value)=0),

SUM(salary_tbl10.Actual_Salary_New_Value)/SUM(salary_tbl08.Checks_Remaining),

IF(

AND(Salary_Calculation="Y",salary_tbl08.Checks_Remaining_Change_Value.Yes,SUM(salary_tbl10.Actual_Salary_New_Value)=0,SUM(salary_tbl08.Checks_Remaining_New_Value)>0),

SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining_New_Value),

IF(

AND(Salary_Calculation="Y",salary_tbl08.Checks_Remaining_Change_Value.Yes,salary_tbl10.Actual_Salary_Change_Value.Yes,SUM(salary_tbl10.Actual_Salary_New_Value)>0,SUM(salary_tbl08.Checks_Remaining_New_Value)>0),

SUM(salary_tbl10.Actual_Salary_New_Value)/SUM(salary_tbl08.Checks_Remaining_New_Value),


SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining)))))

I am running the following script. However, I need assistance with finding out how to run a condition for when a checkbox is not set. The checkbox only has one value. I would want to run a calculation when the value is unchecked.

I am hoping I do not have to switch my fields to radio buttons as that is the only possible solution that may resolve this.

Also, I am unable to get IFS() function to work.

0 0

SELECTED ANSWER

replied on October 14, 2016 • Show version history

Hi Mason,

To answer your last question first, IFS is not currently a supported function; actually, it is not even in the OpenFormula specifications; however, since it is supported for Office products, it can be considered as a feature request for a future release.

With respect to using checkbox types with the IF function, you don't need to check its value against anything, just use the "variable name" for the choice as your condition. So for example with the configuration of the Checkbox field in the attached image and the calculation

=IF(checkbox.a,1,0)

you can set up this demonstration.

In general the "variable name" for the checkbox will be the value provided, but you can use the built-in dropdown on the formula editor to select the correct one based on the value.

Hope this helps!

01-checkbox.png (25.83 KB)

| Download

1 0

replied on October 17, 2016

I was able to fix it.

Here is my updated script.

Thanks James.

=IF(Salary_Calculation="Y",

IF(AND(SUM(salary_tbl08.Checks_Remaining_New_Value)=0,SUM(salary_tbl10.Actual_Salary_New_Value)=0),
SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(salary_tbl10.Actual_Salary_Change_Value.Yes,salary_tbl08.Checks_Remaining_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)=0),SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining_New_Value),

IF(AND(salary_tbl08.Checks_Remaining_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)=0),SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining_New_Value),

IF(AND(salary_tbl08.Checks_Remaining_Change_Value.Yes,salary_tbl10.Actual_Salary_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)=0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),SUM(salary_tbl10.Actual_Salary_New_Value)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(salary_tbl10.Actual_Salary_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)=0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),SUM(salary_tbl10.Actual_Salary_New_Value)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(salary_tbl08.Checks_Remaining_Change_Value.Yes,salary_tbl10.Actual_Salary_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),SUM(salary_tbl10.Actual_Salary_New_Value)/SUM(salary_tbl08.Checks_Remaining_New_Value),

IF(AND(salary_tbl08.Checks_Remaining_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining_New_Value),

IF(AND(salary_tbl10.Actual_Salary_Change_Value.Yes,SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),SUM(salary_tbl10.Actual_Salary_New_Value)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)=0),
SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(SUM(salary_tbl08.Checks_Remaining_New_Value)=0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),
SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining),

IF(AND(SUM(salary_tbl08.Checks_Remaining_New_Value)>0,SUM(salary_tbl10.Actual_Salary_New_Value)>0),
SUM(salary_tbl10.Actual_Salary)/SUM(salary_tbl08.Checks_Remaining),
0))))))))))))

,0)

0 0

Question

Question

Forms - OpenFormula

Answer

Replies

Sign in to reply to this post.