asked on

FM - IF Statement Able to Check for Date on a Weekly Basis Forever

Want an IF statement that checks and sends a reminder email every week before and after a deadline date. The only way I can think of doing this is to get the absolute value of the Deadline Date - Current Date, then divide by 7 and only get a true IF statement if the result is a whole number (no decimals). How would I do that? I don't see an absolute value sign in the FM dialog box, etc. Thank you.

SOLUTION

Will Loving

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

And finding a whole number could be the absence of a decimal as in

PatternCount ( GetAsText ( Field ) ; "." ) = 0 ???

ASKER CERTIFIED SOLUTION

rvfowler2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rvfowler2

ASKER

Actually, ends up you don't need to take the absolute value after all.

Will Loving

I wouldn't use pattern count to check for a decimal point since one might have been entered manually and you can never tell if the field result is set correctly to number or incorrectly to text. If yo want to test for whether something has a decimal you can use:

Case( someField = Int( someField ;"this is a whole number" ; "decimal value present")

Int() being the Integer function which gives you the non-decimel part of a number, e.g. Int( 3.14159 ) = 3 It is not the same as rounding.

The number minus the Int() version of the number will give you just the decimal part.

Another useful similar function is Mod() which gives only the remainder of a division calc and can be used to determine odd/even. e.g. Mod( 4 ; 2 ) = 0 and therefore is even, Mod( 5 ; 2 ) = 1 and therefore is odd. http://www.filemaker.com/12help/html/func_ref3.33.35.html

rvfowler2

ASKER

Final, Final Answer per my IF statement for my Database:

( WORKORDERS::WorkDeadline - Get ( CurrentDate ) ) / 7 =
Int ( ( WORKORDERS::WorkDeadline - Get ( CurrentDate ) ) / 7 )

Will Loving

Why not use:

Abs( WORKORDERS::WorkDeadline - Get ( CurrentDate ) ) = 7

It will only work on the two dates that are one week before and one week after...no Int() required.

Note that if it's an indexed calc it may not work as expected. It needs to be unindexed so the Get ( CurrentDate ) calculates every time.

rvfowler2

ASKER

My boss wants the weekly reminders indefinitely until the work order is closed. Also, I'm putting this in a script, not a field, so the indexing doesn't apply.

Will Loving

OK, then you want to use the Mod() function.

Mod( Abs( WORKORDERS::WorkDeadline - Get ( CurrentDate ) ) ; 7 ) = 0

the Result is True if the difference between the dates is a multiple of 7.

rvfowler2

ASKER

Does this account for if the WorkDeadline equals Get ( CurrentDate ) ?

Will Loving

If you don't want it to include the actual day, then just exclude that:

case(
WORKORDERS::WorkDeadline <> Get ( CurrentDate ) and
Mod( Abs( WORKORDERS::WorkDeadline - Get ( CurrentDate ) ) ; 7 ) = 0
; 1 )

rvfowler2

ASKER

Actually, no I meant I wanted to include it.

Will Loving

Then the Mod calc alone should do it because 0 divided by 7 is still zero so there is no remainder and the test is True.

rvfowler2

ASKER

Thanks Will, gave me the missing piece.