Jump to content
Sign in to follow this  
Mr. Brutus

Date Distance Calculations

Recommended Posts

Mr. Brutus

I have a calculation issue that is way beyond my FMPro 7 skills . . . My project requires a dynamic value based on the distance – in days – between the current date and the date of an event which may or may not be exactly known – a "time proximity factor" if you will.

The idea is to have a date entry field – for the known dates – and a radio button series for other options – if the date is known it will be entered by the operator in the date field, if not, one of the radio buttons will have to be chosen.

 

The logic is the following:

 

Date entry field:

Date of event – current date =

0

>2

>7

>30

>180 V=2

 

Radio buttons:

 

“Unspecified Next 48 Hours”

date posted/modified – current date

>=0

 

“Unspecified Next Week to Ten Days; High Chance of Occurrence”

date posted/modified – current date

>=3 V=7

>=0

 

“Unspecified Next Few Months; Unspecified Chance of Occurrence”

date posted/modified – current date

>=30 V=3

>=0

 

“Not a Time-Related or Time-Sensitive Event”

V= -10

 

I hope this interests or challenges some of you. Thank you very much.

Mr. Brutus.

Share this post


Link to post
Share on other sites
Maarten Witberg

Hi mr. Brutus,

 

the calculation is longish. I wrote it out directly into this BB so I have not tested it for any errors. The general structure is a triple nested case statement:

 

