Parent - Child Record Update Example. Code Help to Complete Needed.

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

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.

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.")
end

The 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 example

Also 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.

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

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
end

Add 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