Renumber line number when its part of a key

In this post, I will show how to renumber the line numbers in a table with out getting the duplicate record is not allowed.

A bit of background first, say you have a table with line number field. It gets out of sequence and now you want to renumber it.

You can’t simply write a method to loop through and renumber it. The system won’t allow you do it.

One solution is to number then to something unique (ie. negative numbers). Then do a second loop and number them correctly.

Another solution is below. I store the RecId and LineNum in a temp table. Then I do a bulk update using update_recordset.

Note: I am using a fake table below called WorkOrder and WorkOrderLine.

static void renumberLineNum(JournalId    _journalId)
{
    WorkOrder                 workOrder;
    WorkOrderLine             workOrderLine;
    TmpLineRenumbering        tmpLineRenumbering;
    real                      lineNum;   

    while select RecId, LineNum from workOrder
        order by workOrder.LineNum
        where workOrder.JournalId == _journalId
    {
        lineNum += 1;
        tmpLineRenumbering.LineRecId = workOrder.RecId;
        tmpLineRenumbering.LineNum   = lineNum;
        tmpLineRenumbering.insert();
    }   

    workOrder.skipDataMethods(true);   

    ttsBegin;

    update_recordset workOrderLine
            setting LineNum = tmpLineRenumbering.LineNum
    join workOrder
        where   workOrder.JournalId == workOrderLine.JournalId &&
                workOrder.LineNum   == workOrderLine.LineNum
    join LineNum from tmpLineRenumbering
        where tmpLineRenumbering.LineRecId == workOrder.RecId &&
              tmpLineRenumbering.LineNum   != workOrder.LineNum;

    update_recordset workOrder
            setting LineNum = tmpLineRenumbering.LineNum
    join LineNum from tmpLineRenumbering
        where tmpLineRenumbering.LineRecId == workOrder.RecId &&
              tmpLineRenumbering.LineNum !=   workOrder.LineNum;   

    ttsCommit;
}