The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum

# Date Distance Calculations

## Recommended Posts

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

â€œ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 on other sites

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

##### Share on other sites

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 on other sites

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

##### Share on other sites

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 on other sites

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 on other sites

`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 on other sites

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

##### Share on other sites

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

##### Share on other sites

That's It!

Thank you very much!

## Join the conversation

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

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

×

• ### Images

• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
• 0
By Soliant Consulting,
×
×
• Create New...