2 Replies Latest reply on Dec 15, 2012 9:00 AM by philmodjunk

    If 'Bank Holiday', charge Double rate

    brian.curran

      Title

      If 'Bank Holiday', charge Double rate

      Post

           In MS Access, I had a table consisting of Holiday dates:

           25/12/2012
           26/12/2012
           01/01/2013
           Etc.

           Whenever a Visit occurred on one of these dates (some form of VLookUp), the billing amount for that Visit would double. Would FM need something similar to record these dates?

           Thanks
           Brian.

        • 1. Re: If 'Bank Holiday', charge Double rate
          brian.curran

               I added the Table as above and created a relationship to Visits then used:
               If ( Visits::CalledDate = BankPublicHolidays::Date ; 2 ; 1 )

               Seems to work as desired but keen to hear of any better methods...

               Thanks
               Brian.

          • 2. Re: If 'Bank Holiday', charge Double rate
            philmodjunk

                 Exactly the same related table may be used in FileMaker.

                 Define a table of Holiday dates and relate it by the date field.

                 YourMainTable::DateFIeld = Holidays::DateFIeld

                 In field options for your rate table add an auto-enter calculaiton such as:

                 If ( Holidays::DateField ; Holidays::HolidayRate ; Globals::gStandardRate )

                 There are a number of possible variations on this method--depending on how you want to manage different rates, but that should at least point you in the correct direction.

                 Note: there are three basic methods for copying data from a related table:

                 1) an auto-enter calculations--what I've described here

                 2) a looked up value field option--you'll find this option also on the auto-enter tab in field options

                 3) use a script--not normally necessary except for special cases

                 There are other options for establishing a "live" link that, unlike the above, update values automatically if a value in the related table is modifiied.