Divide by zero in Explore

Hi All,

   I want to setup a calculated field in explore that takes a package quantity UDF and divides the on-hand quantity by that to show the number of cases they have on hand.  The hitch is that not all of the items have a pack size and some items have zero on hand.   This is easy in Crystal but I can't figure out how to make is display zero if the on-hand is zero.    

  Anyone have any ideas on this?

Parents
  • 0

    In the expression builder, there are some functions listed.  one of the is IFNULL.  Use it like this:  QuantityOnHand / IFNULL(UDF_PackageQty,1).  (Of course, use the real field names for QOH and your UDF).  If the UDF field is null, it will replace it with a 1.  If the QOH is zero, the calculation will be 0/1 = 0.  

  • 0 in reply to hyanaga

    {fn IFNULL("IM_ItemWarehouse"."QuantityOnHand", 1)}/{fn IFNULL("CI_Item"."UDF_PACK_QTY", 1)}

    This still results in a "divide by zero" error.    I think the problem is that Explore sees zero as zero & not null.   Unless it has some sort of a "standard" IF statement that I am SOL...

Reply
  • 0 in reply to hyanaga

    {fn IFNULL("IM_ItemWarehouse"."QuantityOnHand", 1)}/{fn IFNULL("CI_Item"."UDF_PACK_QTY", 1)}

    This still results in a "divide by zero" error.    I think the problem is that Explore sees zero as zero & not null.   Unless it has some sort of a "standard" IF statement that I am SOL...

Children