If you sort on a nonvisible number field (the values can start out as auto-entered serial numbers) the buttons can use the mid point formula to move a record up or down in the sort order.
Move up Script:
If [ Get ( RecordNumber ) > 2 //at least two records are above record where button was clicked]
Set Field [ YourTable::SortField ; Let ( R = Get ( RecordNumber ) ; ( GetNthRecord ( SortOrder ; R - 2 ) + GetNthRecord ( SortOrder ; R - 1 ) ) / 2 ) ]
Else If [ Get ( RecordNumber ) = 2 // only one record above it ]
Set Field [ YourTable::SortField ; Let ( R = Get ( RecordNumber ) ; GetNthRecord ( SortOrder ; R - 1 ) / 2 )]
If [ Get ( RecordNumber ) < Get (foundCount ) - 1 //at least two records are Below record where button was clicked]
Set Field [ YourTable::SortField ; Let ( R = Get ( RecordNumber ) ; ( GetNthRecord ( SortOrder ; R + 2 ) + GetNthRecord ( SortOrder ; R + 1) ) / 2 ) ]
Else If [ Get ( RecordNumber ) = Get (foundCount ) - 1 // only one record below it ]
Set Field [ YourTable::SortField ; Let ( R = Get ( RecordNumber ) ; GetNthRecord ( SortOrder ; R + 1 ) + 1 )]
Note, your records must be kept sorted by the same specified sort order. If you select the "keep records sorted" option, these scripts do not have to sort the records each time, simply changing the value of the sort field is all that is needed. (If the records don't immediately change position, try adding commit records after the last script step shown in these examples.)
( GetNthRecord ( SortOrder ; R - 2 ) + GetNthRecord ( SortOrder ; R - 1 ) ) / 2 ) ]
I'm not allowed use SortOrder in my calculation... Did you mean it should be YourTable::Sortfield..?
I tried to put my sort field, which then kind of works, but then when i click the move up sort button enough times the number in my sort field get too small and a question mark is shown in my sortfield and the function cease to work. But don't know if I was supposed to use YourTable::Sortfield instead of SortOrder..?
I meant a field named "sortOrder". If you want to name it SortField, that's fine, It's just the field name of a number field on which you sort your records.
This is supposed to be a field that is never shown on the layout so very small decimal numbers should not be an issue. And it should not just "stop working" so I'm not sure why that would be the case.
The numbers in SortField or SortOrder should start as auto-entered serial numbers or numbers applied to them via Replace Field Contents with the Serial number option. They should not be empty or have duplicate values at the time you use these scripts.
weird, when I used the down button the they start working again. It's seems it's working.
is there a reason for: If [ Get ( RecordNumber ) > 2 ]
not to be: If [ Get ( RecordNumber ) > 1 ]..? so you can move second record to first..?
The calculations compute the midpoint between the sortfield values. of the two preceding or next two records. If there isn't at least two records above the current record, you can't compute the mid point between those two values. If this is the record with record number = 2, the script computes the midpoint between zero and the first record's sort field value to move it to the top.
The same type of logic is used to move a record below the last record in the found set.
BTW, this isn't the only way you might do this. but other methods require changing the sortfield values of two records (by swapping the sortfield values) instead of just updating the value for the current record.
OK it makes sense... I'll work with this for now than you so much again for your help