A key missing detail needed to suggest detailed solutions is what tables you have set up and how you have linked them in relationships. The first script seems to show that you are linking employee records by employee name and this is not ideal. Employee names are not unique, employees change their names and they are vulnerable to data entry errors--all of which cause issues with your database that are avoided if you do not use their name to link records in that table to records in other tables. An auto-entered serial number is the better choice for such.
This doesn't look like something that should be set up with a script. A calculation based an correctly defined relationships should work for this and since it's a calculation, it shouldn't need an additional script to put back the data into previous values when the request is not approved.
Thanks for your reply. I have 2 tables one for Holiday_Requests and the other is Employee_Details and yes they are related through Employee name as I have two differtent ID fields , one that auto generates a number when I employee is added to the holiday request database and another ID field in the Holiday_Request table for when a request is added...so because each ID generated for each table is different I did want the tables related through the ID field. I needed two fields that hols the same name.
I have managed to create new calculated fields instead which work perfect for this now...so still does what I needed which is look at status "Rejected" or "Accepted" and then will recalculate Taken and Remaining;
This is from Holiday_Request table, I created a new field 'Total Days' If(Rejection Status = "Yes"; 0; TotalWeekDays)
From Employee Table against fields: Holiday Remaining Holiday_Allowance - Sum (Holiday_Requests::Total Days)
Holday Taken Sum (Holiday_Requests::Total Days)
I have attached some screen shots to show what ive done...actually works really well!
I have 2 tables one for Holiday_Requests and the other is Employee_Details and yes they are related through Employee name as I have two differtent ID fields , one that auto generates a number when I employee is added to the holiday request database and another ID field in the Holiday_Request table for when a request is added...so because each ID generated for each table is different I did want the tables related through the ID field. I needed two fields that hols the same name.
Ummm How's that again? I'm afraid that really doesn't make sense to me when I read it. And you still should match an employee ID number in the employee table (auto-entered serial number) an employee number in the holiday requests table (normal number field). The fact that you might also define an auto-entered serial number in the Holiday requests table might be useful, but is not a field that would be used in this relationship. It would be used for other purposes.
Using just what you have now, if you added this calculation field, named cApprovedHolidayDays to Holiday Requests:
If ( Rejection Status = "Approved" ; Date_to - Date_From )
Then a summary field in the same table could be used to compute and return the Total days requested--including week end dates. There are a number of different approaches that can adjust that total to omit holidays and weekends. My preference is to use a calendar table where both weekend and holiday dates are recorded. A relationship to that table can return the number of holiday and weekend days in a date range to be subtracted from this total. A script can automatically generate the week end dates so that all a user need do is add any additional holiday closure dates to the same table.
Define this relationship:
HolidayRequests::Date_From > DaysClosed::Date AND
HolidayRequests::Date_To < DaysClosed::Date
Then the above calculation can be adjusted to be:
If ( Rejection Status = "Approved" ; Date_to - Date_From - count ( DaysClosed::Date ) )
And now your summary field total will reflect the total approved holiday days.
Thanks again for your reply; I will try out the daysclosed! And when its working ill re-post the results!
Sorry if my messages aren't very clear or my way of working, I've only been using filemaker for less than a month and never done scripting before either. So im literally trial and error with anything I do.
Im saving versions as i go along though in case I completely mess it up :)
Ill repost again when ive tried your suggestions. Thanks Toni
Hi Phil, I created a new table 'Calendars', field 'DaysClosed', added public holidays and then just added to a script to also look for daysclose as well as weekends and dates added incorrectly...see screen shot! thanks again for your advice.