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;
}