SetAscending Business Central Sales Order List

Question

I am attempting to use the SetAscending in Visual Studio Code for Business Central Sales Order List Page. It works to set the Sales Order "No." field to descending however it does not reset the scrollbar to the top of the page. I've tried adding it to multiple places and it sorts the page but no scrollbar update. What code could I be missing?

Places I've added it to: Sales Order List Page Extension - OnAfterGetRecord and OnAfterGetCurrRecord Sales Order List Page Events - OnOpenPageEvent, OnAfterGetRecordEvent, and OnAfterGetCurrRecordEvent

trigger OnAfterGetCurrRecord()
    begin
        rec.SetCurrentKey("Document Type", "No.");
        rec.SetAscending("No.", false);
    end

score 4 · Accepted Answer · answered Dec 21 '20 at 19:12

4

You must tell the page to navigate to the new first record.

In the OnOpenPage trigger you do the following:

Rec.SetCurrentKey("Document Type", "No.");
Rec.SetAscending("No.", false);
if Rec.FindSet() then; // use if-then to avoid error if there are no records within the filter

answered Dec 21 '20 at 19:12

kaspermoerch

16,127
4
44
67

how would I write the part after FindSet(), I thought maybe then FindFirst but that seems to filter to only my very last record. – Abby Dec 23 '20 at 15:50

Jonathan Bravetti · Answer 2 · 2020-12-24T21:58:38.653

1

You have 3 methods to use as FIND

FindFirst = Find the first ocurrence of your filters

Findlast = Find the last ocurrence of your filters

FindSet = Find a set of ocurrence of your filter

You can use all of them with the repeat and until statements to loop through records.

The difference between FindFirst and FindSet for Repeat and Until is that FindSet find a Set of registers Whilst FindFirst find only one so FindSet it is more performant.

For your case You need to modify the property sourceTableView. This is a page property. FindSet is used for variables not for DataSourceTable in page list.

Create a key in your table and then put this key in sourceTableView property of the page.

edited Dec 24 '20 at 21:58

answered Dec 23 '20 at 19:26

Jonathan Bravetti

2,228
2
15
29

would you be able to provide sample code of how I would loop through the records to point the list page back to the top? I understand the FindSet() but not sure what should be in then statement once it finds the set. – Abby Dec 23 '20 at 23:01
Update my answer – Jonathan Bravetti Dec 24 '20 at 21:58
1

It is bad for performance to use a `repeat` with `FindFirst` or `FindLast` as they only retrieve a single record from the database. If you add a repeat afterwards it causes an extra roundtrip to the database. `FindSet` does the same as `FindFirst` meaning it finds the first record within the filter, but it also retrieves an additional number of records (the acutal number of records is optimized by the SQL Server). – kaspermoerch Jan 10 '21 at 15:59

score 0 · Answer 3 · answered Dec 28 '20 at 15:35

This is what worked for me. Since, this is a standard list page in Business Central I could not modify the SourceTableView. I had to use two triggers to first sort and the move the cursor to the top of the list.

    trigger OnAfterGetRecord()
    begin
        rec.SetCurrentKey("Document Type", "No.");
        rec.SetAscending("No.", false);
    end;

    trigger OnOpenPage()
    begin
        rec.FindLast();
    end;

score 0 · Answer 4 · answered Dec 15 '21 at 17:19

I was testing answers and reading about this subject on learn.microsoft.com, but the only trick that works is the following:

    trigger OnOpenPage()
    begin
        Rec.SetCurrentKey(SystemCreatedAt);
        Rec.Ascending := false;
    end;

Note: I'm sorting by a datetime object. But the clues are: OnOpenPage trigger, SetCurrentKey and Ascending property instead of SetAscending. This is the unique solution that leaves the scroll bar on the top, at least for me.

score 0 · Answer 5 · answered May 10 '23 at 16:09

This also works, if you programatically open a page with Page.RUN:

procedure ShowYTDSalesOrders(DateFilter: Text)
var
    salesHeader: Record "Sales Header";        
begin
    salesHeader.RESET;
    salesHeader.SETRANGE("Document Type", salesHeader."Document Type"::Order);
    salesHeader.SetFilter("Order Date", DateFilter);
    salesHeader.SetCurrentKey("Order Date");
    salesHeader.Ascending(false);
    salesHeader.SetAscending("Order Date", false);
    if salesHeader.FindSet() then
        PAGE.RUN(PAGE::"Sales Order List", salesHeader);
end;

SetAscending Business Central Sales Order List

5 Answers5