Creating a dependent Child record. Use of 'this' statement.
I would like to create children records when there is a status change in the parent. I can get everything to populate but the foreign key of the parent.
Volunteer --> Timesheet. When volunteer workstatus = 'Working', create a Timesheet entry.
let v := this.Id; (volunteer parent)
let t := (create Timesheet);
t.(Id := v);
t.('Work Date' := today());
t.('Clock-In Time' := time(now()));
t.('Timesheet Status' := 1)
6 replies
-
Hi
Yes, I've answered this once...
Change let v := this.Id; to let v := this;
If your relationship field in Timesheet is called Volunteer Change t.(Id := v) to t.(Volunteer := v)
Regards
John
-
John, That worked! Thank you. For my future reference, how do I know what the relationship name is? It was indeed t.(Volunteer := v), but in the table it only shows as 'Id'.
-
Follow-up. I need to check for the existence of a 'timesheet' record when a volunteer goes to check in for the day. I am struggling with the syntax and getting it to work. My goal is to (a) check for existing timesheet for the given day, (b) if it exists already, alert volunteer they are checked in, or (b) if it does not, then create the initial timesheet record in the child-Timesheet table.
Here's the code I am using to try and make the check. Variable a works to return the last date for the given volunteer when I run it in the Console. In the After Update Trigger it does not work. It creates a new Timesheet record each time. Ideas to fix it?
let v := this;
let a := last(select Timesheet where Volunteer = this.Id).'Work Date';
let t := (create Timesheet);
if a = date(today()) then
alert("Already checked in!")
else
if text('Work Status') = "Working" then
t.(Volunteer := v);
t.('Work Date' := today());
t.('Clock In' := datetime(now()));
t.('Timesheet Status' := 1);
alert("Timesheet created. Welcome!")
else
null
end
end -
Two things. This statement:
let a := last(select Timesheet where Volunteer = this.Id).'Work Date';
is too complicated. Since there is a reference between the tables, it can be simplified:
last(Timesheet order by 'Work Date').'Work Date'
I added the "order by" clause, just to avoid any problem if, for some reason, some records in "Timesheet" are not correctly ordered.
Then, this statement:
let t := (create Timesheet);
is outside the if statement. It's thus not surprising that a new record is created unconditionally. The statement should at the beginning of the "else" branch of the "if" statement.
-
Alain is right. It's easy to confuse Timesheet the Table and Timesheet the reference field. I tend to rename mine so I'd have TimesheetIDs in Volunteer and VolunteerID in Timesheet. Note one is suffixed ID and the other IDs as this also helps me keep clear in my mind which one is the One and which is the Many
-
Indeed. When creating a reference, Ninox, by default, gives to the field created in each table the name of the other table. I like to rename the fields to avoid any confusion. For example, by prefixing the default name by "to" in the name of the 1:N field, and by "from" in the name of the N:1 field. Sometimes, it is even better to create names that better describe the purpose of the reference.
Content aside
- 3 yrs agoLast active
- 6Replies
- 422Views