10 Replies Latest reply on Oct 28, 2013 2:40 AM by pook

    script for related fields in a portal - Holiday allowance calculation

    pook

      Title

      script for related fields in a portal - Holiday allowance calculation

      Post

            

            

           Hi There,

           I need some help writing a script that'll calculate holiday allowance. Ive created a database in Filemaker that shows Holiday Allowance,Holiday Taken, and Remaining Holiday. This is all displayed in a portal. Ive written a script with help from a colleage that also omit weekend days. so the holiday only calculates weekdays, though havent been as clever to also omit public holidays...but ill figure that out later.

           You can see this script attached to this post. I have created a submit button which also runs a script to deduct from fields holiday taken and recalculate holiday remaining when choosing a date range for the requested holiday. The idea is that a manager can then go into this request and choose to approve or reject the holiday. Once approved status will change to approved and change to rejected if rejected. But I need a script that'll recalculate the holiday taken if rejected and reduce holiday taken and back to holiday remaining, perhaps a script that'll no to do this when status says rejected. 

           Im new to Filemaker and new to writing scripts so would really appreciate some help. Please let me know if I need to provide more information. see attached files for more information also.

      Holiday_request.PNG

        • 1. Re: script for related fields in a portal - Holiday allowance calculation
          pook
          /files/3b4dba07ce/Submit_holiday.PNG 520x231
          • 2. Re: script for related fields in a portal - Holiday allowance calculation
            philmodjunk

                 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.

            • 3. Re: script for related fields in a portal - Holiday allowance calculation
              pook

                   Hi Phil,

                   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!

              • 4. Re: script for related fields in a portal - Holiday allowance calculation
                pook
                /files/5f330b915e/Holiday_Requests.PNG 792x344
                • 5. Re: script for related fields in a portal - Holiday allowance calculation
                  pook
                  /files/31d6204173/Employee_Details.PNG 786x405
                  • 6. Re: script for related fields in a portal - Holiday allowance calculation
                    pook
                    /files/d3a7c1df82/Relationship.PNG 578x356
                    • 7. Re: script for related fields in a portal - Holiday allowance calculation
                      philmodjunk
                           

                                 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.

                           Example:

                           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.

                      • 8. Re: script for related fields in a portal - Holiday allowance calculation
                        pook

                             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

                        • 9. Re: script for related fields in a portal - Holiday allowance calculation
                          philmodjunk
                               

                                    Im saving versions as i go along though in case I completely mess it up :)

                               Then you might find this thread to be of interest: Saving Sequential Back Ups During Development

                          • 10. Re: script for related fields in a portal - Holiday allowance calculation
                            pook

                                 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.