5 Replies Latest reply on Nov 21, 2016 12:26 PM by JTG

    Record search within a custom function

    JTG

      The database I'm working on has a calculated field in a table.  This field holds the number of working days between an initial date (stored in the same table) and the current date obtained with the Get(CurrentDate) function.

       

      The difficulty is that I need to take into account vacation days stored in another table.  The working days calculation is specific to a person and each person has his/her own unique vacation schedule.   I was thinking to do the calculation via a custom function, however, I have found no way to search a table from within a custom function.  Have I missed something about custom functions in this regard?

       

      I would appreciate any suggestions about how to approach this problem using custom functions or otherwise.

       

      Thanks

        • 1. Re: Record search within a custom function
          David Moyer

          Hi,

          well, you can't "search" for records within a custom function; but you can "relate" to specific record sets using some sort of relationship.

          • 2. Re: Record search within a custom function
            erolst

            Of course, the idea behind a Custom Function is abstraction; that's why there is no interface to select field references.

             

            But you can either copy and paste a field reference (or just type it), or - true to the spirit of abstraction - add a parameter to the function that lets you "inject" a list of dates into it, e.g.

             

            CalculateWorkingDays ( listOfHolidays )

             

            and use it like

             

            CalculateWorkingDays ( MyGlobalTable::gMyHolidayListField )

             

            In fact, there are already CFs out there doing exactly that ...

            • 3. Re: Record search within a custom function
              philmodjunk

              Let us not forget that we can use ExecuteSQL to search records from within a calculation and that can definitely be part of a custom function's design.

               

              Just pointing out that:

              well, you can't "search" for records within a custom function

               

              can be done so long as you just need a value (a count of the number of vacation days for a given employee in this case) and not the actual found set.

              • 4. Re: Record search within a custom function
                David Moyer

                ahh, I always forget about the SQL options.  I wish folks could use FM as the "formerly known as the inexpensive, beginner-friendly, sole-proprietor solution" to do what they need without learning SQL.

                p.s.  I've spent 25 years working around FM's limitations.

                • 5. Re: Record search within a custom function
                  JTG

                  Everyone --

                   

                  Thanks for the quick replies; they have stimulated my thinking, particularly the suggestion from erolst.

                   

                  I have to pull dates from a related (Vacation) table and an unrelated (Holiday) table.  At this time, I believe that I will use the relationship for the related table and an SQL query for the unrelated table.  Let me know if I'm making this more complicated than necessary.

                   

                  Thanks again.