mibuso.com
Home› NAV/Navision Classic Client
Options

Label Report filtered by if a customer bought certain Items

emulsifiedemulsified Member Posts: 139
edited 2009-02-16 in NAV/Navision Classic Client
](*,) I need to change this label report so that I can filter by only customers who purchased ITEMS with a specific ITEM CATEGORY CODE.

:-k I'm thinking I can just add a Data Item like Sales Line and possibly link the dataitems together through their properties. I don't think that will work though because I only want 1 label for each customer that purchased an ITEM with Item Category Code no matter how many items with Item Category Code they purchased.

This will be outputting labels for the purpose of sending catalogs to our customers who purchased a specific "Item Category Code" which is available through the Sales Line table.

:?: Is it better to loop through the Sales Line table first using SETFILTER on Item Category Code? I would then have all customers who purchased Items with Item Category Code but not a single instance of each as I need. What do I do?

[-o< If someone can help me I would be really appreciate it. I need to get my catalogs out ASAP.
OBJECT Report 50007 Customer - Labels (Avery)
{
  OBJECT-PROPERTIES
  {
    Date=08/10/07;
    Time=[ 1:31:46 PM];
    Modified=Yes;
    Version List=NAVW13.00;
  }
  PROPERTIES
  {
    CaptionML=[ENU=Customer - Labels;
               ESM=Cliente - Etiquetas;
               FRC=Client - tiquettes;
               ENC=Customer - Labels];
    TopMargin=2000;
    BottomMargin=250;
    LeftMargin=1000;
    Orientation=Portrait;
    PaperSize=Letter 8 1/2 x 11 in;
  }
  DATAITEMS
  {
    { PROPERTIES
      {
        DataItemTable=Table18;
        OnPreDataItem=BEGIN
                        CASE LabelFormat OF
                          LabelFormat::"36 x 70 mm (3 columns)",LabelFormat::"37 x 70 mm (3 columns)":
                            NoOfColumns := 3;
                          LabelFormat::"36 x 105 mm (2 columns)",LabelFormat::"37 x 105 mm (2 columns)":
                            NoOfColumns := 2;
                        END;
                        NoOfRecords := COUNT;
                      END;

        OnAfterGetRecord=BEGIN
                           RecordNo := RecordNo + 1;
                           ColumnNo := ColumnNo + 1;
                           FormatAddr.Customer(CustAddr[ColumnNo],Customer);
                           IF RecordNo = NoOfRecords THEN BEGIN
                             FOR i := ColumnNo + 1 TO NoOfColumns DO
                               CLEAR(CustAddr[i]);
                             ColumnNo := 0;
                           END ELSE BEGIN
                             IF ColumnNo = NoOfColumns THEN
                               ColumnNo := 0;
                           END;
                         END;

        ReqFilterFields=No.,Name,Name 2,Customer Price Group;
      }
      SECTIONS
      {
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20600;
            SectionHeight=2538;
            KeepWithNext=No;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((ColumnNo = 0) AND (LabelFormat = LabelFormat::"36 x 70 mm (3 columns)"));
                         END;

          }
          CONTROLS
          {
            { 1   ;TextBox      ;0    ;0    ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][1] }
            { 2   ;TextBox      ;0    ;423  ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][2] }
            { 3   ;TextBox      ;0    ;846  ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][3] }
            { 4   ;TextBox      ;0    ;1269 ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][4] }
            { 5   ;TextBox      ;0    ;1692 ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][5] }
            { 7   ;TextBox      ;7000 ;0    ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][1] }
            { 8   ;TextBox      ;7000 ;423  ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][2] }
            { 9   ;TextBox      ;7000 ;846  ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][3] }
            { 10  ;TextBox      ;7050 ;1269 ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][4] }
            { 11  ;TextBox      ;7000 ;1692 ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][5] }
            { 13  ;TextBox      ;13950;0    ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][1] }
            { 14  ;TextBox      ;13950;423  ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][2] }
            { 15  ;TextBox      ;13950;846  ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][3] }
            { 16  ;TextBox      ;13950;1269 ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][4] }
            { 17  ;TextBox      ;13950;1692 ;6500 ;423  ;FontName=Arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][5] }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20600;
            SectionHeight=2538;
            KeepWithNext=No;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((ColumnNo = 0) AND (LabelFormat = LabelFormat::"37 x 70 mm (3 columns)"));
                         END;

          }
          CONTROLS
          {
            { 19  ;TextBox      ;0    ;0    ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][1] }
            { 20  ;TextBox      ;0    ;423  ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][2] }
            { 21  ;TextBox      ;0    ;846  ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][3] }
            { 22  ;TextBox      ;0    ;1269 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][4] }
            { 23  ;TextBox      ;0    ;1692 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][5] }
            { 24  ;TextBox      ;0    ;2115 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[1][6] }
            { 25  ;TextBox      ;7000 ;0    ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][1] }
            { 26  ;TextBox      ;7000 ;423  ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][2] }
            { 27  ;TextBox      ;7000 ;846  ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][3] }
            { 28  ;TextBox      ;7000 ;1269 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][4] }
            { 29  ;TextBox      ;7000 ;1692 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][5] }
            { 30  ;TextBox      ;7000 ;2115 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[2][6] }
            { 31  ;TextBox      ;14000;0    ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][1] }
            { 32  ;TextBox      ;14000;423  ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][2] }
            { 33  ;TextBox      ;14000;846  ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][3] }
            { 34  ;TextBox      ;14000;1269 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][4] }
            { 35  ;TextBox      ;13950;1692 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][5] }
            { 36  ;TextBox      ;14000;2115 ;6500 ;423  ;FontName=arial;
                                                         FontSize=10;
                                                         SourceExpr=CustAddr[3][6] }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20600;
            SectionHeight=2538;
            KeepWithNext=No;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((ColumnNo = 0) AND (LabelFormat = LabelFormat::"36 x 105 mm (2 columns)"));
                         END;

          }
          CONTROLS
          {
            { 37  ;TextBox      ;0    ;0    ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][1] }
            { 38  ;TextBox      ;0    ;423  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][2] }
            { 39  ;TextBox      ;0    ;846  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][3] }
            { 40  ;TextBox      ;0    ;1269 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][4] }
            { 41  ;TextBox      ;0    ;1692 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][5] }
            { 43  ;TextBox      ;10500;0    ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][1] }
            { 44  ;TextBox      ;10500;423  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][2] }
            { 45  ;TextBox      ;10500;846  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][3] }
            { 46  ;TextBox      ;10500;1269 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][4] }
            { 47  ;TextBox      ;10500;1692 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][5] }
          }
           }
        { PROPERTIES
          {
            SectionType=Body;
            SectionWidth=20600;
            SectionHeight=2115;
            KeepWithNext=No;
            OnPreSection=BEGIN
                           CurrReport.SHOWOUTPUT((ColumnNo = 0) AND (LabelFormat = LabelFormat::"37 x 105 mm (2 columns)"));
                         END;

          }
          CONTROLS
          {
            { 49  ;TextBox      ;0    ;0    ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][1] }
            { 50  ;TextBox      ;0    ;423  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][2] }
            { 51  ;TextBox      ;0    ;846  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][3] }
            { 52  ;TextBox      ;0    ;1269 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][4] }
            { 53  ;TextBox      ;0    ;1692 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[1][5] }
            { 55  ;TextBox      ;10500;0    ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][1] }
            { 56  ;TextBox      ;10500;423  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][2] }
            { 57  ;TextBox      ;10500;846  ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][3] }
            { 58  ;TextBox      ;10500;1269 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][4] }
            { 59  ;TextBox      ;10500;1692 ;10000;423  ;FontSize=9;
                                                         SourceExpr=CustAddr[2][5] }
          }
           }
      }
       }
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=9130;
      Height=2090;
      SaveValues=Yes;
    }
    CONTROLS
    {
      { 1   ;OptionButton ;3410 ;0    ;5500 ;440  ;CaptionML=[ENU=AVERY 5960 (3 columns) 36 x 70 mm;
                                                              ESM=36 x 70 mm (3 columnas);
                                                              FRC=36 x 70 mm (3 colonnes);
                                                              ENC=36 x 70 mm (3 columns)];
                                                   SourceExpr=LabelFormat;
                                                   OptionValue=36 x 70 mm (3 columns) }
      { 21  ;Label        ;0    ;0    ;3300 ;440  ;ParentControl=1;
                                                   CaptionML=[ENU=Format;
                                                              ESM=Formato;
                                                              FRC=Format;
                                                              ENC=Format] }
      { 2   ;OptionButton ;3410 ;550  ;3850 ;440  ;CaptionML=[ENU=37 x 70 mm (3 columns);
                                                              ESM=37 x 70 mm (3 columnas);
                                                              FRC=37 x 70 mm (3 colonnes);
                                                              ENC=37 x 70 mm (3 columns)];
                                                   SourceExpr=LabelFormat;
                                                   OptionValue=37 x 70 mm (3 columns) }
      { 3   ;OptionButton ;3410 ;1100 ;5390 ;440  ;CaptionML=[ENU=AVERY 5961 (2 columns) 36 x 105 mm;
                                                              ESM=36 x 105 mm (2 columnas);
                                                              FRC=36 x 105 mm (2 colonnes);
                                                              ENC=36 x 105 mm (2 columns)];
                                                   SourceExpr=LabelFormat;
                                                   OptionValue=36 x 105 mm (2 columns) }
      { 4   ;OptionButton ;3410 ;1650 ;3850 ;440  ;CaptionML=[ENU=37 x 105 mm (2 columns);
                                                              ESM=37 x 105 mm (2 columnas);
                                                              FRC=37 x 105 mm (2 colonnes);
                                                              ENC=37 x 105 mm (2 columns)];
                                                   SourceExpr=LabelFormat;
                                                   OptionValue=37 x 105 mm (2 columns) }
    }
  }
  CODE
  {
    VAR
      LabelFormat@1000 : '36 x 70 mm (3 columns),37 x 70 mm (3 columns),36 x 105 mm (2 columns),37 x 105 mm (2 columns)';
      CustAddr@1001 : ARRAY [3,8] OF Text[50];
      NoOfRecords@1002 : Integer;
      RecordNo@1003 : Integer;
      NoOfColumns@1004 : Integer;
      ColumnNo@1005 : Integer;
      i@1006 : Integer;
      FormatAddr@1007 : Codeunit 365;

    BEGIN
    END.
  }
}
Half-empy or half-full how do you view your database?

