Hi, Readers.
Today I would like to discuss an interesting question that was asked recently, how to find last second record in the table via AL.
For example, how to get the record of customer 40000 in the customer table below. Regardless of the purpose, is there a convenient way to do this?
In this post, I will simply share three methods, hoping to help those who need this.
PS: Dynamics 365 Business Central: How to get the sort order for the records returned (Record.GetAscending Method)
1. Using Repeat-until (Loop)
This way of thinking is very simple. First, sort the data in the order you need, such as in descending order, and then find the second value, which is the second to last.
This only loops once, so it shouldn’t have much of an impact on performance.
Test code:
pageextension 50119 CustomerListExt extends "Customer List"
{
actions
{
addafter("Co&mments")
{
action(GetLastSecondRecord)
{
Caption = 'Get Last Second Record';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
Image = GetLines;
trigger OnAction()
var
Cust: Record Customer;
i: Integer;
begin
i := 0;
Cust.Reset();
Cust.Ascending(false);
if Cust.FindSet() then
repeat
i := i + 1;
if i = 2 then begin
Message('The second to last Customer Name: %1', Cust.Name);
exit;
end;
until Cust.Next() = 0;
end;
}
}
}
}PS: This can also be done using Cust.Next(-1), the challenge is to reduce the number of loops.
pageextension 50119 CustomerListExt extends "Customer List"
{
actions
{
addafter("Co&mments")
{
action(GetLastSecondRecord)
{
Caption = 'Get Last Second Record';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
Image = GetLines;
trigger OnAction()
var
Cust: Record Customer;
i: Integer;
begin
i := 0;
Cust.Reset();
if Cust.FindLast() then
repeat
i := i + 1;
if i = 2 then
Message('The second to last Customer Name: %1', Cust.Name);
until (Cust.Next(-1) = 0) or (i = 2);
end;
}
}
}
}2. Using Record.FindLast() Method twice
Record.FindLast() Method: Finds the last record in a table based on the current key and filter.
This method is also very interesting. We can first find the last value, then exclude it in the filter, and then find the last value again, which is the second to last value.
Very simple.
Test code:
pageextension 50119 CustomerListExt extends "Customer List"
{
actions
{
addafter("Co&mments")
{
action(GetLastSecondRecord)
{
Caption = 'Get Last Second Record';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
Image = GetLines;
trigger OnAction()
var
Cust: Record Customer;
Cust1: Record Customer;
begin
Cust.Reset();
Cust.Ascending(true);
if Cust.FindLast() then begin
Cust1.Reset();
Cust1.SetFilter("No.", '<>%1', Cust."No.");
if Cust1.FindLast() then
Message('The second to last Customer Name: %1', Cust1.Name);
end;
end;
}
}
}
}3. Using Record.FindLast() Method and Record.Next([Integer]) Method
This method comes from the community, thanks for the information. It is similar to the second method, but it is a little more concise.
Test code:
pageextension 50119 CustomerListExt extends "Customer List"
{
actions
{
addafter("Co&mments")
{
action(GetLastSecondRecord)
{
Caption = 'Get Last Second Record';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
Image = GetLines;
trigger OnAction()
var
Cust: Record Customer;
begin
Cust.Reset();
Cust.Ascending(true);
if Cust.FindLast() and (Cust.Next(-1) = -1) then
Message('Customer Name: %1', Cust.Name);
end;
}
}
}
}4. Using Query Object (Query.TopNumberOfRows Method)
As you might know, in SQL Statement, we can use SQL SELECT TOP Clause to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of records. Returning a large number of records can impact performance. For example, SELECT TOP 3 * FROM Customer
So if you use SQL statements this time, you can do it with the following statements.
SELECT TOP 1 * From
(select Top 2 * from Customer ORDER BY CustomerNo DESC) x
ORDER BY CustomerNoBut unfortunately, there is no such convenient method in AL. If we want to do something similar, we can use Query Object. Because we can use Query.TopNumberOfRows([Integer]) Method.
Query.TopNumberOfRows([Integer]) Method: Specifies the maximum number of rows to include in the resulting data set of a query.
Let’s look at a simple example. First we need to create a Query Object, sets the results to include the top 2 the results in descending order
Then get the second value from the created Query Object. Because the Query Object does not have a FindLast method, a simple loop is required.
Same result: Test video
Test code:
pageextension 50119 CustomerListExt extends "Customer List"
{
actions
{
addafter("Co&mments")
{
action(GetLastSecondRecord)
{
Caption = 'Get Last Second Record';
ApplicationArea = All;
Promoted = true;
PromotedCategory = Process;
Image = GetLines;
trigger OnAction()
var
GetLastSecondCustomer: Query "Get Last Second Customer";
i: Integer;
begin
i := 0;
if GetLastSecondCustomer.Open() then
while GetLastSecondCustomer.Read() do begin
i := i + 1;
if i = 2 then
Message('The second to last Customer Name: %1', GetLastSecondCustomer.Customer_Name);
end;
GetLastSecondCustomer.Close();
end;
}
}
}
}
query 50123 "Get Last Second Customer"
{
// Sets the results to include the top 2 the results in descending order
TopNumberOfRows = 2;
OrderBy = descending(Customer_Number);
elements
{
dataitem(C; Customer)
{
column(Customer_Number; "No.")
{
}
column(Customer_Name; Name)
{
}
}
}
}Great, give it a try!!!😁
END
Hope this will help.
Thanks for reading.
ZHU
コメント