How to set a calculated field to null if reference fields are also null.

[Henry.Ong]

Good day!

I have a COST field in my PDF form.

This COST field is a calculated field with the following formula ...

COST = (QTY*MATERIAL)+LABOR+OTHER+SUBCONTRACTOR

What I wish to happen is if QTY, MATERIAL, LABOR, OTHER and SUBCONTRACTOR are null (meaning no value is entered), want my COST field to also be null.

Thanks for the help.

[Eric Dumas]

Hi, You need a bit of script for that. 

Can you confirmed you tested the Calculation tab in the Properties of the Cost field?

[Henry.Ong]

Hi Eric,

If by confirmed, you mean if I have tested the formula of Cost field in the Calculation tab, it is working properly if numeric values are inputed in the reference fields I have stated. Thanks!

[Thom Parker]

What you want is a "blank" value, not a "Null" value. 

Right now, if any of the input values are "blank" then the output is 0, correct?

So, what if all the inputs are 0? do you want the output to be 0 or blank?

This is very important because for the calculation "blank" and zero are the same. The solution is easy if they ar handled in the same way. Things get tricky if you want to handle them differently.

And finally, is the format on this field set? This is also significant. 

[Henry.Ong]

Hi Thom,

To answer your questions:

a) What you want is a "blank" value, not a "Null" value. - I am not sure but this might help. When I open the PDF, the input fields doesn't display anything. So I am not sure if the said fields contain blank or null. 

b) Right now, if any of the input values are "blank" then the output is 0, correct? - The computed field show "0.00"|

c) So, what if all the inputs are 0? do you want the output to be 0 or blank? - If all the input fields are 0, it is ok if the computed field is 0.

d) And finally, is the format on this field set? This is also significant. - Please see below the format of the following input fields:

QTY, MATERIAL, LABOR, OTHER and SUBCONTRACTOR

- QTY - Numeric with no decimal point

- MATERIAL  - Numeric with 2 decimal point

- LABOR - Numeric with 2 decimal point

- Other - Numeric with 2 decimal point

- SUBCONTRACTOR - - Numeric with 2 decimal point

and the cumputed field (COST) is Numeric with 2 decimal point

Allow me to articulate what I want to happen with your permission.

My PDF form have a table. The columns are Description, Qtr, Unit, Material, Labor, Other, Subcontractor which are input fields and lastly the Cost column which is computed

The table is free format, meaning in the Description, they can input title and subtitle. Example:

Phase 1 - Demolition            - Title

- Remove Kitchen Cabinets  - Subtitle

Phase 2 - Construction          - Title

- Range                                  - Subtitle

- Dishwasher                         - Subtitle

- Dishdryer                            - Subtitle

Phase 3 - Cleanup                  - Title

- General Cleaning                 - Subtitle

For Titles, user won't put values on the input columns. It is only in the subtitle that user are expected to enter values on the input columns

But since is is a free format table, I won't know when that row will be used as a just Title or sibhtitle so I still need to defined the formula of the Cost.

Unfortunately, the Cost column which is a computed shows 0.00 even on Title.

What I want is for Title, the Cost cell won't show anything.

I hope this help.

[try67 · Accepted solution]

You can use this code as the custom calculation script of the COST field:

var v1 = this.getField("QTY").valueAsString;
var v2 = this.getField("MATERIAL").valueAsString;
var v3 = this.getField("LABOR").valueAsString;
var v4 = this.getField("OTHER").valueAsString;
var v5 = this.getField("SUBCONTRACTOR").valueAsString;

if (v1=="" && v2=="" && v3=="" && v4=="" && v5=="") event.value = "";
else {
	var QTY = Number(v1);
	var MATERIAL = Number(v2);
	var LABOR = Number(v3);
	var OTHER = Number(v4);
	var SUBCONTRACTOR = Number(v5);
	event.value = (QTY*MATERIAL)+LABOR+OTHER+SUBCONTRACTOR;
}

[Henry.Ong]

It worked! Thanks for this solution.