Work out annual leave days.
Good morning. I hope you brilliant peeps would be able to help me out again please.
I am working on the tables in my database for staff. I am trying to work out annual leave. I have 3 tables. Staff, Timesheet and Leave requests. They are all related to the Staff table.
Staff has 3 fields that I am interested in: Annual Leave days(Integer), Start Date(Date), Work days(Multiple choice combo box). So you could say that person a joined on 1May 2023, has 28 days leave and works Tuesday to Saturday)
Timesheet is a timesheet table. It has fields for Name, Date, Time In, Time Out
Leave Requests has Name, and an appointment field for the leave dates.
I will add screenshots of this below.
Now, In my company, let's take me. I work Tuesday to Saturday. If I do work a Monday or a Sunday, I get those days back as annual leave. I enter the days I worked on the Timesheet.
What I'd like to try and set up is a formula that will look at the start date and do a search through the timesheet for all the Mondays and Sundays I've worked this year (between 1 May 2023 and 30 Arpil 2024), find how many of those days I've worked and subtract that from my remaining annual leave days.
Is this possible? I've been trying to figure out using weekdayName or one of those other functions how to do this but have come up with a blank.
Thanks for any help incoming. It really is appreciated!!
6 replies
-
let t := this; let cnt := cnt(select Timesheet[date > date(1, 5, 2023)][date < date(30, 4, 2024)][contains(t.'work days', text(weekdayName(date)) = false])
I think this will provide what you're looking for? If not let me know
-
try t.text('Work Days')
-
Thanks . Apologies for the delay in replying, work has been MAD! I’ve only just got to play again. Unfortunately I am STILL getting an error message with this code. Function is not defined. Contains(multi, Boolean) at column 134. Is this still related to the Work Days thing you think?
Thanks for the help mate. I think we are REALLY close on this.
-
I missed off a close bracket
contains(t.text('work days'), text(weekdayName(date))) = false
Give this a try
Content aside
- Status Answered
-
1
Likes
- 11 mths agoLast active
- 6Replies
- 72Views
-
2
Following