8 Replies Latest reply on Jul 3, 2012 10:00 AM by barenose

    Identifying holiday dates


      Hello all!


      I've seen the replies regarding scripting for weekends, et. al. Has anyone developed a way to automatically identify holidays? What I would like to do is have a function where I can load in a holidays table, and when I come to a date, for example create a record for a certain date, I'll get a dialogue telling me that the selected date is a holiday (as per the table I've previously loaded). Obviously, this would not be throughout the database tables, but in something like a commLog, or a purchase order, etc.


      Any ideas?


      All the best,



        • 1. Re: Identifying holiday dates

          Why use a Cartesian relationship and a list when you can relate the holiday table via the date field?


          Then the check reads simply: isValid ( myHolidayTable::date )

          • 2. Re: Identifying holiday dates

            Hi Jeremy


            Please forgive me for being blunt, but on this list there are examples of both advice and good advice ...


            Your solution (or the one you have described) ignores FileMaker's simple built-in 'Relationship' feature and tries to mimic the same functionality using a high-overhead calculation.


            Let's just review what is required here ...


            A table of holiday dates is used to see if the date entered into a different table matches one of these dates. This is a classic 'Relationship' function. 


            Set up a relationship between the 'Holiday Date' field and the 'Date Entry' field and then use an 'If' statement to check that the result of the relationship is empty (ie: Not a Holiday).


            Example: If [ IsEmpty ( Holiday Dates::Holiday Date )  // Not a Holiday Date. ]


            This is a native FileMaker function and I suspect cannot be improved upon for speed by using any other more complicated calculation.


            My apologies for being critical, but I read this list everyday and get frustrated when advice is given which although answering the question, does not provide the most obvious and efficient solution.


            Best wishes - Alan Stirling, London UK.

            1 of 1 people found this helpful
            • 3. Re: Identifying holiday dates

              Hi Erolst


              As has been already discussed on this list (in depth), the 'IsValid' function tests the validity of the relationship and does not provide a different result if a particular record is found or is missing.


              You need to use 'IsEmpty' in order to reliably test for a matching/missing entry in the related table.


              I believe that you will see this if you carry out a practical test.


              Best wishes - Alan Stirling, London UK.

              • 4. Re: Identifying holiday dates

                Alan.  Ive removed my comment in deference to more efficient advice.  The solution I suggested which someone else suggested does work. But I do understand the IsValid idea. Makes sense.  I didn't know that and no one suggested it to me.


                Maybe there needs to be a "most efficient" button on this forum to indicate the best solution

                Or a "not efficient" button


                I'll make sure if I ever give advice it is the most efficient. Which is why I'm on here—to learn what's efficient

                • 5. Re: Identifying holiday dates

                  Hi Alan


                  I'm afraid you didn't understand the method I suggested, in-depth discussion or no in-depth discussion.

                  The relationship to a Holiday table via date either finds no records, or exactly one - the matching Holiday record.


                  This means you don't even have to check on IsEmpty, but simply on IsValid, which should make it a wee bit faster

                  (or more efficient, if you like).


                  The attached screenshot displays the result of a “simple practical test”.



                  • 6. Re: Identifying holiday dates

                    Hi Erolst


                    Please accept my apologies, you are correct - IsValid will work in this situation.


                    However, IsValid is testing for 3 or 4 specific situations, whereas IsEmpty is testing for one, so I find it difficult to agree that IsValid is likely to run faster when used here.


                    Best wishes - Alan Stirling, London UK.

                    • 7. Re: Identifying holiday dates

                      Hi Alan,


                      no apologies necessary, I just wanted to make my point


                      As for being more efficient - well, Isvalid does do different things, but which one exactly depends on the kind of field you feed it with.


                      So I assume that IsValid, used with a related field, merely checks if there is one related record - or not - and leaves it at that, same as Case () stops as soon as it finds a true statement.


                      It was meant anyway a bit tongue in cheek. Isn't it nice that FileMaker offer so much ways to do the same thing, from very the simple to the most convoluted?


                      Have a nice Sunday.




                      • 8. Re: Identifying holiday dates

                        Hello Alan,


                        I often come to the forum to see if there's a more efficient way to do what I'm already doing. I'm glad that my answer matched yours, and I thank you for your valuable contributions to the forum for all of us.


                                    All the best,