Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Service date and service interval


Paul Brugman

Recommended Posts

I'm looking for a cunning calculation to the following:

There is a database containing devices, who have to go back to the factory on predefined service interval times. In a field I want to show the next comming service date.

I have the following fields:

Manufacturing date: manudate

Service interval in years: servint

Service date: servdate

I have a rather complicated calculation handling only the year, not the entire date. That has a result that if you have still a month or so before the service date, it already shows the next service year. My mathematic brain is too small to salve this problem, so can anybody help me out with this?

 

Thanks

Paul

Link to comment
Share on other sites

Hello Paul,

It's not entirely clear from your post whether the service date field is a data entry field in which the date of the last service is entered - or whether you simply want to calculate the the next service date in intervals from the manufacture date, and return the first one which is not yet in the past.

 

If the former is the case, you could use a formula (in an unstored calc) for NextServiceDate specified as:

 

Date(Month(LastServiceDate), Day(LastServiceDate, Year(LastServiceDate) + servint)

However if, as I suspect, you simply want to extrapolate the next service date working forward from manudate by periods equal to servint, you might prefer to use the following formula (also in an unstored calc):

 

Case(
Mod(Year(Status(CurrentDate)) - Year(manudate), servint),

Date(
Month(manudate), 
Day(manudate), 
Year(Status(CurrentDate)) + 
servint - Mod(Year(Status(CurrentDate)) - Year(manudate), servint)),

Date(
Month(manudate), 
Day(manudate), 
Year(Status(CurrentDate)) +
Case(
(Month(manudate) = Month(Status(CurrentDate)) and Day(Status(CurrentDate)) > Day(manudate)) or 
Month(manudate) < Month(Status(CurrentDate)), 
servint)
)
)

...which will give the next scheduled service date calculated in intervals of servint from manudate, automatically incrementing to the next date interval when each one is passed.

Link to comment
Share on other sites

Yeeaaahhh,

So, now I'm sure: The Aussies not only bring us good wine, but also brilliant Filemaker Pro's.

Your second solution was what I was looking for so desperately and it works perfect. The MOD function I used in my own solution as well and I have to study your calculation to understand how it works. BTW, the commas you used have to be replaced by the dot-comma ";" to make it work.

 

Thanks a lot Ray, you made my day!

Link to comment
Share on other sites

This is exactly what I needed for an equipment maintenance log I am creating. Now, how to have a field which shows an alert if the "service performed?" box is not checked "YES" on the appropriate service date...?

Link to comment
Share on other sites

Hi Maura,

The logic of the second calc provided above is such that is will recalculate to the next scheduled service date after each service date is reached (which was what Paul wanted).

 

In your case, it is clear that you want to be still able to see the past dates, so that you can see whether the service for a given date was performed. I presume therfore, that you will have each scheduled service on a different record within a service log file?

 

That being the case, you could use the first calc above - or perhaps you could make the field stored (so the value will not change with the date) and use the second formula. However the effect will be that the date shown will always be the service date immediately following the date a record is created.

 

In either case if you then add, alongside the calculated service date, a checkbox (eg called 'ServiceStatus') for whether the service has been performed (eg with a valuelist attached with the word 'completed'), you will be able to flag overdue services with an unstored calculation along the lines of:

 

Case(Status(CurrentDate) > ServiceDueDate and not ServiceStatus = "completed", "SERVICE OVERDUE")

 

You'd then be able to search on this field to produce an overdue maintenance report, and/or to add flag and summary fields to drive a global flag for overdue services across the whole file (eg a flag in the header which says something like 'There are currently 3 overdue services').

Link to comment
Share on other sites

...and on the earlier thread:

 

Paul, thanks for your kind words, and I'm pleased to hear that the second calc formula I suggested was on target! smile.gif

 

BTW the format I gave was the US/International format, using commas as the separator. I overlooked to mention that in local versions in some European countries (including the Netherlands) a semi-colon is used as the separator instead. Glad you figured that one out despite me overlooking to mention it in my original post! wink.gif

Link to comment
Share on other sites

Hi Ray,

 

Yes, I needed the calculation which will show the next service date based on the service interval and the date the last service was actually performed. The second bit will alert me to what's overdue without the mental arithmatic I go through daily now. Awesome!

I'll let you know how it turns out.

Thanks!

 

Maura

Link to comment
Share on other sites



×
×
  • Create New...

Important Information

Terms of Use