6 Replies Latest reply on May 9, 2014 11:31 AM by jbrogers1234

    lookup fields

    jbrogers1234

      Title

      lookup fields

      Post

           So as far as I can determine in order for my number of workday calculation to work you have to manually enter a start and end date into the star and end fields.  This will not work as the start dates are already in the table as are the end dates.  So, how do I fool fm12 into thinking the start and end dates are being entered versus already there?  the calculation I used can be found under "Calculating the number of workdays between dates" in this forum.

        • 1. Re: lookup fields
          philmodjunk

               You should not need to do any such thing. Define your calculation as a field of type calculation instead of setting up a number field with an auto-entered calculation and there will be nothing to "look up".

          • 2. Re: lookup fields
            jbrogers1234

                 maybe i was not clear enough, lookups work when the state and end dates are enter directly into the appropriate fields, however, when the data is in the start and end date fields from one record to the next the lookups do not work.

                  

                 the calculations work not the look ups

                      

            • 3. Re: lookup fields
              philmodjunk

                   There still should not be any "lookup" of data needed.

                   Please describe what look ups you want to do and how your data is entered into your tables.

                   Are you saying that you have the start date entered in a date field of one record and the end date is entered into a date field of another? That would definitely complicate the process, but I still am not clear on what data you need to "look up" in order to calculate the needed value.

                   PS. I'm quite familiar with the Knowledge Base article that documents this calculation. When another user reported a bug in the results that it produced, I was the one that found and posted the needed correction to it that FileMaker then updated the article to correct the error.

                   There are also several different variations in how you can adjust the computed value in order to allow holiday closures within the specified date range.

              • 4. Re: lookup fields
                jbrogers1234

                     maybe I am not using the terminology correctly.  So here is the calculation:

                     5 * Int ( ( EndDate - StartDate ) / 7 ) +Middle ( "0012345501234544012343340123223401111234010012340" ; 7 * (DayOfWeek ( StartDate ) - 1 ) + DayOfWeek ( EndDate ) ; 1 )- (Lookup High - Lookup Low)

                      

                      EndDate and StartDate are currently in the table as dates.  In order to determine the number of "school days"  I calculate the number of vacation and holidays using the lookups and those are subtracted from the number of possible school days resulting in the number of school days between two dates.   The  calculation works fine if I enter the start and end date into the layout but no so much if I use the dates there and go from record to record.

                • 5. Re: lookup fields
                  philmodjunk

                       Please explain how the fields "lookup High" and "lookup low" get values. This should not require that the fields auto-enter data  in order for this calculation to work. They can be replaced with a single calculation field that computes the total number of related "holiday" records for the specified start and end date values.

                       The relationship might be:

                       YourTable::StartDate <  Holidays::Date AND
                       YourTable::EndDate > Holidays::Date

                       The calculation: Count ( Holidays::Date ) would then return the total holidays records from StartDate to EndDate.

                       However, a script that modifies one of the match fields used in the relationship that makes the look up possible can trigger a relookup of the data for the record where the field was modified. The modification need not actually change the value it can set it to the value that is already assigned to it.

                  • 6. Re: lookup fields
                    jbrogers1234

                         that is exactly what i thought and have now no need for the look ups and the calculation works great.  thanks for your help!