I am having trouble with field calculation in Filemaker 13. The result of the calculation is two letters with conditional formatting determining the bar colour on a gantt layout.

 

This unstored field calculation was working as expected then suddenly ceased to generate the expected result.

 

Let([

 

~hourfm = If( IsEmpty(Book_Time_From ) ; 1 ; Hour( Book_Time_From ) - 7 ) ; //gantt starts at 1 meaning 0800 hours

~hourto  = If( IsEmpty(Book_Time_To ) ;  12 ; Hour( Book_Time_To     ) - 7 ) ;  // gant finishes at 12 meaning 1900 hours

~category = If( IsEmpty( Book_Category ) ; "Re" ; Left( Book_Category ; 2 ) );    // if not set make a 'Request'

~current_date = xgantt_gd_Start_Cal_Thursday + 2 ;  // uses Thusrsday as start of client week in gantt chart

~thishour = 5  // ie 1200 hours

 

];

 

Case(

~current_date >  Book_To     ; "" ;   //ie booking is earlier than the period to be shown

 

~current_date < Book_From  ; ""  ;   // ie booking begins later than the period to be shown

 

~current_date = Book_From and ~current_date = Book_To ;  // a one day event within the period to be shown

If( ~hourfm <= ~thishour and ~hourto >= ~thishour ;  ~category ; ""  ) ;

 

~current_date =  Book_From ;  // is this the start date

If( ~hourfm <= ~thishour ;  ~category ; ""  ) ;

 

~current_date =  Book_To ;    // is this the end date

If( ~hourto >= ~thishour ;  ~category ; "" ) ; 

 

If( ~current_date  >   Book_From    and ~current_date  <  Book_To  ;   //is this a between date

  ~category ; "" )

 

) 

)

 

The relevant fields show:

 

Book_Category  =  “Request”

Book_From = 12/04/2014

Book_To = 12/04/2014

Book_Time_From = 1200:00:00

Book_Time_To = 1500:00:00

xgantt_Start_Cal_Thursday = 10/04/2014

Calculated Field shows: “"

Some times it the simple things that catch one out.

 

In this case its the time which should be formatted 12:00:00 and 15:00:00 respectively. In this case a data entry issue.

Please update your profile to reflect your current FileMaker version and other platform information. Here is a quick link to your profile. MY PROFILE.