case(the date of the event is not in the past

(case (date_of_event field is empty

case( value of radio button b1; r1 ; b2; r2;etcetere );

case( value of date of event =1 ; result 1; value 2 ; result 2; etcetera)

default result =-10)

 

here goes:

V=
case(
not(mod_date-current_date<0 OR date_of_event-current_date<0);
case(
isempty(Date_of_event);
case(
Radio_button_field="Unspecified Next 48 Hours" AND mod_date-current_date>=0 AND mod_date -current_date<3;9;
Radio_button_field="next week to ten days" AND mod_date-current_date>=0 AND mod_date -current_date<3;9;
Radio_button_field="next week to ten days" AND mod_date-current_date>=3;7;
Radio_button_field="next few months" AND mod_date-current_date>=0 AND mod_date -current_date<30;2;
Radio_button_field="next few months" AND mod_date-current_date>=30;3;-10
);
Date_of_event-current_date>0 AND Date_of_event-current_date<=2;10;
Date_of_event-current_date>2 AND Date_of_event-current_date=<7;8;
Date_of_event-current_date>7 AND Date_of_event-current_date=<30;6;
Date_of_event-current_date<30 AND Date_of_event-current_date=<180;4;
Date_of_event-current_date>180;2
);
-10
)

 

if you do not have a separate current_date field then you can replace this by get(currentdate) (v7) or status(currentdate) (v6).

Either way, do not store the result or get() / status () will not update.

 

now there's one thing missing and that's how to get mod_date (modification date of the radio button field) to evaluate and that can be done by scripting.

 

define 4 scripts, one for each radio button value (i give only one):

 

set field["radio_button_field","Unspecified Next 48 Hours"]
set field["mod_date", "get(currentdate)"]

Now place a script button over each radio button and link each to the appropriate script.

Now make the script buttons transparent so you see the radio button field.

 

One more thing, I advise you to use more descriptive fieldnames than "V" so you still know what they mean when you open up the database later on.

 

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
Mr. Brutus

Thank you very much for the quick reply. I did try to implement this but got a few errors - made a few changes but it still doesn't work . . . anything you see that doesn't make sense? Also, I am not sure about what you mean when you say not to store results?

 

 
Case(not(Date_Modified-Get(CurrentDate)<0 or CF1_Time-Get(CurrentDate))<0;
Case(
IsEmpty(CF1_Time);
Case(CF1b="Unspecified Next 48 Hours" and Date_Modified-Get(CurrentDate)>=0 and Date_Modified-Get(CurrentDate)<3;9;
CF1b="Next Week to Ten Days" and Date_Modified-Get(CurrentDate)>=0 and Date_Modified-Get(CurrentDate)<3;9;
CF1b="Next Week to Ten Days" and Date_Modified-Get(CurrentDate)>=3;7;
CF1b="Next Week to Ten Days" and Date_Modified-Get(CurrentDate)>=0 and Date_Modified-Get(CurrentDate)<3;9;
CF1b="Next Few Months" and Date_Modified-Get(CurrentDate)>=0 and Date_Modified-Get(CurrentDate)<30;2;
CF1b="Next Few Months" and Date_Modified-Get(CurrentDate)>=30;3;-10);
CF1_Time-Get(CurrentDate)>0 and CF1_Time-Get(CurrentDate)<=2;10;
CF1_Time-Get(CurrentDate)>2 and CF1_Time-Get(CurrentDate)<=7;8;
CF1_Time-Get(CurrentDate)>7 and CF1_Time-Get(CurrentDate)<=30;6;
CF1_Time-Get(CurrentDate)<30 and CF1_Time-Get(CurrentDate)<=180;4;
CF1_Time-Get(CurrentDate)>180;2;)-10
)

Thanks

Share this post


Link to post
Share on other sites
Maarten Witberg

Yes, there was something wrong with it definetly. I managed a working test calculation:

 

case(not(event_date-get(currentdate)<0 OR not(mod_date-get(currentdate)<0);
case(
Isempty(event_date) AND mod_date-get(currentdate)>=0;
Case(#evaluate the radio button field here#);
#now evaluate the event_date field here#;-10);-10)

it works but it does not look very neat, there seem to be double evaluations in it for negative values of event_date-currentdate. But I could not get it to work without. Sorry.

 

Further things to consider:

-all date fields should be set as date not text or number

-do not store option can be found under the options button in the define field box.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
-Queue-

You could use Let ([ D = Date_Modified - Get(CurrentDate); T = CF1_Time - Get(CurrentDate)]; ... ) and substitute D and T appropriately.

 

You could also work the 'time' test backward to simplify it.

 

Case( ... ; T > 180; 2; T > 30; 4; T > 7; 6; T > 2; 8; T > 0; 10 )

Share this post


Link to post
Share on other sites
Mr. Brutus

I can't thank you enough Kjoe, and you too Queue - but unfortunately the thing still doesn't work - The Case statement you suggested tells me that there are too many "(" or ")" so I tried the following but the results is always -10 . . .

 Let ([D=Date_Modified-Get(CurrentDate);T=CF1_Time-Get(CurrentDate)];
Case(not(T<0 or not(D<0));
Case(
IsEmpty(CF1_Time) and D>=0;
Case(
CF1b="Unspecified Next 48 Hours" and D>=0 and D<3;9;
CF1b="Next Week to Ten Days" and D>=0 and D<3;9;
CF1b="Next Week to Ten Days" and D>=3;7;
CF1b="Next Week to Ten Days" and D>=0 and D<3;9;
CF1b="Next Few Months" and D>=0 and D<30;2;
CF1b="Next Few Months" and D>=30;3;-10
);
T>0 and T<=2;10;
T>2 and T<=7;8;
T>7 and T<=30;6;
T<30 and T<=180;4;
T>180;2
);
-10
))

Thanks - if you still have it in you!

Share this post


Link to post
Share on other sites
-Queue-

not (T<0 or not D<0) 

means

if T>=0 and D<0.

I think what you want is

not (T<0 and D<0)

which means T>=0 or D>=0. You have duplicate tests and results where tests should be. Try this

Let ([ D = Date_Modified - Get(CurrentDate); T = CF1_Time - Get(CurrentDate) ];
Case( not (T < 0 and D < 0);
Case( IsEmpty(CF1_Time);
Case(
CF1b = "Unspecified Next 48 Hours"; Case( D < 3; 9; -10 );
CF1b = "Next Week to Ten Days"; Case( D < 3; 9; 7 ); 
CF1b = "Next Few Months"; Case( D < 30; 2; 3 ); -10
);
Case( T < 3; 10; T < 8; 8; T < 31; 6; T < 181; 4; 2 );
); 
-10 ) )

Share this post


Link to post
Share on other sites
Mr. Brutus

It's telling me that a "number, text, constant or ( is expected here" - referring the line before last.

Share this post


Link to post
Share on other sites
-Queue-

Remove the ; following "4; 2 )". Sorry about that.

Share this post


Link to post
Share on other sites
Mr. Brutus

That's It!

Thank you very much!

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

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

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use