14 Replies Latest reply on Mar 23, 2011 2:01 PM by philmodjunk

    Working out fines accounting for weekends and holidays

    Brucey

      Title

      Working out fines accounting for weekends and holidays

      Post

      Have posted before on this topic, but got no replies. Help needed please.

      Can anyone solve a problem where I have to work out the number of days late and the resulting fine for an item in a lending library database taking into account weekends and holidays.

      I have the fields:

      Loan_date

      Due_date

      Return_date

      Days_late

      Fine

      An item can be on loan for fourteen days.  If the Due_date falls on a falls on a day when closed (eg weekends or holidays) then the Due_date becomes the date the business opens. A fine of £0.50p per day is charged to the borrowers who return items after the due date. If an item is returned more than fourteen days after the Due_date there is an additional £1.00 charge.

      Examples

      If an item is due back on a Friday and is returned on the following Monday, the fine is £0.50p 

      If an item is due back on the Wednesday the week before a one week holiday, and is returned on the Tuesday after the holiday, the fine is £2.00

      Apologies for being so prescriptive, but I could do with a solution to this one - so any help appreciated.

        • 1. Re: Working out fines accounting for weekends and holidays
          jasongan_1

          Hi Brucey,

          The solution is not hard, but just tedious and needs patience.

          first of all, you can define a calculation field for your due date, based on your 14 days away to the load date. you should also know if the date:

          loan date + 14 is a weekend, so you can extend the due date.

          secondly, you can figure out the days late. ( Returned date - Due date>0).  

          last, you can use a calculation field "fine" to include "days late" and your find policy.

          Hope it helps.

          Jason

          • 2. Re: Working out fines accounting for weekends and holidays
            philmodjunk

            The challenge here is to adjust the days late and resultant fine for days when you are closed. While weekend days are predictable and can be included in the calculation. Holidays are not and almost always change from year to year. I've been able to handle that complication in my system by using a "calendar" table where I record which days the business will be open which will be closed. This is a simple table with one record for each day of the year and a status field that marks it "open" or "closed". A count of the records with status = "open" for a specified date range (from due date to returned date) would give you the adjusted number of days overdue for including in your fine calculation.

            • 3. Re: Working out fines accounting for weekends and holidays
              Brucey

              PhilModJunk

              Could you please be a bit more specific with your solution please.  Calculations and relationships etc.  I get the idea, but don't know how to set it up.

              Regards 

              Brucey

              • 4. Re: Working out fines accounting for weekends and holidays
                philmodjunk

                Define a table with at least these two fields:

                Date (Date)
                Status (Text, auto-enter data: "open")

                In your main table, you'd have these two key fields of type date for the relationship:

                DateDue
                DateReturned

                and a calculation field, cOpenKey, that returns the text "open".

                You'd define this relationship between the two tables:

                Main::DateDue < Calendar::Date AND
                Main::DateReturned > Calendar::Date AND
                Main::cOpenKey = Calendar::Status

                It's not a bad idea to sort this relationship by date in ascending order though in most cases this will be the default order of your records.

                To set up the records in the calendar field, create one record for each day that you are open. If you want to, you can include dates for when you are closed and then use a value list on the status field so that you can easily mark the days your are open and the days your are closed. (This makes it easier to use a script to generate records for the entire year and then you just go through and mark the holidays when you are closed.)

                Then this calculation field defined in Main will count the number of days past due:

                Count ( calendar::Date )

                You can also define a calculation field in the Calendar table to compute a due date if you set up this unstored calculation:

                GetNthRecord ( Date ; Get ( RecordNumber ) + 14 )

                for 14 days open into the future, (add as many days open into the future as you need in place of 14).

                • 5. Re: Working out fines accounting for weekends and holidays
                  Brucey

                  PhilModJunk

                  Followed your set up details.  However, I have encountered the following problem

                  For example:

                  Loan_date = 1/10/2010 (Friday) Due_date = 15/10/2010 (Friday) Date_returned = 18/10/2010 (Monday)

                  The Count (Calendar::Date) returns 2, when I think it should return 1.  One day late

                  I have a portal showing from the holidays tbl, just while I am testing and it is showing two dates, 15/10/2010 and 18/10/2010, both with their status as "Open".

                  If I do it when the due_date falls in a holiday, for example

                  Loan_date = 15/10/2010 (Friday) Due_date = 29/10/2010 (Friday) Days_past_due = 1/11/2010 (Monday)

                  The Count (Calendar::Date) returns 1, when i think it should return 0, because the book is returned the first day back after the holiday. The day after should return 1 and then return 2 the day after and so on.....

                  The portal set up is showing one date 1/11/2010 with a status of "Open"

                  Any suggestions appreciated

                  Also, could you explain what the relationships are doing as I have to try and explain this to my students.

                  Thanks for your time with this.

                  • 6. Re: Working out fines accounting for weekends and holidays
                    philmodjunk

                    looks like the relationship needs a small change. (Using the portal is a very good way to test this by the way...)

                    Main::DateDue < Calendar::Date AND
                    Main::DateReturned > Calendar::Date AND
                    Main::cOpenKey = Calendar::Status

                    That way items returned on the date due are not counted as one day late.

                    Also, could you explain what the relationships are doing as I have to try and explain this to my students.

                    Are these computer students learning database theory? Wink (Otherwise, I don't see the point for sharing this explanation with them. The system is intended to match the procedures you already have in place.)

                    There are three pairs of "match" fields in this relationship. All three fields in a given record in Main must "match" ( as defined with the boolean operators <, >, and =) to a record in Calendar in order for that record to be linked (related) to the record in Main. DateDue and Calendar::Date are date fields. The < (less than) operator means that all matching dates in Calendar::date must be for dates later (greater than) than the date in DateDue. The next clause is much the same. Only dates in Calendar::Date that are earlier or the same as (less than or equal to )DateReturned will be related. Use these two clauses together and a record in Main matches to a range of dates in Calendar from day after DateDue to the DateReturned. The final clause serves as a "filter" to make sure that only calendar records with a Status of "open" are included. Thus, any records in calendar marked "closed" will be automatically excluded.

                    • 7. Re: Working out fines accounting for weekends and holidays
                      Brucey

                      PhilModJunk

                      Tried the change you suggested.

                      This is what happened.

                      Example:

                      Loan_date = 1/10/2010 (Friday) Due_date = 15/10/2010 (Friday) Date_returned = 18/10/2010 (Monday). This works fine.

                      The Count (Calendar::Date) returns 1.  One day late.

                      If I do it when the due_date falls in a holiday again, for example

                      Loan_date = 15/10/2010 (Friday) Due_date = 29/10/2010 (Friday) Date_returned = 1/11/2010 (Monday)

                      The Count (Calendar::Date) still returns 1, instead of 0, as the book is returned the first day back after the holiday. The day after should return 1 and then return 2 the day after and so on.....

                      The portal set up is showing one date 1/11/2010 with a status of "Open"

                      Tried various tweaks with the relationship, but still can't get it to work.

                      Any further suggestions

                      Many thanks.

                      • 8. Re: Working out fines accounting for weekends and holidays
                        philmodjunk

                        I wouldn't calculate a date due as falling on a holiday as your borrower cannot return the item on that date. The first day after the holiday would be the date due and this would eliminate the discrepancy.

                        Please note that the calculation field that I suggested as a possible method for calculating a due date counts the days open--which avoids this issue, but may not produce the due date you want as it skips holidays. (If you allow items to be borrowed for 14 days, the calculation computes 14 open days to return a due date falling on a day when you are open.)

                        • 9. Re: Working out fines accounting for weekends and holidays
                          Brucey

                          I agree, but how would you calculate the date due = the first day after the holiday.

                          • 10. Re: Working out fines accounting for weekends and holidays
                            philmodjunk

                            A good question, I only had time yesterday evening to analyze the issue, not come up with a solution here.

                            A script or recursive custom function could be used to start on the computed due date in the Calendar table and step forward one record at a time until an "open" date is reached. Creating a custom function requires FileMaker advanced.

                            • 11. Re: Working out fines accounting for weekends and holidays
                              Brucey

                              Thanks PhilModjunk for spending so much time on this for me.

                              We have FileMaker Advanced here at my school, so if you do come up with a solution it would be greatly appreciated if you could pass it on.

                              As a matter of interest I have spoke to other teachers trying to solve the same problem. (I am the only one using FileMaker) They are using Access and resorting to using VB code and still struggling. I think the exam board have really cocked up here with this task.

                              Anyway, thanks again.

                              • 12. Re: Working out fines accounting for weekends and holidays
                                philmodjunk

                                I prefer the custom function approach myself, but didn't want to try that route unless it was a viable option for you.

                                Now that I think a little further, I'm not so sure that a CF is a good option as it would need to either traverse a set of records in Calendar or you'd have to pass it a possibly very long list of dates as a parameter. Let's look at a scripted approach first.

                                I don't know if the lending interval is a constant or varies with the type of patron and/or type of item borrowed. In any case, loading the lending interval in a field allows you to change the interval at later date without needing to alter any scripts or calculations that use it. I will assume that you have a field named "lending Interval" in the current table that counts the number of days the item may be borrowed. In reality, this can be a value looked up from another table, a global field or a field from a single related record where you store the intended lending interval.

                                #Script must execute from layout based on table occurrence name "main"
                                Set Variable [$date Due ; Main::Loan date + Main::Lending Interval]
                                Freeze Window
                                Go To Layout [Calendar]
                                Enter Find Mode[] //clear pause check box here
                                Set Field [Calendar::Date ; "> " & $date due]
                                Set Field [Calendar::Status ; "Open"]
                                Set Error Capture [on]
                                Perform Find[]
                                Sort [Restore] //specify ascending order for Date, not needed if records are created in ascending order
                                Loop
                                   Exit Loop If [Calendar::Status = "Open"]
                                   Go To Record/Request/Page [Next ; Exit after last]
                                End Loop
                                If [ Calendar::Status = "Open"]
                                   Set Variable [$date due ; Calendar::Date ]
                                   Go To Layout [original layout]
                                   Set Field [Main::due_date ; $date due]
                                Else
                                   Go To Layout [original layout]
                                   Show custom dialog ["Error in calendar table. No date open after specified date interval could be found."]
                                End If

                                • 13. Re: Working out fines accounting for weekends and holidays
                                  Brucey

                                  Ended up with a work around in the end.  I put a -1 after the count hoiidays calculation

                                  Count ( Holidays_tbl::Holiday_date ) - 1.  The script above was a bit beyond my students.

                                  Would you be able to suggest a calculation/script that limits a borrower to two loan books at any given time?

                                  • 14. Re: Working out fines accounting for weekends and holidays
                                    philmodjunk

                                    You should have a table where each record represents a book that has been loaned, with BookID, date loaned, date due and BorrowerID. Linking the table of Borrowers to this table by BorrowerID would allow you to set up a count function that counts all loaned books.

                                    Count ( booksLoaned::NotReturned ) (NotReturned is a field that can be set to blank when the book is returned so that only non returned books are counted.)

                                    Sounds like you are having your students create the database. If so, wish I were there to work with them on it. That would be a lot of fun...