Discussions

Expand all | Collapse all

How do I get a Formula Numeric filed to display null not 0?

  • 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
    ------------------------------