Dynamic date filtering in a AL query

(1) Share

Report

Posted on by VeNeZ

Hi there,

Hoping somebody can give me the hint to solve my small riddle. In essence I would like to pre-filter for overdue invoices with the help of a query (see below for AL code). That works fine if I am using a hardcoded solution by specifying a day for the 'ColumnFilter' within the dataitem. E.g. if today is the 20th of a month the code is returning all invoices summed up until the 19th of the current month.

Now, obvious that's not a solution that works for every day of a month. I thought, I could achieve my goal by setting the filter via the trigger method, but the return results are identical with a non-filtered item.

Please tell me, what am I missing for a dynamic solution?

PS: this answer didn't worked for me

query 10001 SummedUpInvoices
{
    Caption = 'Open Invoices'; // without any date filter
    //Caption = 'Overdue Invoices'; // with date filter 'earlier than today' applied
    QueryType = Normal;
    QueryCategory = 'Sales Invoice List';

    elements
    {
        dataitem(SalesInvoiceHeader; "Sales Invoice Header")
        {
            column(JobNo; "Job No.")
            {
            }
            filter(DueDate; "Due Date")
            {
                // ColumnFilter = DueDate = filter(< 20); // hardcoded, non-dynamical solution
            }
            column(AmountIncludingVAT; "Amount Including VAT")
            {
                Method = Sum;
            }
        }
    }

    trigger OnBeforeOpen()
    begin
        TodayDate := Today();
        //CurrQuery.SetFilter(DueDate, '< %1', TodayDate); // dynamic approach, but doesn't work
    end;

    var
        TodayDate: Date;

}

Categories:

Dynamics 365 Business Central

All responses (4)

Answers (1)

Verified answer

VeNeZ 30 on at

Like (0)

Report
Dynamic date filtering in a AL query

If I am being honest, I do not know what now made the difference but it works with the code as per below for past respectively upcoming invoices.

Nevertheless, thanks for the inspiration and your time.

trigger OnBeforeOpen() begin // For invoices in the past CurrQuery.SetFilter(DueDate, '<%1', Today); // or alternative via a range where the start is specified in the far past //CurrQuery.SetRange(DueDate, DMY2Date(1, 1, 2010), Today); // // // For upcoming invoices //CurrQuery.SetFilter(DueDate, '>%1', Today); // or alternative via a range where the end is specified in the far future //CurrQuery.SetRange(DueDate, Today, DMY2Date(1, 1, 2100)); // end;
Suggested answer

YUN ZHU 77,925 Super User 2025 Season 1 on at

Like (1)

Report
Dynamic date filtering in a AL query

I tested it briefly and there is no problem.

My test code:

query 50200 "Customer_Sales_Quantity" { QueryType = Normal; QueryCategory = 'Customer List'; elements { dataitem(C; Customer) { column(Customer_Number; "No.") { } column(Customer_Name; Name) { } dataitem(SL; "Sales Line") { DataItemLink = "Sell-to Customer No." = c."No."; SqlJoinType = InnerJoin; column(Qty; Quantity) { } column(Posting_Date; "Posting Date") { } } } } trigger OnBeforeOpen() begin currQuery.SetRange(Posting_Date, Today); end; }
VeNeZ 30 on at

Like (2)

Report

Dynamic date filtering in a AL query

Thanks for your response, but as mentioned (and written directly in the code as well) the 'OnOpenBefore' trigger method doesn't work (for unknown reason to myself). Any idea, what is wrong there?

On the other topic of 'object that runs the query': as I am using the 'QueryCategory' property, the option to execute is given me on the page(s) I specified there. Do you mean I have to write instead a page extension and introduce it there with additional code?

BR
Suggested answer

YUN ZHU 77,925 Super User 2025 Season 1 on at

Like (1)

Report

Dynamic date filtering in a AL query

Hi, I think the simplest method is the following:

You can call the SetFilter and SetRange method from the AL code of the Business Central object that runs the query object or from the OnBeforeOpen Trigger of the query object.

https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/devenv-query-filters?wt.mc_id=DX-MVP-5004336#calling-the-setfilter-and-setrange-methods

Hope this can give you some hints.

Thanks.

ZHU