Suppress the printing of items with 0.00 Qty 2

[blueboyz]

I am designing a report that lists inventory items and the
quantity on hand
quantity on purchase orders
quantity on sales orders
quantity not committed


Is there a way to suppress the details if all 4 quantities = 0.00?

The object is not to print an item that has 0.00 quantity on hand and 0.00 quantity on purchase orders and 0.00 quantity on sales orders and 0.00 quantity not committed.

If there is some activity for an inventory item, then we want to see it on the report. It's just when there's no activity that it's not necessary to list the item with 0.00's across the page.

 

[synapsevampire]

The best way would be to eliminate them from the recordset returned by using Report->Edit Selection Formula->Record witth something like:

{table.quantity on hand} = 0
and
{table.quantity on purchase orders} = 0
and
{table.quantity on sales orders} = 0
and
{table.quantity not committed} = 0

The alternative would be to right click the details section, select format section and in the X 2 next to suppress use a similar formula as above.

-k

 

[synapsevampire]

Sorry, that should have been:

(
{table.quantity on hand} <> 0
and
{table.quantity on purchase orders} <> 0
and
{table.quantity on sales orders} <> 0
and
{table.quantity not committed} <> 0
)


-k

 

[blueboyz]

When I put:

{@In Stock} <> 0.00 and
{@Not Committed} <> 0.00 and
{@On Purchase Orders} <> 0.00 and
{@On Sales Orders} <> 0.00

in the record selection, only 13 records are selected.

It shows records that have a value in each of the fields, for example:

Item A
In Stock 4.00
Not Committed 3.00
On Purchase Orders 1.00
On Sales Orders 1.00

if Item B had
In Stock 5.00
Not Committed 0.00
On Purchase Orders 0.00
On Sales Orders 2.00
the report would now list Item B at all.

If I use "or" instead of "and", I get 1,765 records, whereas before I added the selection criteria suggested above, I got 79 records.

My record selection looks like this:
{LineItem.ItemID} <> " " and
{@In Stock} <> 0.00 and
{@Not Committed} <> 0.00 and
{@On Purchase Orders} <> 0.00 and
{@On Sales Orders} <> 0.00 and
{JrnlHdr.JrnlKey_Journal} in [10, 11]

Is there another formula I can use?

 

[synapsevampire]

Ahhh, sorry, I should have used:

{table.quantity on hand} +
{table.quantity on purchase orders}+{table.quantity on sales orders} +
+
{table.quantity not committed} <> 0

Now there are other factors here, such as the possibility of negative quntities on hand, and whether your database might have nulls.

I've been out of Crystal for a bit, but this looks OK now.

If there are nulls, you can use the File->Report Options and select convert nulls to default to get around this.

-k

 

[blueboyz]

I changed the record selection to:

{LineItem.ItemID} <> " " and
({@In Stock} <> 0.00 + {@Not Committed} <> 0.00 +{@On Purchase Orders} <> 0.00 +{@On Sales Orders} <> 0.00) and
{JrnlHdr.JrnlKey_Journal} in [10, 11]

but I get an error message that the )is missing. It puts the cursor at the right side of "<>" of {Not Committed.

 

[lbass]

I believe SV meant:

{LineItem.ItemID} <> " " and
(
{@In Stock} + {@Not Committed} +{@On Purchase Orders} +{@On Sales Orders}
) <> 0.00 and
{JrnlHdr.JrnlKey_Journal} in [10, 11]

-LB

 

[synapsevampire]

I didn't rewrite the whole record selection, but LB did.

The point is to look for those that don't add up to 0.

Again nulls and negative numbers will throw this off, in which case you'll have to create a lengthy formula with checks for it all.

-k

 

[blueboyz]

That did the trick:

{LineItem.ItemID} <> " " and
({@In Stock} + {@Not Committed} + {@On Purchase Orders} +
{@On Sales Orders}) <> 0.00 and
{JrnlHdr.JrnlKey_Journal} in [10, 11]

Thank you so much. You saved the day.