Thanks.

Comments

  • Options
    emulsifiedemulsified Member Posts: 139
    Okay I used the report above and did this:

    My data items are:

    Sales Invoice Line
    -->Customer

    Customer DataItem properties are:

    DataItemLinkReference: <Sales Invoice Line>
    DataItemLink: No.=FIELD(Sell-to Customer No.)

    C/AL Global added: TempSellToCustomerNo Text 30

    C/AL Code in DataItem Sales Invoice Line:
    Sales Invoice Line - OnAfterGetRecord() 
    IF "Sell-to Customer No." = TempSellToCustomerNo THEN CurrReport.SKIP;
TempSellToCustomerNo := "Sell-to Customer No.";

    This is okay except that I get duplicates of customers because they bought the item with item category code on a different date.

    If I can just group or filter the data in Sales Invoice Line by "Sell-To Customer No." from the start I shouldn't have any problem.

    I'm stuck and only one small step away and I can't figure it out.
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    DaveTDaveT Member Posts: 1,039
    Hi,

    I would approach this a different way. Leave the dataitem as Customer and write code to see if they have sales of items with the item category code and if not skip the customer.
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
  • Options
    jversusjjversusj Member Posts: 489
    i agree with DaveT. you could even put a lookup to the Item Category table on the options tab of the report so that it was dynamic and not hardcoded for a single ICC. you would pass this variable to your code where you would build a filter on the sales lines. if your salesline record set turns out ISEMPTY, you would do currreport.SKIP and that customer would not have a label print.
    kind of fell into this...
  • Options
    emulsifiedemulsified Member Posts: 139
    Do tell jversusj/DaveT...

    Basically a lookup textbox (TextBoxName) tied to the Item Category Code table on the dialog options tab and then the following in the OnAfterGetRecord of Customer:

    SETFILTER(SalesLine."Item Category Code",TextBoxName) or SETRANGE(SalesLine."Item Category Code",TextBoxName)

    Some sort of FIND loop next

    IF statement here somewhere with a CurrReport.SKIP;


    I get the idea but don't know how to write the code for my ramblings above.

    Can I get a hand with this?
    Half-empy or half-full how do you view your database?

    Thanks.
  • Options
    DaveTDaveT Member Posts: 1,039
    Hi,

    the code you need is like:
    SaleInvoiceLine.setrange( "Sell-to Customer No.", customer."no." );
SaleInvoiceLine.setrange( type, SaleInvoiceLine.type::Item );
SaleInvoiceLine.setrange( "Item Category Code", 'MYCODE' );
if NOT SaleInvoiceLine.findfirst then
   CurrReport.skip;

    The problem with using the invoice line table is if an invoice was cancelled be a credit it will still appear on the report.
    Dave Treanor

    Dynamics Nav Add-ons
    http://www.simplydynamics.ie/Addons.html
Sign In or Register to comment.