Calculated Fields - Returning Nulls Instead of Values

Kinetic ERP
1

I am scratching my head on how to correct this. I have a BAQ that I have written that is pulling on hand data from PartBin.OnHand Qty per below. Current Inventory (PartBinNonIQC_OnhandQty) + Quarantine / Inspection (PartBin_OnhandQty). Then I have two calculated fields to show Available Inventory and Remaining Qty:

image
image1136Ă—360 22.2 KB

image
image1554Ă—704 18.6 KB

However you will see in the results below that the calculation will only work correctly IF there is a value in all fields - like the row below:

image
image1812Ă—102 6.42 KB

However the row below that should be returning a value of 1 for Available Inventory and -299 for Remaining Quantity.

I did search and found a topic that suggested it might be a type issue and to try updating the calculated field to below as an example for Available Inventory:

CAST (PartBin.OnhandQty AS DECIMAL(7,2)) + CAST (PartBinNonIQC.OnhandQty AS DECIMAL (7,2))

But alas that didn’t work either

I have spent way to long on what I would have thought to be a simple task so I am turning it over to the Brains Trust.

Much Appreciated
Angie

image
image1828Ă—510 53.8 KB

2

Have a look at the ISNULL() function. Try something like:

ISNULL(PartBin.OnhandQty + PartBinNonIQC.OnhandQty, 0)

3

Also try:

CAST(COALESCE(PartBin.OnhandQty,0) AS DECIMAL(7,2)) + CAST(COALESCE(PartBinNonIQC.OnhandQty, 0) AS DECIMAL (7,2))
4

Hi Angie and welcome to the community !
You cannot make group by NULL as you have noticed already.
What you can do is to move the query to be a subquery, use a calculated field for fields which can be null:

isnull(something,0) returns “something” if it is not null or zero otherwise.

This will solve your null problem. Then use the Subquery as a table in the main query and there you can make group by the calculated field which now no loinger has nulls in it.

Good luck !
Mihai

5

Josh - this was the ticket to success for me. Thanks so much!

1 Like
6

Thanks everyone - Josh’s solution is what worked on this occasion. Very much appreciate the assist!