Predict Future dates

[JMart]

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.

[rwoods]

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 February 10, 2017 by rwoods

[comment]

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).

[JMart]

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 February 10, 2017 by JMart

[comment]

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?

[JMart]

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 

[comment]

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 February 10, 2017 by comment