X++ query to SELECT MAX value of String type field

Question

I have a field in Dynamics AX 2012 table populated with serial number of PRE1 00162 format and I need to return next serial number, which in this case would be PRE1 00163.

On a legacy system it is achieved by running

SELECT MAX(RIGHT(SerialNumber,5))+1 FROM Table_Serials 
WHERE SerialNumber LIKE 'PRE1%' 

against the table on SQL server.

How can I achieve same result in X++? My guess so far is

select maxof(right(SerialNumber,5))+1
    from tableSerials
    where tableSerials.SerialNumber 
like tableSerials;

but it shows syntax error starting right after maxof(right( part.

Thank you!

Answer 1

The right way should be to create Number Sequence (Whitepaper).

---

If you relay want to keep legacy functionality you have to create AOT View with computed column. Example for the field ViewMethod:

private static server str compSerial()
{
    str srcCol = SysComputedColumn::returnField(tableStr(TableSerialsView), identifierStr(TableSerials_1), fieldStr(TableSerials, SerialNumber));
    str result = strFmt('MAX(RIGHT(%1, 5)) + 1', srcCol);

    return result;
}

And then add computed field to the fields of the view and select.