In this example I describe how we can renumbering records in a grid. We developed two buttons. The “Up” button moves record to the top and in the process the line number field, which is in our example also the primary key of the table get automatically renumbered. The “Down” record is doing the same but in opposite direction. Also in the case we remove a record, system is renumbering the line numbers.
For example in below example we see that account number 100001 has line number 1. If we click the “down” button we see that the line is moving downward and it gets the line number 2. The originally line with line number 2 will get line number 1. Every time user click the “down” button this process will be repeated until it’s reach the last record of the grid. In that case the “down” button will be disabled, so that user cannot continue. The “up” button works in the same way, but in opposite direction.
In the right corner of the form we see two buttons with the name “Up” and “Down”.
We see that the record with the account number has Line number 1. When we click the ‘Down’ button, we are able to move this record through the grid and during the process, the line number will be re-assigned depending of the position in the grid. We see that the first record has Line number 1 and account number 100001. Now we move that line to the 4th position in the grid. We see that line number will be changed to 4.
With the “Up” button we can set back the record to the first position. The Down button works the same in opposite direction:
If we check the form design we see the following structure. The red markings are the controls we need to modify. In the design we see that the added buttongroup plus the buttons. At the left side we see that there is a new method “setDialogButton” which cover the logic for disabling/enabling the “Up” and “Down” buttons. At the Datasource we need to modify ‘active’ and ‘delete’ methods.
In the table (of the form data source) we need to add the following methods:
At form, at the “Up” and “Down” button controls we make the following changes. When the user clicks button ‘up’ or button ‘down’ firstly following logic in the ‘clicked’ methods of buttons are called:
We see a reference to the “LineNumUp” and LineNumDown” method of the (datasource) table. In case when the ‘Up’ button is clicked, the “LineNumUp” of the datasource table is called and for ‘Down’ it’s the “LineNumDown” method.
In both methods we give the current selected record of the grid as parameter. The LineNumUp method also buffering the previous record, and the LineNumDown is buffering the next records.
For example if the lineNumber 2 is selected and we click ‘Up’ the LineNumUp method is buffering also the record with lineNumber 1. In the same scenario, the ‘Down’ button which call LineNumDown is buffering lineNumber 3.
These two record buffers are given as parameter to the datasource method “exchangeLineNum”. This method show us the logic how to swap the record buffers. Be aware that the reread of the record buffers should be done first. Also check if table property cache lookup. It should not set to “NotInTTS”. If this is not the case we could get the following error:
Cannot edit a record in FoxServiceOrderTable.
Cannot call NEXT, update(), or delete() on buffer where data is selected or inserted in another transaction scope. Calls to NEXT, update(), or delete() must be performed on the buffer on the selection transaction level, or within the same transaction (TTS) scope. 
Furthermore we need also make following changes in the code to the form data source: Create method ‘setDialogButtons’ in the form root. The method disable the Up and Down button once in the grid the first (“Up” button) rest the last (“Down”) record in the grid is selected. In the datasource ‘active’ method we make the call to this method.
Logic to find the findMinLineNum (“Up” button”) and findMaxLineNum (“Down” button):
Add the following code to the ‘Clicked’ method of the button ‘Up’ and ‘Down’: When we delete one of the records in the grid, we will do instantly a renummering to avoid gaps in linenumber sequence. In the datasource ‘delete’ method we make following modifications: We see a call to the (datasource) table method “renummeringLines”.
The method which is re-nummering our table: