Forum Discussion

AdminEntry's avatar
AdminEntry
Qrew Cadet
8 years ago

Conditionally counting records based on field on new record

The setup:

Employees { Timesheet_Employees (1:M) { Employee Records  (1:M)

EMPLOYEES 
Employee_Name,
etc. (this table captures basic employee info.)

TIMESHEETS (This table captures information about the shift being worked. The header portion of a timesheet)
Timesheet_Date,
Supervisor_Name,
etc...

TIMESHEET_EMPLOYEES (children records to TIMESHEETS and to EMPLOYEES. Each Employee may be part of one or more Timesheets.) There is a Many-to-Many relationship between TIMESHEETS and EMPLOYEES through this table. This table captures a record for each shift that the employee works. One employee may work more than one shifts in any given day.
Related_Timesheet,
Related_Employee,
Start_Time,
End_Time,
Per_Diem, etc. 
.
The Per Diem field is a Boolean field. Employees are only allowed to have 1 per diem on any given day that they work (only one Per Diem per day regardless of how many shifts they work).

So if I'm the employee, I may be working the AM shift with my supervisor Paul, and he gives me my per diem for the day (he checks the per diem box on my employee record for this shift). Then in the evening - same day - I may work another shift, this time with supervisor Ryan. Ryan doesn't know that I already received my per diem for the day, so he checks the per diem box on my employee record for the evening shift. Now I have 2 per diems in one day.

I need to be able to determine when an employee (in EMPLOYEES) has been given the per diem by his supervisor for the day, so that he doesn't accidentally get another per diem on the same day from another supervisor.  

My thoughts so far revolve around conditionally counting the number of per diems the Employee has where the date of the record (not the Date Created, but the Timesheet Date) is equal to the date of the current timesheet, and then use a form rule to hide the per diem box when that count is > 0. I just can't seem to figure out how to execute it.

Any thoughts on how to do this?  Or any suggestions on a better approach?

Thanks in advance.

4 Replies

Sort By
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    You would need to pass timesheet_date down to the timesheet_employees table. 

    then, do a summary field between employees and timesheet_employees, counting the number of records where timesheet_date = today and per diem is true. Then, pass that summary back down to the timesheet_employees table as a lookup field. 

    In your form rule, you would do something like: if [employees - # of per diems today] > 0 hide [per diem]. 

    That should work. 

    Let me know if you run into trouble.

    Thanks,

    Eric
  • AdminEntry's avatar
    AdminEntry
    Qrew Cadet
    8 years ago
    Thanks Eric, but this would only work if the Timesheet was actually being created "today" (meaning the actual date of work.  Unfortunately due to the nature of the work, timesheets are sometimes created days later.

    I have to keep trying.

    Thanks again.
  • Eric_J_Hansen's avatar
    Eric_J_Hansen
    Qrew Cadet
    8 years ago
    ok - yea, that makes sense. this is tricky! I am thinking that some javascript will be required here because the native way to do it would probably require a days table and a employee_days table in order to summarize per diems given for any given employee on a given date. With a javascript solution, it could query the other timesheet_employee records for a timesheet and allow or disallow per diems.
  • AdminEntry's avatar
    AdminEntry
    Qrew Cadet
    8 years ago
    Yes. I had already implemented the third table approach but it was just looking too cumbersome and clumsy. As of now I don't see a native way to easily accomplish this. But who knows? It may come to me.

    Thanks again.