Conditionally counting records based on field on new record

Question

The setup:Employees { Timesheet_Employees&nbsp;(1:M)&nbsp;{ Employee Records &nbsp;(1:M)EMPLOYEES&nbsp;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.&nbsp;.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. &nbsp;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 &gt; 0. I just can't seem to figure out how to execute it.Any thoughts on how to do this? &nbsp;Or any suggestions on a better approach?Thanks in advance.

eric_j_hansen · Answer

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 · Answer

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 · Answer

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 · Answer

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.

Forum Discussion

Conditionally counting records based on field on new record

4 Replies

Related Content

Duplicate Record Checker

Custom Record Titles

Using Grid Edit to Edit Multiple Records at Once

Cant See Associated Records

Record Details