# Discussions

View Only

## How do I get a Formula Numeric filed to display null not 0? #### Andrea Pahor26 days ago #### Don Larson25 days ago #### Andrea Pahor25 days ago #### Don Larson25 days ago • #### 1.  How do I get a Formula Numeric filed to display null not 0?

Posted 26 days ago
Edited by Andrea Pahor 26 days ago
I need a formula numeric field (called No of Vehicles) to return null and not 0 when a condition is met:

If ([No of Techs/Vehicle] = null, null)

The full formula for this field (No of Vehicles) is:

If ([No of Techs/Vehicle] = null, null,
If ([Vehicle per technician] = "Yes", ToNumber([No of Technicians]),
If ([Vehicle per crew] = "Yes", ToNumber([No of Crew])
))

Note:
No of Techs/Vehicle is a formula numeric field
Vehicle per technician is a text - multiple choice field
Vehicle per crew
is a text - multiple choice field

Thanks in advance for any help!

------------------------------
Andrea Pahor
------------------------------

• #### 2.  RE: How do I get a Formula Numeric filed to display null not 0?

Posted 25 days ago
Andrea,

Try this:

// Calculate the Vehicles Per Tech
var number VehiclesPerTech =If ([Vehicle per technician] = "Yes", ToNumber([No of Technicians]);
// Calculate the Vehicles Per Crew
var number VehiclesPerCrew = If ([Vehicle per crew] = "Yes", ToNumber([No of Crew]);
// Calculate the Techs Per Vehicle, Account for the case where the denominator is zero
var number TechPerVehicle =  if( \$VehiclesPerCrew=0, 0, \$VehiclesPerTech/\$VehiclesPerCrew);
// Control the UI to show a Null Value of the answer is Zero
if ( \$TechPerVehicle=0,null, \$TechPerVehicle)

------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------

• #### 3.  RE: How do I get a Formula Numeric filed to display null not 0?

Posted 25 days ago
Thanks, Don - much appreciated.

I omitted some key information:

No of Technicians / No of Crew can be unspecified (TBD), in which case the formula should return null.

For example, I want a result of null if Vehicle per technician = "Yes" and No of Technicians = "TBD".

How would I build this into your suggestion?

------------------------------
Andrea Pahor
------------------------------

• #### 4.  RE: How do I get a Formula Numeric filed to display null not 0?

Posted 25 days ago
Andrea,

Updated for that business case and fixed the 1st If Statement.

// Calculate the Vehicles Per Tech.  Gives us the Number of Technicians or Zero
var number VehiclesPerTech =If ([Vehicle per technician] = "Yes", ToNumber([No of Technicians],0);

// Calculate the Vehicles Per Crew, Account for TBD in the Number of Technicians.  Gives us the Number of Technicians or Zero
var number VehiclesPerCrew = If ([Vehicle per crew] = "Yes" and [No of Technicians]<>"TBD" , ToNumber([No of Crew],0);

// Calculate the Techs Per Vehicle, Account for the case where the denominator is zero
var number TechPerVehicle =  if( \$VehiclesPerCrew=0, 0, \$VehiclesPerTech/\$VehiclesPerCrew);

// Control the UI to show a Null Value of the answer is Zero.  This is the objective of the formula.
if ( \$TechPerVehicle=0,null, \$TechPerVehicle)

------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------

• #### 5.  RE: How do I get a Formula Numeric filed to display null not 0?

Posted 22 days ago
Thank you, Don.

------------------------------
Andrea Pahor
------------------------------