Jump to content
Sign in to follow this  
JMart

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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.