I need to make a calculation that detects if a record has not been changed in the last 6 business hours. Then if it does the field will show STALE. However it has to be business hours, so if they make a change at 4pm on a friday it cannot say stale until 1pm monday monring (we close at 5 and open at 8)
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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
Hi mrsam3,
Perhaps this will get you started. By no means complete and only the easy part.
Create a new Filemaker file for testing.
Create 5 fields - see the attached picture - note: I put z_ before the fields but this is not necessary
z_Test Text Field - text field, indexed - so you can cause the record to be modified
z_ModifiedDate - date field
z_ModifiedTime - time field
z_ModifiedDateTimeAnd6Hour
DateToText(z_ModifiedDate)
note: 6 hours x 60 minutes per hour x 60 seconds per minute - you could just use 21600
z_StaleDateTimeReached - calculation field-text, under options -> Storage Options: check the box that says "Do not store calculation results - calculate only when needed"
Case(DateToText(Status(Cur
The field "StaleDateTimeReached" will show Stale if 6 hours have passed since the last modification time of the record.
For testing you can modify the calculation down to +216 instead of +21600 so that it changes after 3.6 minutes.
Well, that is the easy part.
" You now have a field that displays Fresh for any record modified within the last 6 hours. Otherwise it will show Stale.
note: you need to refresh or change the layout for the field to change - this causes the calculation to update.
We are close... You still need to create the calculations that determine:
A) how much time, if any, is still left open for the next day
B) if the next day is a weekday then ADD that left over amount + 54000 (5 pm to 8 am which is 15 hours worth of seconds) to get the new end time
C) if the next day is a Saturday then ADD the left over amount + 226800 (63 hours in seconds) to get the new end time
Then the Fresh Stale Calculation would need to use these new calculations.
I am pretty sure it could all be done in one IF or Case statement but sometimes it is better to create the calculations separately first to make it easier to troubleshoot any errors.
If I get more time in the next day or 2 I will reply again.
Cheers, Mike
Five Fields
Business Accounts
Answer for Membership
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.