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...
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.