The service interval in the bicycle details table is just an number e.g. 5
Does that "5" represent 5 months?
Yes it does and the variable can be adjusted by the user.
"the variable can be adjusted" means that the number of months specified as your "interval" can be adjusted?
And there are two ways to treat as a "month" when dealing with dates. You can consider 30 days to be one month or you can compute a date for the same day of the month, but add 5 to the month value to compute a new month. In other words. If your date is Janaury 1, 2015. 5 months later would be June 1, 2014. The two methods produce similar but not identical results.
I'm not overly worried about precision of the dates as we visit sites so as long as the due date is less than the next visit date the we are good for a reminder.
Your general description of how you want to do this seems sound, but there is not enough detail as to the excact design of your database to comment in more detail about how to go about doing this.
I think my main issue is how to get the date of the last service in to the bicycle detail table or use the data in the associated table. I can do it from the script that sets the service to complete but it won't obviously update the historical services - this is a new system using legacy data from our old system. This is the last issue to sort out before we can finish testing and reimport the live data from our old system.
I thought that I recognized this project, but wasn't about to trust my memory to be accurate and even if I had accurately remembered your design, there was no way to know if the design in that other thread is still your current design anyway.
Does your current design match the screen shot near the end of that thread or have you made other design changes such as the changes that I suggested in the last post in that thread?
Sorry for the delay in responding,
Yes relationship has changed a little although i still haven't implemented you solution - i got a bit confused as even you simple solution is at the limit of my ability. However having reread your support article I am feeling a little clearer on that problem. The date issue wasn't a critical issue just a necessary one but we have a temporary work around so it has been moved down the priority list!
This one however is so i can't go live without sorting it out.
I have attached an updated relationship diagram in ht hope it will make things a little clearer.
As always Phil, thanks again for your invaluable help it is immensely appreciated.
Am I correct that you want to find all records in ServiceVisits with a date in VisitDate that are on or after today but no more than 30 * Interval days into the future from today?
Do you want to see these records as a list of records in a portal or a found set on a layout based on an occurrence of the ServiceVisits table?
Or perhaps as values in a value list?
This script would produce the needed found set:
Go to Layout ["ServiceVisits" (ServiceVisits) ]---> any layout with "ServiceVisits" in "Layout Setup|Show Records From? can be used
Set Variable [$Interval ; value: YourTable::Interval ] ----> Don't know what table stores this value.
Set Variable [$Customer ; value: YourTable::CustomerCode ]
Enter Find Mode  ----> Clear the pause check box
Set Field [ServiceVisits::VisitDate ; Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 30 * $Interval ]
Set Field [CustomerBikes::CustomerCode ; $Customer ]
Set Error Capture [on]
Perform Find 
Note that if you use a layout based on a different occurrence of ServiceVisits such as ServiceVisits 2, you'll need to change the Set Field step to refer to the different table occurrence.
You may also need to modify the Start of script depending on which table stores the interval.