Small and medium business | Business Central, N...

Answered

Report of all Sales Invoices without Sales Credit Memos

(0) Share

Report

Posted on by Community Member

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):

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 | "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.

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (8)

Answers (1)

Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No

Suggested answer

Inge M. Bruvik

1,111 Moderator on at

Like (0)

Report

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)
            {
            }
        }
    }
}

Was this reply helpful? Yes No

Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Verified answer

Amit Baru 3,037 on at

Like (0)

Report

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

www.erpconsultors.com

Was this reply helpful? Yes No
Community Member on at

Like (1)

Report
Thank you all for your contributions. With your support the problem is solved.

The solution looks like this in the end:

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.

Was this reply helpful? Yes No
Suggested answer

Inge M. Bruvik 1,111 Moderator on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Suggested answer

Amit Baru 3,037 on at

Like (0)

Report

Pls verify the answered if useful

Regards

Amit Sharma

Was this reply helpful? Yes No

Join the conversation

Community site session details

Report of all Sales Invoices without Sales Credit Memos

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates

Community site session details

Report of all Sales Invoices without Sales Credit Memos

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Small and medium business | Business Central, NAV, RMS

Featured topics

Product updates