Display items where length, width, or height is 0 in UOM tbl

johnsogj

Hello, I'm trying to create a report displays all items where on hand quantity is greater than 0 (in the item card) AND where either length, width, or height in the UOM table is "0". I'm having trouble. can anyone help me with this code?

thanks

MBerger

Only way i'd see to do this ( next to going through all records and MARK-ing them, which is WAY slow ) is to add a field with length+height+width ( and the appropriate code to fill it, of course ). Then filtering that field on <> 0 would work

DenSter

You can't filter the Item table for this purpose, and you also don't have to add any fields. Create a report off of the Item table, and write some code in the OnAfterGetRecord table to loop through the Item UOM records related to the Item, and do CurrReport.SKIP if none of those fields is 0.

I don't know whether filtering the Inventory field is faster than calculating inventory in the same trigger, you'd have to try that out.

johnsogj

something like this? It isnt working.... any ideas?

UOMTable.SETCURRENTKEY("Item No.", "Length", "Width", "Height");
UOMTable.SETRANGE("Item No.",Item."No.");
IF "length">0 THEN
CurrReport.SKIP
ELSE BEGIN
length := UOMTable.Length
END;;

DaveT

Hi,

Create your report off the item table. In the on aftergetrecord trigger put

if ItemUOM.get( "No.", "Base unit of measure code" ) then // Assuming you want to test the base UOM
  if ( itemUOM.Length <> 0 ) and ( itemUOM.Width <> 0 ) and ( itemUOM.Height <> 0 ) then
     currreport.skip;

Of Course set the filter for the item.inventory > 0

DenSter

In OnAfterGetRecord of an Item dataitem, something like this:

Item.CALCFIELD(Inventory);
IF Item.Inventory = 0 THEN
  CurrReport.SKIP;

ItemUOM.RESET;
ItemUOM.SETRANGE("Item No.",Item."No.")
IF ItemUOM.FINDSET(FALSE,FALSE) THEN BEGIN
  FoundOne := FALSE; //FoundOne is a boolean variable
  REPEAT
    IF (ItemUOM.Length = 0) OR
       (ItemUOM.Width = 0) OR
       (ItemUOM.Height = 0)
    THEN
      FoundOne := TRUE;
  UNTIL (ItemUOM.NEXT = 0) OR (FoundOne = TRUE);
  IF NOT FoundOne THEN
    CurrReport.SKIP;
END ELSE BEGIN
  CurrReport.SKIP;
END;

I haven't tried any of this, so I might have my TRUE and FALSE mixed up

johnsogj

wow! that was perfect. I had to make a few syntax tweaks, but it worked. I will surely be able to model it in future reports. thanks for your help.

DenSter

Cool you're welcome I'm glad you were able to make that work