# Predict Future dates

## Recommended Posts

Predict Future dates

Good day gurus,

I am in dire need of assistance with a formula that would allow me to calculate future dates based on five fields for a service database, e.g.

There are five fields;

Field 1: Frequency_of_the_service (e.g. monthly, quarterly or annually)

Field 2: Date_sold

Field 3: First_service_data (30, 90, or 365 from the date_sold)

Field 4: Last service date (based on field

Predict Future dates Next Service dates (based on last_service_date)

Based on these five fields I need to populate a portal by adding a record for servicing every month, quarter or annual.

The portal can be a separate table linked by the serial no.

Any help is greatly appreciated.

##### Share on other sites

Hello

Your post made sense right up to the 'Field 4' part where it seemed to be missing some info. Also, the next line didn't make much sense.

Could you edit your post a little and I can help!

Also, is 'Last service date' supposed to mean the future last date on which service will be provided, or the most recent service date. It seems like we don't know how many services to schedule (do they get 4, 8, 12 etc etc?)

Edited by rwoods

##### Share on other sites

I find it confusing too. I would expect to have these fields as the starting point:

Date_of_Sale
Frequency_of_Service (e.g. monthly, quarterly or annually)
Length_of_Service_Period (presumably a number of years?)

Now, if you plan to add a record in a related table for every planned service event, then you don't need any other fields in this table; you need a script to go to the other table and loop until it creates the required number of records (for example, a quarterly service for 5 years requires 4 * 5 = 20 service records).

##### Share on other sites

Hi guys my apologies if I was not clear and truly appreciate your help,

I have tow tables

Table one contains all the details of the contract and table two contains the contract schedule

Table one I have the following fields;

Equipment_Serial_Number (key field linking both table)

Contract_Term (monthly, quarterly, annually)

Billing_Cycle (monthly, quarterly, annually)

Contract_Start_Date

Table two I have the following fields;

Equipment_Serial_Number (key field linking both table)

Scheduled_Service_Date  This date would be enter manually by the dispatched as work service is scheduled

Estimated_Service_Date Populated dates based on Contract_Term and Contract_Start_Date field

Contracts would never be more than one year. where I am stock is with the loop to populate the field on the second table.

Once again thanks for your help

Edited by JMart

##### Share on other sites
43 minutes ago, JMart said:

Contracts would never be more than one year.

But would they ever be less? If yes, where would this be recorded?

##### Share on other sites

Thanks for the reply, since my last post I added an a calculation field that would auto enter the number of scheduled visits , e.g. if contract term is annual = 1, monthly = 12

##### Share on other sites

IMHO, if the contract is for one year from today, and the frequency is monthly, then the number of visits is 11. Unless you have a service visit immediately upon signing of the contract.

In any case, the script could look something like this:

```Set Variable [ \$parentID; Value:Parent::ParentID ]
Set Variable [ \$frequency; Value:Parent::Frequency ]
Set Variable [ \$date; Value:Parent::StartDate ]
Set Variable [ \$n; Value:Case (
\$frequency = "monthly" ; 12 ;
\$frequency = "quarterly" ; 4 ;
\$frequency = "annually" ; 1
) ]
#
Freeze Window
Go to Layout [ “Child” (Child) ]
Loop
Exit Loop If [ not \$n ]
New Record/Request
Set Field [ Child::ParentID; \$parentID ]
Set Variable [ \$date; Value:Case (
\$frequency = "monthly" ;
Date ( Month ( \$date ) + 1 ; Day ( \$date ) ; Year ( \$date ) ) ;
\$frequency = "quarterly" ;
Date ( Month ( \$date ) + 3 ; Day ( \$date ) ; Year ( \$date ) ) ;
\$frequency = "annually" ;
Date ( Month ( \$date )  ; Day ( \$date ) ; Year ( \$date ) + 1 ) ;
) ]
Set Field [ Child::Date; \$date ]
Set Variable [ \$n; Value:\$n - 1 ]
End Loop
Go to Layout [ original layout ]```

I have used Parent and Child as the names of the two tables, and ParentID as the name of the match fields that link them.

Edited by comment

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×

• ### Who Viewed the Topic

1 member has viewed this topic:
Terrible Toll
×
×
• Create New...