Parent - Child Record Update Example. Code Help to Complete Needed.
I hope this will be enlightening for others, in addition, to help to me. Thank you in advance. I appreciate the help I’ve received. Nonetheless, I am working in circles. Perhaps if I lay out the use case, experts can fill the gaps.
We do social service. Our workforce is ‘volunteer’ with quite a bit of turn-over through time. For many reasons, we need to track attendance and work hours.
Work Flow -- PROCESS 1
A volunteer comes to our work location and ‘signs in’. IF the volunteer doesn’t exist, they create themselves at the kiosk. The volunteer information is captured in a simple form.
VOLUNTEER [table / fields]
ID
Name
Email
Work Status (values: Not Working, Working, Work Complete)
The Volunteer assigned the initial ‘Work Status’ of “Not Working.
PROCESS 2
The Ninox display form is in the KANBAN VIEW. The volunteer finds him/herself in the list of ‘active’ volunteers in the NOT WORKING list (Kanban view tied to Volunteer.Work Status.).
Volunteer drags card from “Not Working” to “Working”
After Update Trigger: This action triggers the creation of a TIMESHEET record for that volunteer.
TIMESHEET [table / fields]
>ID
>Work Date
>Clock in Time
Clock Out Time
Hours Worked
>Timesheet Status (‘Clocked In’, ‘Clocked Out’)
Record create = ID, Work Date, Clock In Time, Sets Timesheet status = Clocked In.
A volunteer can have only 1 timesheet in a single day.
IF they can’t find themselves on the list, they go to PROCESS 1.
IF they have already clocked in, the alert says “Already Clocked In”
Code Block 1-create volunteer timesheet [note: my code creates the record and fields, but the ‘check for today’ logic doesn’t work]
—-
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
—-
PROCESS 3
From NINOX KANBAN view. Volunteer drags card from ‘Working’ to ‘Work Complete.’
After Update Trigger: This action triggers the update of the timesheet.
Record update = today’s record: Clock Out Time, Hours Worked is calculated, Timesheet Status = Clocked Out.
Code Block 2-Check Volunteer out. [doesn’t work. updates ALL timesheet records for the volunteer]
—
let a := last(select Timesheet where Volunteer = this).'Work Date';
if a = today() then
Timesheet.('Clock Out' := datetime(now())) and
Timesheet.'Timesheet Status' = 2
end
—
Code Block 3 -- email volunteer record of timesheet and hours worked.
If an email for a volunteer is populated, an email is automatically sent with a record of the timesheet (volunteer name, date, start time, end time, hours worked.)
NO CODE BLOCK [I have not written this code yet]
PROCESS 4.
Code Block 4 -- Reset Volunteer Work Status. Schedule based. Not sure how to write this one either.
Every day the Volunteer work status is reset to “Not Working”
17 replies
-
In Code Block 1:
let v := this;
let a := last(select Timesheet where Volunteer = v order by 'Work Date').'Work Date'; <--Since Volunteer is a reference field you only need to set it to your this variable then you can sort by Work Date so you make sure you get the most recent. Or another way is to use your Status field. A volunteer should only have one Timesheet with a status of 1 at any given time, so you can just search for Status =1.
let t := (create Timesheet); <-- see your other thread for the fix for this.This is my sample code in a my volunteer db that I put in a button.
let curRec := this;
let xStatus := ((select Timesheet where Volunteer = curRec and Status = 1) order by WorkDay).WorkDay;
if xStatus = date(today()) then
alert("You have already checked in")
else
let t := (create Timesheet);
t.(Volunteer := curRec);
t.(ClockIn := datetime(now()));
t.(Status := 1);
alert("Timesheet Created! Welcome!")
end -
Code block 2:
You should be using the variable a in your field references instead of Timesheet. I think that is the issue as you are telling it to use the whole table Timesheet and not the single record in "a". Which means we need to modify you let a statement so it is not tied to only Work Date.
let a := last(select Timesheet where Volunteer = this order by 'Work Date'); <-- removed the 'Work Date' field specific so you can use the record reference later. also add the order by to makes sure you get the latest version
if a.'Work Date' = today() then <--this is where we add the Work Date check
a.('Clock Out' := datetime(now())); <--Now we can also reference other fields from a and not be stuck with only Work Date, also added the ";" to separate the the two fields that need to be updated
a.('Timesheet Status' := 2) <--added the ":"in front of the equal so it will set the field to 2.
end
-
Fred, Thank you. RE Code Block 2: I think I set it up as you suggested, but I get a "Field not found: a at line 2, column 4." Can you see what the issue is?
let a := last((select Timesheet where volunteer = this order by 'Work Date');
if a.'Work Date' = today() then
a.('Clock Out' := datetime(now()));
a.('Timesheet Status' := 2);
'Work Status' := 3
else
if 'Work Status' = 3 then
alert("Already clocked out. Thank you!");
end
end -
let a := last((select Timesheet where volunteer = this order by 'Work Date'); <-- too many open parenths, just get rid of one of them
-
If you really want the alerts then they can NOT be in Triggers. They have to be in buttons cause they have to be run client side. Triggers run on the server side.
-
Fred, I really appreciate the help. I just can't get it to work. The CREATE code for Timesheet works. The only difference from your code is the reference to 'status' in the xStatus variable. I got an error "can't find status' so I prefixed it with curRec (this). Code as follows:
---
let curRec := this;
let xStatus := ((select Timesheet where VolunteerID = curRec and curRec.Status = 1) order by WorkDay).WorkDay;
if xStatus = date(today()) then
alert("You have already checked in!")
else
let t:=(create Timesheet);
t.(VolunteerID := curRec);
t.(WorkDay := today());
t.('Clock-In' := datetime(now()));
t.(TStatus := 1);
Status := 2;
alert("Timesheet Created! Welcome!")
end---
The UPDATE doesn't work. If I prefix the fields with the variable as shown below, it updates the Status but not the Clock-out date-time. It leaves them blank. If I change the variable to a table reference of Timesheet, it UPDATES all the Clock-out records for the given Volunteer.
---
let curRec := this;
let a := last((select Timesheet where VolunteerID = curRec) order by WorkDay);
if a.WorkDay = today() then
a.('Clock-Out' := datetime(now();
a.(TStatus := 2);
curRec.(Status := 3)))
else
if Status = 3 then
alert("work day complete")
end
end----
-
Hi
I have put a version together.
I have added an OldStatus exactly as Status to track where the card has come from, and another Reference filed that just holds the current timesheet. The Trigger after update script will only allow the volunteer cards to go between allowed statuses.
Regards
John
-
In addition to this
The Volunteer Trigger on create needs
Status := "Not Working";
OldStatus := "Not Working"The second relationship CurrentTimesheetID is the other way round to TimesheetID, one timesheet can have many volunteers (even though it only ever has one).
Also I would move the destruction of this relationship CurrentTimesheetID :=0 to OldStatus = 3 and Status = 1 this way you can display the log in and log out times on your Kanban Board if you wish.
Regards
John
-
-
Here is my code:
let curRec := this;
let t := last((select Timesheet where Volunteer = curRec) order by WorkDay);
if t.WorkDay = today() then
t.(ClockOut := datetime(now()));
t.(Status := 2);
WorkStatus := 3;
alert("Thanks for helping today. See you soon.")
endThe only thing I can see is that you may have parenthesis in wrong places:
a.('Clock-Out' := datetime(now()));<--add two at the end so it closes this line. with the line closed properly this may fix the Status line...
a.(TStatus := 2);
Status := 3; <-- now this should work like in my exampleAlso testing for WorkDay first may not work in all circumstances. What if they have checked out today, then currently the if statement will check them out again. An easy solution would be to test for Status first then check for WorkDay? Or you can do what John has developed.
-
John,
Thank you very much for your answer. I recreated as you've laid it out, but ran into a problem. The On Update trigger is setup as you did, but I get a 'Field Not Found error.. see below. Thoughts?
-
Fred, The first time through the process everything works. If I try to check out multiple times, the code gives me the correct message "work date complete."
However, if I try and check in again for the same volunteer on the same day, based on the code, it should not allow it.
let xStatus := ((select Timesheet where VolunteerID = curRec and curRec.Status = 1) order by WorkDay).WorkDay;
if xStatus = today() then
alert("You have already checked in!")However, it is creating a new record each time I transact. I currently have create code in a button Check In and then a Check Out button. I am using buttons to test. The second time through the Check Out code fails.
-
Hi
You need an extra right hand bracket in the line above, so it becomes
t.(In := datetime(now()));
But you don't need the datetime function. just use
t.(In := now());
Regards John
-
John: Great! This is working. However, I can put the same person through the process for the same day. For one day, a volunteer should only have one timesheet. Did you envision a way to do that?
Also, on the Kanban view: Is there any way to eliminate the column called "(empty)"? Technically this should not be possible. Same might be said for the "+Add value" column. thoughts?
-
Re the Empty column, in the Status field change Required to Yes.
More coming re the rest...
-
You wrote:
let xStatus := ((select Timesheet where VolunteerID = curRec and curRec.Status = 1) order by WorkDay).WorkDay;
if xStatus = today() then
alert("You have already checked in!")1) in the first line you need to do add last, because this is a Many to 1 relationship you can theoretically get many records for your search so you have to specify you only want one.
2) for proper syntax, you can't use curRec at the beginning of a filter in a select. You use curRec.Status = 1. In the Timesheet table there is no reference to curRec and I don't know if Ninox knows which relationship to use. Since you want to reference the Status field from the Volunteer table you would type:
VolunteerID.Status = 1 <--I'm guessing that VolunteerID is the name of the reference field in Timesheets
3) Since the Timesheet is a child of the Volunteer, whatever the VolunteerID.Status is currently equal to is updated in all of the records of the volunteer in the Timesheet table. If you show the Volunteer.Status field in your Timesheet table you will see it change whenever you update the Status of the Volunteer. So I don't think using the Volunteer Status field is a good way to check if they are set to not working.
You can do a Display field only if, to hide the checkin button if their Status = 2. You can do a similar thing for checkout to hide if Status = 1. That way people can't accidentially do things they shouldn't and less error correction needed in your code.
-
I would use a Reset button but first you need to update the Trigger after update so that this allows the Reset button to work
if OldStatus = 1 and Status = 2 then
OldStatus := Status;
let v := this;
let t := (create Timesheet);
t.(VolunteerID := v);
t.(In := now());
CurrentTimesheetID := t
end;
if OldStatus = 1 and Status = 3 then
Status := 1
end;
if OldStatus = 2 and Status = 1 and CurrentTimesheetID != null then
Status := 2
end;
if OldStatus = 2 and Status = 3 then
OldStatus := Status;
CurrentTimesheetID.(Out := now())
end;
if OldStatus = 3 and Status = 1 and CurrentTimesheetID != null then
Status := 3
end;
if OldStatus = 3 and Status = 2 then
Status := 3
endAdd this to your reset button
let t := (select Volunteer);
t.(CurrentTimesheetID := 0);
t.(Status := 1);
t.(OldStatus := 1)The trigger doesn't allow the Volunteer to move from 2 to 1 or from 3 to 1 if there is a CuurentTimesheetID. The reset button removes this and so all the records can be set to 1.
I'm stumped on not having + Add Value in the Kanban view
Regards John
Content aside
- 3 yrs agoLast active
- 17Replies
- 1079Views