Report for Sales Lines Invoiced at 0 (zero) quantity

emulsified · 2008-01-02

Kind of like a DAILY 0 QTY NOT SHIPPED Report

I'm wondering what would be the most effecient way to create a report that will give me ITEMS grouped by ITEM."No." that were on Posted Invoices with a 0 (zero) or blank quantity field.

I guess I could just create a report based on Sales Invoice Line table and set the following manually each time:

Document Type: Invoice
Type: Item
Qty To Ship: < 0
Date Filter: 12/30/07..12/31/07

Group Footer or Totals or something on ITEM so that the number only shows up once instead of each time it occurs in all documents.

Any suggestions?

Savatage · 2008-01-02

emulsified wrote:

Qty To Ship: < 0

= 0

Belias · 2008-01-03

look this...
http://www.mibuso.com/forum/viewtopic.php?t=22963
the problem is the same: why we don't have something like distinct in nav??

emulsified · 2008-01-03

Here is the report I have:

All I want to do is have each ITEM# only show up 1 time on the report even if it occurs on 30 invoices as 0 quantity. I can't remember how to make it do that.

Here is my current report CODE:

OBJECT Report 50049 Items Invoiced at ZERO
{
  OBJECT-PROPERTIES
  {
    Date=01/03/08;
    Time=[ 5:42:55 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table113;
        DataItemTableView=SORTING(Posting Date,Shortcut Dimension 1 Code,Shortcut Dimension 2 Code);
        OnPreDataItem=BEGIN
                        LastFieldNo := FIELDNO("Posting Date");
                      END;

        ReqFilterFields=Posting Date,Type,Quantity;
        GroupTotalFields=No.;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=1692;
          }
          CONTROLS
          {
            { 1000000001;Label  ;0    ;0    ;7500 ;423  ;FontSize=8;
                                                         FontBold=Yes;
                                                         CaptionML=[ENU=Items Invoiced at ZERO Quantity;
                                                                    ESM=Hist¢rico l¡n. factura venta;
                                                                    FRC=Ligne facture vente;
                                                                    ENC=Sales Invoice Line] }
            { 1000000002;TextBox;15000;0    ;3150 ;423  ;HorzAlign=Right;
                                                         SourceExpr=FORMAT(TODAY,0,4) }
            { 1000000003;TextBox;0    ;423  ;7500 ;423  ;SourceExpr=COMPANYNAME }
            { 1000000004;TextBox;17700;423  ;450  ;423  ;CaptionML=ENU=Page;
                                                         SourceExpr=CurrReport.PAGENO }
            { 1000000005;Label  ;16950;423  ;750  ;423  ;ParentControl=1000000004 }
            { 1000000006;TextBox;15900;846  ;2250 ;423  ;HorzAlign=Right;
                                                         SourceExpr=USERID }
          }
           }
        { PROPERTIES
          {
            SectionType=Header;
            PrintOnEveryPage=Yes;
            SectionWidth=18150;
            SectionHeight=423;
          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT := FooterPrinted;
                           FooterPrinted := FALSE;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = "Sales Invoice Line".FIELDNO("Posting Date");
                         END;

          }
          CONTROLS
          {
            { 1000000008;TextBox;3150 ;0    ;1200 ;423  ;HorzAlign=Left;
                                                         SourceExpr="Posting Date" }
            { 1000000009;Label  ;0    ;0    ;3000 ;423  ;ParentControl=1000000008 }
          }
           }
        { PROPERTIES
          {
            SectionType=GroupHeader;
            SectionWidth=18150;
            SectionHeight=423;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT :=
                             CurrReport.TOTALSCAUSEDBY = LastFieldNo;
                         END;

          }
          CONTROLS
          {
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=18150;
            SectionHeight=846;
          }
          CONTROLS
          {
            { 1000000011;TextBox;2100 ;0    ;4500 ;423  ;HorzAlign=Left;
                                                         SourceExpr=Description }
            { 1000000013;TextBox;450  ;0    ;1500 ;423  ;HorzAlign=Left;
                                                         SourceExpr="No." }
            { 1000000014;TextBox;0    ;0    ;300  ;423  ;HorzAlign=Left;
                                                         SourceExpr=Type }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9020;
      Height=3410;
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      LastFieldNo@1000000000 : Integer;
      FooterPrinted@1000000001 : Boolean;

    BEGIN
    END.
  }
}

If someone can remind me I would appreciate it. I know it is something very simple.

Savatage · 2008-01-03

Can it be done when there is not "Item No" key?

Belias · 2008-01-04

I meant to fill a temp table and then retrieve item nos from there...
follow the link I posted before, take a look to the code wrote by ara3n to fill the temp table and then you can loop into the temptable to obtain only one occurence per article...
Hope it's clear,

Report for Sales Lines Invoiced at 0 (zero) quantity

Comments

Categories