Skip to main content
Answered

Query but Method = Max can't be applied to Date values

editSubscribe (0) ShareShare
ReportReport
Posted on by 530
So I'm building a customer query. Including their contact details, along with some sales summary fields, such as total quantity and total amount. I would like to also present the user with their last sale date. But applying the Max method to a Date field isn't allowed? If AL is supposed to emulate basic T-SQL then the MAX method is certainly available using that route. Here's my code below. Any suggestions as to how I can add the most recent Sales Line Posting Date to this result set?
 
/// <summary>
/// Query DCH Customer Sales Totals (ID 50003).
/// </summary>
query 50003 /DCH Customer Sales Totals/
{
Caption = 'DCH Customer Sales Totals';
QueryType = Normal;
//OrderBy = Descending(Posting_Date);
 
elements
{
dataitem(Customer; Customer)
{
column(No; /No./)
{
}
column(Name; Name)
{
}
column(Address; Address)
{
}
column(Address2; /Address 2/)
{
}
column(City; City)
{
}
column(County; County)
{
}
column(PostCode; /Post Code/)
{
}
column(PhoneNo; /Phone No./)
{
}
column(MobilePhoneNo; /Mobile Phone No./)
{
}
column(EMail; /E-Mail/)
{
}
column(SalespersonCode; /Salesperson Code/)
{
}
column(MailOK; /Mail OK/)
{
}
column(EmailOK; /Email OK/)
{
}
column(Location_Code; /Location Code/)
{
 
}
dataitem(Sales_Line; /Sales Line/)
{
DataItemLink = /Sell-To Customer No./ = Customer./No./;
SqlJoinType = InnerJoin;
 
column(Quantity; Quantity)
{
Caption = 'Sum Quantity';
Method = Sum;
}
column(Amount; Amount)
{
Caption = 'Sum Amount';
Method = Sum;
}
// Need to present the user with the last sale date. Method = Max doesn't work for Date data types. :(
/*column(Posting_Date; /Posting Date/)
{
Caption = 'Last Sale';
}*/
}
}
}
 
trigger OnBeforeOpen()
begin
 
end;
}
Categories: