6 Replies Latest reply on Dec 17, 2010 12:36 AM by crtopher

    Carrying over a finish time to the next record start time but adding 1 minute

    crtopher

      Title

      Carrying over a finish time to the next record start time but adding 1 minute

      Post

      I have a field StartTime that has an OnObjectEnter script trigger thus:

      [IMG]http://i56.tinypic.com/15n6at2.jpg[/IMG]

      which takes the previous record's FinishTime and puts it in the StartTime of the next record if it's empty, but enables me to adjust it if needed.

      It is also has an AutoEnter calculation that I grabbed from somewhere else here on the forum thus:

      Let ( [ nums = Filter ( StartTime ; "0123456789" ) ; chars = Filter (Upper ( StartTime ) ; "AMNP" ) ; lgthN = Length ( nums ) ; lgthC = Length (chars ) ; numTest = Case ( GetAsNumber ( nums ) < 1260 ; 1 ;                              GetAsNumber (nums ) < 2360 ; 2 ;                              0                           ) ; charTest = Case ( lgthC > 0 and lgthC < 3 ; 1 ; 0 ) ; mins = Case ( lgthN > 2 ; Right ( nums ; 2 ) ; "00" ) ; hrs = Case ( lgthN > 2 ; Middle ( nums ; 1 ; lgthN - 2 ) ; nums ) ; hrsN = GetAsNumber ( hrs ) ; minsTest = Case ( GetAsNumber ( mins ) > 59 ; 0 ; 1 ) ; hrs12 = Case ( numTest = 2 ; GetAsText ( hrsN - 12 ) ;                          hrsN = 0 ; "12" ;                          hrs                      ) ; suffix = Case ( charTest = 1 and ( PatternCount ( chars ; "A" ) or Exact ( chars ; "M" ) ) ; " AM" ;                         charTest = 1 and ( PatternCount ( chars ; "P" ) or Exact ( chars ; "N" ) ) ; " PM" ;                         charTest = 0 and Exact ( Left ( nums ; 1 ) ; "0" ) ; " AM" ;                         numTest = 2 and hrsN > 12 ; " PM" ;                         ""                      ) ; hrsTest = Case ( GetAsNumber ( hrs12 ) > 12 ; 0 ; 1 ) ; suffixTest = Case ( hrsN > 12 and Exact ( suffix ; " AM" ) ; 0 ; 1 ) ; inter = GetAsNumber ( hrs12 & mins )] ; 

        Case ( numTest = 0 ; "INVALID TIME" ;             minsTest = 0 ; "INVALID TIME" ;             hrsTest = 0 ; "INVALID TIME" ;             suffixTest = 0 ; "INVALID TIME" ;             inter > 0 and inter < 700 ; Left ( inter ; 1 ) & ":" & Right ( inter ; 2 ) &                         If ( not IsEmpty ( suffix ) ; suffix ; " PM" ) ;             inter >= 700 and inter < 1000 ; Left ( inter ; 1 ) & ":" & Right ( inter ; 2 ) &                        If ( not IsEmpty ( suffix ) ; suffix ; " AM" ) ;             inter >= 1000 and inter < 1200 ; Left ( inter ; 2 ) & ":" & Right ( inter ; 2 ) &                         If ( not IsEmpty ( suffix ) ; suffix ; " AM" ) ;             inter >= 1200 and inter < 1260 ; Left ( inter ; 2 ) & ":" & Right ( inter ; 2 ) &                         If ( not IsEmpty ( suffix ) ; suffix ; " PM" ) ;             "INVALID TIME"         )    )

      which parses any time entry into an appropriate format (eg 1030 becomes 10:30); donot replace existing value is UNCHECKED.

      I want to add one minute to the previous FinishTime and have it enter into the StartTime. I tried this 

      GetNthRecord ( Main::FinishTime ; Get ( RecordNumber ) -1 ) + 60

      which works only if I take off the Autoenter calculation above. If I leave that in, I get a ?mark in the StartTime field for the next case.

      Sorry for the complicated post. ANy help appreciated

      CHeers

      Chris

        • 1. Re: Carrying over a finish time to the next record start time but adding 1 minute
          philmodjunk

          I get lost half way through reading that calculation. If you were to repost after reformatting the calculation to make it readable, it would be a big help.

          Do you see anything if you click into that question marked field? (You can get a ? simply if the field is too narrow to display the results.)

          • 2. Re: Carrying over a finish time to the next record start time but adding 1 minute
            raybaudi

            By http://www.aptworks.com/cgi-bin/calc_formatter.cgi

            Let(
              [
                nums = Filter( StartTime; "0123456789" );
                chars = Filter( Upper( StartTime ); "AMNP" );
                lgthN = Length( nums );
                lgthC = Length( chars );
                numTest = Case( GetAsNumber( nums ) < 1260; 1; GetAsNumber( nums ) < 2360; 2; 0 );
                charTest = Case( lgthC > 0 and lgthC < 3; 1; 0 );
                mins = Case( lgthN > 2; Right( nums; 2 ); "00" );
                hrs = Case( lgthN > 2; Middle( nums; 1; lgthN - 2 ); nums );
                hrsN = GetAsNumber( hrs );
                minsTest = Case( GetAsNumber( mins ) > 59; 0; 1 );
                hrs12 = Case( numTest = 2; GetAsText( hrsN - 12 ); hrsN = 0; "12"; hrs );
                suffix =
                Case(
                  charTest = 1 and ( PatternCount( chars; "A" ) or Exact( chars; "M" ) );
                  " AM";
                  charTest = 1 and ( PatternCount( chars; "P" ) or Exact( chars; "N" ) );
                  " PM";
                  charTest = 0 and Exact( Left( nums; 1 ); "0" );
                  " AM";
                  numTest = 2 and hrsN > 12;
                  " PM";
                  ""
                    );
                hrsTest = Case( GetAsNumber( hrs12 ) > 12; 0; 1 );
                suffixTest = Case( hrsN > 12 and Exact( suffix; " AM" ); 0; 1 );
                inter = GetAsNumber( hrs12 & mins )
              ];
              Case(
                numTest = 0;"INVALID TIME";
                minsTest = 0;"INVALID TIME";
                hrsTest = 0;"INVALID TIME";
                suffixTest = 0;"INVALID TIME";
                inter > 0 and inter < 700;
                Left( inter; 1 ) & ":" & Right( inter; 2 ) & If( not IsEmpty( suffix ); suffix; " PM" );
                inter >= 700 and inter < 1000;
                Left( inter; 1 ) & ":" & Right( inter; 2 ) & If( not IsEmpty( suffix ); suffix; " AM" );
                inter >= 1000 and inter < 1200;
                Left( inter; 2 ) & ":" & Right( inter; 2 ) & If( not IsEmpty( suffix ); suffix; " AM" );
                inter >= 1200 and inter < 1260;
                Left( inter; 2 ) & ":" & Right( inter; 2 ) & If( not IsEmpty( suffix ); suffix; " PM" );
                "INVALID TIME"
              )
            )

             

            P.S.: only to let you know the above site ;)

            • 3. Re: Carrying over a finish time to the next record start time but adding 1 minute
              crtopher

              Sorry Phil...the link is here: http://www.briandunning.com/cf/1170

              I think raybaudi has reformatted it anyway above.

              With regards to the fields width, I don't think that is the problem since the auto-enter calculation had been working fine, indeed the on object enter script had been working fine, until i tried to add one minute to the StartTime. It seems adding a calculation to the field that the autoenter calculation has to work on is the problem. Is it possible that the autoenter calculation is not seeing the result of the onobject enter script calculation (ie GetNthRecord ( Main::FinishTime ; Get ( RecordNumber ) -1 ) + 60) but rather the calculation itself and so cannot parse it?

              Cheers, Chris

              • 4. Re: Carrying over a finish time to the next record start time but adding 1 minute
                philmodjunk

                When I tested the above calculation, I found it worked with either time or text data types, but the "invalid" results only displayed with a text field. In a field of type time, you see a question mark in place of this text.

                Since FinishTime is a field of type time, it's actually a number field that records the time in seconds elapsed since midnight. That's why you can add one minute to this time by adding 60 to it. But that means that GetNthRecord ( Main::FinishTime ; Get ( RecordNumber ) -1 ) + 60) is inputting a number (not 12:00 PM or some such) that evaluates as invalid.

                • 5. Re: Carrying over a finish time to the next record start time but adding 1 minute
                  crtopher

                  Hi Phil

                  Thanks for replying. I have tried putting the calculation in the previous record and just bringing the field result forward. ie I made a field called FinishTimePlusOne (a calculated field with FinishTime = 60) (which works fine - adding one minute to the finish time), then using an OnObjectEnter script SetField [Main::StartTime; GetNthRecord ( Main::FinishTimePlusOneMinute ; Get ( RecordNumber ) -1 )]; 

                  It doesn't work if the AutoEnter Calculation is set to replace existing value (which it must be in order to parse the manually entered data)...I also changed it to text and got INVALID (which is obviously generated by the AutoEnter calculation) because its obviously not seeing the time thats brought forward as a time even though the calculation is done on the previous record, and the time thats brought across looks exactly like a time should if i turn off the autoEnter. Is there a way I can modify the calculation to recognise whatevers being put in the StartTime field that looks like a time to me but not obviously to FileMaker.

                  I know...I know I'm getting hysterical...I'm calming down now...just reaching for my Serepax, swig of vodka...ok coool...I'm good. Awaiting calmly your reply. 

                  • 6. Re: Carrying over a finish time to the next record start time but adding 1 minute
                    crtopher

                    OK Phil...I think the alcohol and drugs helped for I HAVE SOLVED IT! The key is in that calculation - it only recognizes 4 or less numbers as a valid time (ie doesn't like seconds) and of course the mathematical manipulation of the finish time by adding 60 (seconds) is expressed as a "time" not a number and is entered thus xx:xx:xx even though the seconds are "00". I made another field that takes only the hour and minutes out of the manipulated field thus: Hour ( FinishTimePlusOneMinute ) & ":" & Minute ( FinishTimePlusOneMinute ) and then used that figure in the OnObjectEnter script.

                    woohoo!! I am very excited. Small victory, the results of which are reasonably insignificant. But it's the victory that counts!

                     Thank you for your time on this I appreciate it!!