Help with Time - date calc

[aldipalo]

I have a portal that tells me when a task is scheduled - ::Date of task/::Time of Task/::Alarm/::Reminder Time.

::ReminderTime is a calc field see JPEG above:

The calc gets data from the Alarm field see JPEG above:

Which is a value list.

Problem:

As long as I am choosing 5 minutes to 12 hours everything works fine. If I want to set the reminder to 1 + days/weeks before it does not calculate. I have figured out that I need a calc that will set the ReminderTime to a day before at the same time, but, I can not figure out how to set up the calculation.

Help Please!!

Thanks,

Al

If anyone has any suggestions as to how to make this shorter and more efficient pleae tell me. :D

Edited September 27, 2006 by Guest

[comment]

It looks like you need to add a date element to the Reminder, e.g. make the calculation result in Timestamp. Then you could use something like:

Let ( [

f = Case (

PatternCount ( Alarm ; "Minute" ) ; 60 ;

PatternCount ( Alarm ; "Hour" ) ; 3600 ;

PatternCount ( Alarm ; "Day" ) ; 86400 ;

PatternCount ( Alarm ; "Week" ) ; 604800 )

] ;

Timestamp ( Interview_Date ; Interview_Time ) - GetAsNumber ( Alarm ) * f

)

[aldipalo]

Hi Comment:

Thanks for the quick reply. If I use a timestamp field how do I just get the time to show in the field?

[comment]

You can format the field to show/not show any date element, by selecting Format > Date... in Layout mode. But I am puzzled: if an interview is scheduled for Dec 7 11:00am, and the alarm is set to "1 week" - what use is showing "11:00am" as the ReminderTime (when it really is "Dec 1, 11:00am")?

[aldipalo]

If the Interviewtime is today at 3 PM, and the remindertime is 30 minutes before I want it to just show the time, not date and time. This is used in a scriptfire script and the remindertime is the field the triggerfield looks at to create the schedule. I guess I need to rework it because as soon as I set the field to TimeStamp from Time I get error messages.

So, I'm not sure what I need to do to fix that problem. Its back to the manual to see.

As usual, I appreciate your help and I'll let you know if I solve the scriptfire problem or not.

Al

[comment]

As there's virtually no conditional formatting in Filemaker, you will need a field with a TEXT result for the display, e.g.

Let ( [

f = Case (

PatternCount ( Alarm ; "Minute" ) ; 60 ;

PatternCount ( Alarm ; "Hour" ) ; 3600 ;

PatternCount ( Alarm ; "Day" ) ; 86400 ;

PatternCount ( Alarm ; "Week" ) ; 604800 ) ;

ts = Timestamp ( Interview_Date ; Interview_Time ) - GetAsNumber ( Alarm ) * f

] ;

Case (

f ≥ 86400 ; GetAsDate (ts) ; GetAsTime ( ts ) )

)

[aldipalo]

Scriptfire requires a text field that sets the "Schedule": the date and time of the event. It needs to be formatted:

Once: mm-dd-yyyy hh:mm:ss

I reworked my calculation in this field to:

Case(

IsEmpty(Interview_Time);" ";

"Once:" & " " & Month (Reminder Time) &"-"& Day(Reminder Time) &"-"& Year (Reminder Time) &" "& Hour (Reminder Time) & ":" & Minute (Reminder Time) & ":" & Seconds (Reminder Time)

)

This seems to have solved the problem.

Thanks for that extra case statement that will do the trick.

************************

I tried the new calc and that date comes back as 1/01/0001...:?:

Edited September 27, 2006 by Guest