0

Default value for subtable reference field.

I have a table Camera with subtable Lenses. I have added a reference to Lenses to the Camera view and can select from a list of lenses. I would like the reference to default to a particular Lens instead of being empty. Any ideas on how this can be done? 

13 replies

null
    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    Hi Chris,

    You must initialise your reference to Lenses in Carmera when a new record is created. To do this, set a default value for your lens on the "Trigger on creation" function in the definition of the "Camera" table.
    Example : Lens := first(select Lenses [LensName=XXX])

    • Chris_Mullett
    • 3 yrs ago
    • Reported - view

    Thanks Jacques. That works fine, but onlly when xxx is a fixed value. It needs to be depenadant on the value in the Camera field. I was using something similar:

    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    Lens = MyLensID;

    But this doesn't work. However if I put:

    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    Lens = MyLensID;
    Lens

    In a formula field it returns the correct value. It seems as though the trigger event doesn't recognise the path and where criteria have to be constants. This can't be correct, can it? I've tried all sorts of variations without success. The fact that it works as a formula suggests to me that the syntax is correct, so why doesn't it work in a trigger?

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    There miss ":" in last line 

    Lens := MyLensID;

    • Chris_Mullett
    • 3 yrs ago
    • Reported - view

    Jacques, thanks, I feel a dope for that. but adding the : to make:

    let MyCriteria := 1;
    Lens := first(select LensPairings where Type > MyCriteria);
    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    Lens := MyLensID

    gives an error code Expression does not return a record Id of table 'LensPairings':MyLensID at line 5, column 16. MyLensID does return the number 15, which is a valid Id for the table.

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    'Select Lens' is a pointer to which table in  first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens' ?

    • Chris_Mullett
    • 3 yrs ago
    • Reported - view

    It is me being a complete novice with Ninox., it is the name of a field in LensPairings.

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    As I understand it, 'Select Lens' is the ID of another record in the same LensPairings table.

    Is 'Select lens' a number or a table pointer?
    If it's a number, you must use select to find the record whose ID is 'Select lens':
    Lens := first(select LensPairings where ID = 'Select Lens');

    Why do you assign a value to Lens twice in this code (line 2 and 5)?

    • Chris_Mullett
    • 3 yrs ago
    • Reported - view

    Thanks for your patience Jacques. The first assignation is redundant, was there withe an alert to test code, I deleted the alert but forgot the assignation line.

    I now have a trigger:
    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    Lens = MyLensID
    and a Function
    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    Lens = MyLensID;
    MyLensID
    The function generates a valid result in the field, the trigger doesn't return anything visible.

    Since the function work I assume the syntax is correct and returns a correct value. The Problem is that the trigger does not assign the value to the Lens field which is the subtable reference.

    I think this is the question I should have asked in the first place!

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    No problem Chris. There are people who help me too and they have a lot of patience with me 😁.

    In the trigger and in the function, the ':' at line: Lens := MyLensID is missing. This is probably why the value is not assigned to the field.

    • Chris_Mullett
    • 3 yrs ago
    • Reported - view

    Jacques, if I have:

    Trigger

    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    Lens := MyLensIDLens

    I get the error "Expression does not return a record Id of table 'LensPairings':MyLensID at line 3, column 16.

    Formula

    let MyCameraID := Sessions.Camera.Id;
    let MyLensID := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1).'Select Lens';
    MyLensID

    Works fine.

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    Could you send me the definitions (screenshot) of LensPairings and Camera by e-mail ? jacques.tur@neuf.fr.

    ps : the last trigger line has the wrong name of var MyLensID : Lens := MyLensIDLens. I guess it's a copy error?

    • Chris_Mullett
    • 3 yrs ago
    • Reported - view

    Hi Jacques,

    Not sure if you have done anything with this yet but I have figured a workaround I had tunnel vision on trying to set the default in the trigger, but it finally occurred that I could set it using after update on one of the other fields. Obvious, just check that is hasn’t already been set so after update trigger on other fields set to:
    if Lens = null then
    let MyCameraID := Sessions.Camera.Id;
    Lens := first(select LensPairings where 'Select Camera' = MyCameraID and Type > 1)
    end
    Works a treat. It would still be nice to know why the trigger on create doesn’t work.

    Thanks again for your help.
    Regards
    Chris

    • Ninox developper
    • Jacques_TUR
    • 3 yrs ago
    • Reported - view

    Hi Crhis, I recieved your screenshot.
    I not saw Sessions.Camera.Id on form. Where is ?

    I think the right way is use Camera.ID of Film : let MyCamera := Film.Camera.ID.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 13Replies
  • 931Views