1

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

null
    • Daniel_Berrow
    • 1 yr ago
    • Reported - view
    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

      • John_Wilmans
      • 1 yr ago
      • Reported - view

      hey  . Thanks for the help, man. Unfortunately, this doesn't help. I have got a couple of errors; the one that is saying now is: "Function is not defined. Contains (multi, string)." I'm guessing that's related to me using the drop-down combo box and then trying to mix it with a string from the Weekday Name?? 

    • Daniel_Berrow
    • 1 yr ago
    • Reported - view

    try t.text('Work Days')

    • John_Wilmans
    • 1 yr ago
    • Reported - view

    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.

    • Daniel_Berrow
    • 1 yr ago
    • Reported - view

    I missed off a close bracket

    contains(t.text('work days'), text(weekdayName(date))) = false

    Give this a try

      • John_Wilmans
      • 1 yr ago
      • Reported - view

       Thanks for your help mate. All sorted

Login to reply

Content aside

  • Status Answered
  • 1 Likes
  • 1 yr agoLast active
  • 6Replies
  • 72Views
  • 2 Following