Label Report filtered by if a customer bought certain Items
emulsified
Member Posts: 139
](*,) 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.
:-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.
Thanks.
0
Comments
-
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.0 -
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.0 -
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...0
-
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.0 -
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.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions