Is it feasible for your users to update this with a script?
" i.e. click button to find any stale records and then list those
--------
If not that is OK. Just don't want to be working on a script solution that will not be used.
FYI - There is a chance that you may not be able to index your calculations which would mean a long list of records could take a long time to refresh on the screen.
I will wait on Bill's comments. Mike
Main Topics
Browse All Topics





by: billmercerPosted on 2009-11-09 at 10:51:42ID: 25778749
Ouch. This is a pretty complicated calculation, and it's made more difficult by the fact that you're using FM6, which means you will need separate fields for the dates and times.
Are there any plans to upgrade to a newer version of FileMaker? This sort of problem would be a lot easier to solve with a newer version that supports datetime fields and user defined functions.
Start with a simpler problem, figuring out the elapsed time between two values without regard to weekends or evenings. The fact that the two values are stored in separate fields with different scales will make this more complicated.
FileMaker date fields are displayed in date format, but internally are stored as integer numbers. The number corresponds to the number of days since an arbitrary starting date. This means you can do addition and subtraction with date fields. For example, you can compute the number of elapsed days between two dates by subtracting one from the other.
Time fields are similar. They display as time, but internally are stored as the number of seconds since midnight.
To find the total elapsed time between two pairs of date and time fields, you need to put both date and time values into a common scale.
One way to do that is to convert the date values from days to seconds, so you can add the time to the date and get a single value. There are 86400 seconds in one day, so we can multiply the first date by 86400, then add the first time. Then do the same for the second date and time pair, and subtract the earlier from the later, which will give you the total number of seconds that have elapsed between the two.
(CurrentDate * 86400 + CurrentTime) - (ModDate * 86400 + ModTime )
This can then be converted back into minutes, hours, days, or whatever.
The tricky part of this is including only a limited range of hours from each day and only weekdays.
In FileMaker 6 you are rather limited by the fact that your calculations must be done with either a calculated field or a script. A calculated field will probably be best in this case because you want a "live" update on the status of each record. A script would require an end-user to do something like click a button to trigger the calculation.
I'm gonna think about this for a while.