Default value for subtable reference field.

C

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? 

Ninox Profile

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

C

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 Profile

There miss ":" in last line 

Lens := MyLensID;

C

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 Profile

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

 

C

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

Ninox Profile

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

C

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 Profile

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.

1 2
Reply