You’re offline. This is a read only version of the page.
Announcements
Hi Community,
I'm working on a customised "Salesperson - Commission" report where it is extended with the Sales lines to get a more detailed view of the commission and sales. There is also a customisation where the commission is based on the item, not on the salesperson.
The challenge I am facing is when there is a Sales Credit Memo for a Sales Invoice. The report should only show Sales Invoice Lines where there is no Sales Credit Memo (lines).
The only connection I have found between the two (Sales Invoice and Sales Credit Memo) are the fields "Applies-to Doc. No." and "Applies-to Doc. Type" in the Credit Memo. It would also be possible to use the "Cancelled" field on the Sales Invoice Header but this is probably not reliable in all scenarios.
The dataset in the report currently looks as this (high level):
Share
Reportdataset
{
dataitem("Salesperson/Purchaser"; "Salesperson/Purchaser")
{
RequestFilterHeading = 'Salesperson/Purchaser';
DataItemTableView = SORTING(Code);
PrintOnlyIfDetail = true;
RequestFilterFields = "Code";
dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")
{
DataItemLink = "Salesperson Code" = FIELD(Code);
DataItemTableView = SORTING("Salesperson Code", "Posting Date") WHERE("Document Type" = FILTER(Invoice | "Credit Memo"));
RequestFilterHeading = 'Time Period';
RequestFilterFields = "Posting Date";
dataitem("Sales Invoice Header"; "Sales Invoice Header")
{
DataItemLink = "No." = FIELD("Document No.");
dataitem("Sales Invoice Line"; "Sales Invoice Line")
{
DataItemLink = "Document No." = FIELD("No.");
DataItemTableView = SORTING("Document No."));
}
}
}
}
How is it possible to extend the dataset with filters or queries to achieve the goal of only getting invoices without corresponding sales invoices?
Thank you in advance for your input.
Glad you got it working!
Please help the community by marking the question as answered if you felt you got info / assistance that solved your problem.
Keep up the good work!
Thank you all for your contributions. With your support the problem is solved.
The solution looks like this in the end:
All responses (
Answers (
dataset
{
dataitem("Salesperson/Purchaser"; "Salesperson/Purchaser")
{
RequestFilterHeading = 'Salesperson/Purchaser';
DataItemTableView = SORTING(Code);
PrintOnlyIfDetail = true;
RequestFilterFields = "Code";
dataitem("Cust. Ledger Entry"; "Cust. Ledger Entry")
{
DataItemLink = "Salesperson Code" = FIELD(Code);
DataItemTableView = SORTING("Salesperson Code", "Posting Date") WHERE("Document Type" = FILTER(Invoice));
RequestFilterHeading = 'Time Period';
RequestFilterFields = "Posting Date";
dataitem("Sales Invoice Header"; "Sales Invoice Header")
{
DataItemLink = "No." = FIELD("Document No.");
dataitem("Sales Invoice Line"; "Sales Invoice Line")
{
DataItemLink = "Document No." = FIELD("No.");
DataItemTableView = SORTING("Document No."));
}
}
trigger OnAfterGetRecord()
var
SalesCreditMemo: Record "Sales Cr.Memo Header";
begin
SalesCreditMemo.Reset();
SalesCreditMemo.SetRange("Applies-to Doc. No.", "Document No.");
If SalesCreditMemo.FindFirst() then
CurrReport.Skip();
end;
}
}
}
Adding it it in the OnAfterGerRecord for the sales line produced an extra line and it had to be moved to the "Cust. Ledger Entry"
I very much appreciated your time and support.
Hi,
As per the above report.
pls write small piece of code onAfterggetrecord
SalesCrMemo.reset.
SalesCrMemo.Setrange("Applies-to Doc No.","Document No.");
if SalesCrMemo..findfirst then
currreport.skip.
And also put filter on CUST. LEDGER Entry dataitem only Document Type = Sales Invoice
Regards
Amit Sharma
That gave me some ideas, thanks!
I now see the issue, which I didn't express clearly before. The problem that the "Applies-to Doc. Type" and "Applies-to Doc. No." are only filled on the Credit Memo and not on the corresponding Sales Invoice. When trying to exclude the Sales Invoices with corresponding Credit Memo, the Sales Invoice to be excluded have to be taken from the Credit Memo.
Can this be achieved?
This report will list all sales invoice lines where the sales invoice is not applied against a sales credit memo.
Hope you can use that to build on. I listen all the fields on the sales invoice line. You can remove those not needed for your report.
report 50102 SalesInvoiceLineReport
{
ApplicationArea = All;
Caption = 'SalesInvoiceLineReport';
UsageCategory = Lists;
dataset
{
dataitem(SalesInvoiceLine; "Sales Invoice Line")
{
column(AccountCode; "Account Code")
{
}
column(AllowInvoiceDisc; "Allow Invoice Disc.")
{
}
column(AllowLineDisc; "Allow Line Disc.")
{
}
column(Amount; Amount)
{
}
column(AmountIncludingVAT; "Amount Including VAT")
{
}
column(ApplfromItemEntry; "Appl.-from Item Entry")
{
}
column(AppltoItemEntry; "Appl.-to Item Entry")
{
}
column(Area; "Area")
{
}
column(AttachedtoLineNo; "Attached to Line No.")
{
}
column(BilltoCustomerNo; "Bill-to Customer No.")
{
}
column(BinCode; "Bin Code")
{
}
column(BlanketOrderLineNo; "Blanket Order Line No.")
{
}
column(BlanketOrderNo; "Blanket Order No.")
{
}
column(CrossReferenceNo; "Cross-Reference No.")
{
}
column(CrossReferenceType; "Cross-Reference Type")
{
}
column(CrossReferenceTypeNo; "Cross-Reference Type No.")
{
}
column(CustomerDiscGroup; "Customer Disc. Group")
{
}
column(CustomerPriceGroup; "Customer Price Group")
{
}
column(DeferralCode; "Deferral Code")
{
}
column(DepruntilFAPostingDate; "Depr. until FA Posting Date")
{
}
column(DepreciationBookCode; "Depreciation Book Code")
{
}
column(Description; Description)
{
}
column(Description2; "Description 2")
{
}
column(DimensionSetID; "Dimension Set ID")
{
}
column(DocumentNo; "Document No.")
{
}
column(DropShipment; "Drop Shipment")
{
}
column(DuplicateinDepreciationBook; "Duplicate in Depreciation Book")
{
}
column(ExitPoint; "Exit Point")
{
}
column(FAPostingDate; "FA Posting Date")
{
}
column(GenBusPostingGroup; "Gen. Bus. Posting Group")
{
}
column(GenProdPostingGroup; "Gen. Prod. Posting Group")
{
}
column(GrossWeight; "Gross Weight")
{
}
column(ICItemReferenceNo; "IC Item Reference No.")
{
}
column(ICPartnerCode; "IC Partner Code")
{
}
column(ICPartnerRefType; "IC Partner Ref. Type")
{
}
column(ICPartnerReference; "IC Partner Reference")
{
}
column(InvDiscountAmount; "Inv. Discount Amount")
{
}
column(ItemCategoryCode; "Item Category Code")
{
}
column(ItemReferenceNo; "Item Reference No.")
{
}
column(ItemReferenceType; "Item Reference Type")
{
}
column(ItemReferenceTypeNo; "Item Reference Type No.")
{
}
column(ItemReferenceUnitofMeasure; "Item Reference Unit of Measure")
{
}
column(JobContractEntryNo; "Job Contract Entry No.")
{
}
column(JobNo; "Job No.")
{
}
column(JobTaskNo; "Job Task No.")
{
}
column(LineAmount; "Line Amount")
{
}
column(LineDiscount; "Line Discount %")
{
}
column(LineDiscountAmount; "Line Discount Amount")
{
}
column(LineDiscountCalculation; "Line Discount Calculation")
{
}
column(LineNo; "Line No.")
{
}
column(LocationCode; "Location Code")
{
}
column(NetWeight; "Net Weight")
{
}
column(No; "No.")
{
}
column(Nonstock; Nonstock)
{
}
column(OrderLineNo; "Order Line No.")
{
}
column(OrderNo; "Order No.")
{
}
column(PmtDiscountAmount; "Pmt. Discount Amount")
{
}
column(PostingDate; "Posting Date")
{
}
column(PostingGroup; "Posting Group")
{
}
column(PrepaymentLine; "Prepayment Line")
{
}
column(PriceCalculationMethod; "Price Calculation Method")
{
}
column(Pricedescription; "Price description")
{
}
column(PurchasingCode; "Purchasing Code")
{
}
column(QtyperUnitofMeasure; "Qty. per Unit of Measure")
{
}
column(Quantity; Quantity)
{
}
column(QuantityBase; "Quantity (Base)")
{
}
column(ResponsibilityCenter; "Responsibility Center")
{
}
column(ReturnReasonCode; "Return Reason Code")
{
}
column(SelltoCustomerNo; "Sell-to Customer No.")
{
}
column(ShipmentDate; "Shipment Date")
{
}
column(ShipmentLineNo; "Shipment Line No.")
{
}
column(ShipmentNo; "Shipment No.")
{
}
column(ShortcutDimension1Code; "Shortcut Dimension 1 Code")
{
}
column(ShortcutDimension2Code; "Shortcut Dimension 2 Code")
{
}
column(SystemCreatedEntry; "System-Created Entry")
{
}
column(SystemCreatedAt; SystemCreatedAt)
{
}
column(SystemCreatedBy; SystemCreatedBy)
{
}
column(SystemId; SystemId)
{
}
column(SystemModifiedAt; SystemModifiedAt)
{
}
column(SystemModifiedBy; SystemModifiedBy)
{
}
column(TaxAreaCode; "Tax Area Code")
{
}
column(TaxCategory; "Tax Category")
{
}
column(TaxGroupCode; "Tax Group Code")
{
}
column(TaxLiable; "Tax Liable")
{
}
column(TransactionSpecification; "Transaction Specification")
{
}
column(TransactionType; "Transaction Type")
{
}
column(TransportMethod; "Transport Method")
{
}
column(Type; "Type")
{
}
column(UnitCost; "Unit Cost")
{
}
column(UnitCostLCY; "Unit Cost (LCY)")
{
}
column(UnitPrice; "Unit Price")
{
}
column(UnitVolume; "Unit Volume")
{
}
column(UnitofMeasure; "Unit of Measure")
{
}
column(UnitofMeasureCrossRef; "Unit of Measure (Cross Ref.)")
{
}
column(UnitofMeasureCode; "Unit of Measure Code")
{
}
column(UnitsperParcel; "Units per Parcel")
{
}
column(UseDuplicationList; "Use Duplication List")
{
}
column(VAT; "VAT %")
{
}
column(VATBaseAmount; "VAT Base Amount")
{
}
column(VATBusPostingGroup; "VAT Bus. Posting Group")
{
}
column(VATCalculationType; "VAT Calculation Type")
{
}
column(VATClauseCode; "VAT Clause Code")
{
}
column(VATCode; "VAT Code")
{
}
column(VATDifference; "VAT Difference")
{
}
column(VATIdentifier; "VAT Identifier")
{
}
column(VATProdPostingGroup; "VAT Prod. Posting Group")
{
}
column(VariantCode; "Variant Code")
{
}
column(WorkTypeCode; "Work Type Code")
{
}
trigger OnAfterGetRecord()
var
SalesInvoiceHead : Record "Sales Invoice Header";
begin
SalesInvoiceHead.Get(SalesInvoiceLine."Document No.");
If SalesInvoiceHead."Applies-to Doc. Type" = SalesInvoiceHead."Applies-to Doc. Type"::"Credit Memo" then CurrReport.Skip();
end;
}
}
requestpage
{
layout
{
area(content)
{
group(GroupName)
{
}
}
}
actions
{
area(processing)
{
}
}
}
}
Thanks.
It think this will be very rare cases as the customer will create the credit memo from the invoice.
On a technical level, how would I design a filter which can exclude the invoice lines with corresponding credit memo?
The most reliable way is to use the applied to doc. type and applied to doc no. fields.
But you should be aware that it is completely possible to create a creditmemo that has no applied to doc type or applied to doc no.
How to they fit into your logic?
#1
André Arnaud de Cal...
293,296
Super User 2025 Season 1
#2
Martin Dráb
232,093
Most Valuable Professional
#3
nmaenpaa
101,156
Moderator
