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".
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
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.
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.
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.
that is exactly what i thought and have now no need for the look ups and the calculation works great. thanks for your help!