Auto increment in subtable

Try this in the BEATS subtable (formula field):

let myID := number(Id);
let myVIDEOS:= VIDEOS;
if VIDEOS then
cnt((select BEATS)[VIDEOS = myVideos and number(Id) <= myID])
else
0
end

Hi John,

I made an example of what I think you want. I even added 2 buttons to reorder your beats up or down.

Link : https://www.dropbox.com/s/hvrygvn9t6uw4ly/RenumberBEATS.ninox?dl=0

Steven

I loaded the proposed solution in the iPad version of Ninox, and, unfortunately, the new records in the "BEATS" table are always numbered "1". I am not surprised, because I already encountered the issue in that version. The trick to overcome the issue is to act, not on the creation of the record, but as the "Trigger after update" of the "VIDEO" reference field in the "BEATS" TABLE.

Now, please bear with my personal crusade against the use of the "select" function when there is another, faster path... With the references in place, the records of the "BEATS" table linked to the same record of the "VIDEO" table can be gathered by the simple expression "VIDEO.BEATS". So, in the "Trigger after update" of the "VIDEO" reference field in the "BEATS" table, one can simply put:

ORDER := max(VIDEO.BEATS.ORDER) + 1

Similarly, the formula to fill the "BEATS IN THIS VIDEO" view can be reduced to:

VIDEO.BEATS

And the same applies to the scripts attached to the buttons. The trick with the "1.5" value is superb, but renumbering all the records has the inconvenient of touching them all, and thus disturbing the value of the _md and _mu fields of records that are not affected by the move. So, I would propose, for the "Move Up" button, a script like:

ORDER := ORDER - 1.5;
let c := 1;
for i in VIDEO.BEATS order by ORDER do
i.if ORDER != c then ORDER := c end;
c := c + 1
end

The script for the "Move Down" button is left as an exercise.

Well Alain, you did it again! You are truly a master. This is why I love this forum. We always learn something from each other. Well done and thanks  

I will have to unlearn the habit of using a select function and make more use of Alain's solution when the tables are linked. Similar to the view in the subtable, I can place in the VIDEOS table a view with formula: this.BEATS

The "select" function is so much slower than following references that it is indeed a good practice to avoid it when possible. In a small database, the difference will not be sensible, but, as the number of records grows, it will start to hurt.

To fill the view, the formula can be simply "BEATS". While prefixig with "this." does not do any harm, it is generally useless. "this" represents the record that is current in the active context. Which means that writing "someField" or "this.someField" always designates the named field in the same, current record. What makes "this" useful is that its value can be stored in a variable. The variable can then be used to access the saved context when the active context has changed.

Hi John

The 1.5 trick gives it a temporary sort order value between the one above and the one above that, before all being re-assigned a new order value. Lets move No 4 up one

Old -> Temp -> New

1 -> 1 -> 1

2 -> 2 -> 2

3 -> 3 -> 4

4 -> 2.5 -> 3

5 -> 5 -> 5

Regards John

@John: By "the 1.5 trick", I mean the neat idea of adding or substracting the value "1.5" to/from the value of the "ORDER" field in the record that must be reordered, in order to make it fall in between the two records preceding or following it when sorted on that field. One drawback of that approach is that it only works if the records are numbered with a regular increment of 1 before running the script. Indeed, they are always numbered correctly after each run of the script, but if a record is deleted, or a number modified manually, the procedure may fail. In order to give you more fun during the holidays, here are proposal of scripts using an other approach to play with.

Move Up:

let o := ORDER;
let p := last(VIDEO.BEATS[ORDER < o] order by ORDER);
if p then
ORDER := p.ORDER;
p.(ORDER := o)
end

Move Down:

let o := ORDER;
let p := first(VIDEO.BEATS[ORDER > o] order by ORDER);
if p then
ORDER := p.ORDER;
p.(ORDER := o)
end

It is still possible to fool those scripts by engineering pathological cases in the numbering. Oh, and maybe a full renumbering could sometimes be necessary:

let c := 1;
for i in VIDEO.BEATS order by ORDER do
i.if ORDER != c then ORDER := c end;
c := c + 1
end