
Auto increment in subtable
Hi guys,
once again I'm stuck :)
I know how to auto increment values in a fild - but this is different.
How can I auto increment a value in a "Child table" - ONLY regarding the actual records in the "Parent table"
I have a Parent-table: "VIDEOS"
In "VIDEOS" I have a subtable "BEATS", and would like to auto increment in the field BEATS.Order
Any ideas?
Thanks,
John
-
Thanks a lot Nick,
it works exactly as you intended - and I, learned that it's important to be clear when I ask questions in here - in order not to waste peoples time :))The thing is, that sometimes I have to change the order of the beats as I write the video scripts - which is why I have an "order" field to sort by inside BEATS.
What I have in mind is something like this:
On creation:
If no BEAT records exists, (for this VIDEO), create record and set the value of the ORDER field to "1"
If BEAT records exist, create new BEAT record and set the value of the ORDER field to max+1 (for this particular VIDEO)
Hope this makes better sense :)
-
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. -
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.
-
@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
-
@John: Thanks for the explaination. It makes perfect sense :)
@Alain: you describe exactly what could be the problem when writing scripts - sometimes a BEAT will be deleted - so I'll defenitely have a play with your code :)
btw.: I would like to have a better understanding of the coding principles in Ninox - but can't find any manuals describing the logic. Would playing with / studying JS be helpful you think ?
J-)