X++ Update_recordset using MaxOf

Question

Is there anyway to use MaxOf on update_recordset on X++?

ttsbegin;
update_recordset asuRepairServiceProdDeviceInfo
    setting RepairCompletionDate = **maxof**(asuRepairOrderTrans.ScanEndDateTime)
    join asuRepairOrderTrans
    where asuRepairServiceProdDeviceInfo.RepairOrderID ==  asuRepairOrderTrans.RepairOrderId 
       && asuRepairOrderTrans.RepairStepId == '7000';

info(strFmt("Repair Completion Date FG : Number of records updated is %1.",
            asuRepairServiceProdDeviceInfo.rowCount()));
ttscommit;

Or any different approach aside for While statement? as I'm working on over millions of records

Answer 1

The update_recordset does not allow aggregate functions. You can however use aggregates in views.

Create a view (ASURepairOrderMaxDate) on the the ASURepairOrderTrans containing the RepairOrderId, RepairStepId and max(ScanEndDateTime) fields.

Then the update is easy:

update_recordset asuRepairServiceProdDeviceInfo
    setting RepairCompletionDate = asuRepairOrderMaxDate.ScanEndDateTime
    join asuRepairOrderMaxDate
    where asuRepairOrderMaxDate.RepairOrderId ==  asuRepairServiceProdDeviceInfo.RepairOrderId
       && asuRepairOrderMaxDate.RepairStepId  == '7000';
info(strFmt("Repair Completion Date FG : Number of records updated is %1.", 
            asuRepairServiceProdDeviceInfo.rowCount()));