0

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

13replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Nick
    • Nick
    • 6 mths ago
    • Reported - view

    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

    Like
    • John
    • KAXIG ApS
    • John.1
    • 6 mths ago
    • Reported - view

    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 :)

    Like
  • 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

    Like
    • John
    • KAXIG ApS
    • John.1
    • 6 mths ago
    • Reported - view

    Wouwww, thank you sooo much :)

     

    It gives me more than I even imagined - and will be very helpful in other types of "process management apps"

     

    Have learned a lot from both solutions. Really appreciated !!!

    Like
  • 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.

    Like
    • John
    • KAXIG ApS
    • John.1
    • 6 mths ago
    • Reported - view

    Thanks Alain,

    the "always shown as 1" issue - is the same on the Mac version.

     

    So this gives me something to play with during the holidays (not sure the mrs.s can see the fun though :)))

     

    But exercises are always welcome :)

     

    btw - "the 1.5 trick"? what is happening there ? 

     

    kr, J-)

    Like
  • 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 🙏 

    Like
  • 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

    Like
  • 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.

    Like
    • John Halls
    • John_Halls
    • 6 mths ago
    • Reported - view

    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

    Like
  • @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

    Like
    • John
    • KAXIG ApS
    • John.1
    • 6 mths ago
    • Reported - view

    Don´t know if this is the correct solution to the "move down exercise" - but it works:

     

    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

    J-)

    Like
    • John
    • KAXIG ApS
    • John.1
    • 6 mths ago
    • Reported - view

    @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-)

    Like
Like Follow
  • 6 mths agoLast active
  • 13Replies
  • 517Views