1 2 Previous Next 17 Replies Latest reply on Jan 8, 2009 6:23 AM by flybynight

    Time field calculations messed up

    flybynight

      Title

      Time field calculations messed up

      Post

      I use a modified version of a Time Card template that was supplied with a previous version of FMP. It worked fine in v5 & v6 (still works fine in v6). Then, we upgraded to v9 and things aren't working right.

      Here is how it was working:

      I enter times using the decimal as a separator (faster on the 10-key pad) as 9.15 or 17.05 and it converted it to 9:15 or 17:05. We almost never have someone work past midnight, so 24 hour time is adequate for a simple End Time - Back From Lunch + Leaving For Lunch - Start Time calculation.

      In FMP v9, opening the same file, times I enter show up as "0:00" after I tab out of the field, unless it is just a number like an even hour "9" and then it shows up as "9:00". Either way the calculation field shows up as "?" until I click in it and then it shows "8.04.00" as an example.

      If I try to enter times with a ":" separator (just to test) I get a message saying:

      The value of this field mus be a valid time (hours, hours and minutes, or hours, minutes and seconds) and should look like "11.09.03 PM". 

      Any idea what is going on here? Basically, my goal is just to be able to enter hours and minutes with a "." separator and have it do simple calculations. To get by I have just been using FMP v6 for this one and v9 for everything else.

        • 1. Re: Time field calculations messed up
          raybaudi
            

          Hi FlyByNight

           

          it seems that you need only to extend that field; IOW more space to the time is better ;)

          • 2. Re: Time field calculations messed up
            flybynight
              

            No, that doesn't seem to be it. I've tried altering the layout with more space and/or smaller font and it doesn't seem to matter. I'll see if I can put in some screen shots here.

            This is how it looks with no fields selected. The bottom line that has numbers were entered as just "9", where the others that display "0:00" are entered as something like "8.55".

            This shot shows one of the fields selected:

             

            Now, here's the interesting part. The first line (Monday) contains data that was entered in FMP v6. I've selected the calculation field which shows "8.04.00" - if the 8 is hours and the 04 is minutes, it's doing the calculation correctly... I just can't see it without selecting it.

            In the next line (Tuesday) I've entered the exact same data for the 4 times (8.12, 12.31, 13.13 & 16.58) but I entered it after converting to FMP v9. Now, if I click in the calculation field, it shows "0.00.07.64" - WTH?

             

            If you can figure out what it is doing - and more importantly - what I need to change to get it to act the way it used to, I would be greatly appreciative. Thanks everyone! 

            • 3. Re: Time field calculations messed up
              raybaudi
                

              Ok, I know what is up !

               

              Your keypad has comma, not period.

               

              So, when you write an integer hour there is no problem with the calculation, but when you write 8.12 it really enters 8,12 meaning 0 hours, 0 minutes and 8,12 seconds.

              • 4. Re: Time field calculations messed up
                Jens Teich
                   Point obviously is no longer supported as time separator. I found the same thing with date fields where the set of supported separation characters decreased.

                You have to switch to colon or use numbers instead which can be converted into time values via calculation.

                Jens

                • 5. Re: Time field calculations messed up
                  raybaudi
                    

                  Hi Jens

                   

                  > Point obviously is no longer supported as time separator.

                   

                  Who said that ?

                   

                  It depends by your control pannel settings.

                  • 6. Re: Time field calculations messed up
                    Jens Teich
                       Hi Daniele,

                    >> Point obviously is no longer supported as time separator.

                    > Who said that ?

                    I'm not sure. For my German installations point was forbidden also in 5.x/6.

                    > It depends by your control pannel settings.

                    Possible.

                    Jens





                    • 7. Re: Time field calculations messed up
                      flybynight
                        

                      I don't follow... My keypad is a standard US Apple keyboard. When I enter 8.12, it displays "0:00" and when I click into the field it shows exactly what I entered: "8.12" not "8,12". It works correctly in v6, but not v9. 

                      I would think it has to be a preference or formatting setting, but I can't figure it out.

                      You are right that it does seem to calculating whatever I enter as seconds if there is a decimal there. As a test, I entered "9" for a start and "17" for an end time and I get "8.00.00" when I click in the calculation field. But if I put "9.00" and "17.00" (or "9.30" and "17.30" ), I get "0.00.08" in the calculation field. 

                      In either case, that only shows when selected, otherwise the calculation field just shows "?" no matter how large I make the field. 

                      • 8. Re: Time field calculations messed up
                        raybaudi
                          

                        My Italian version support the point as separator.

                         

                        BTW: he said on his first post that the alert dialog asked him to enter the time with the point.

                        • 9. Re: Time field calculations messed up
                          flybynight
                            

                          Yes, it does say that I need to format it with periods. I am using the US version.

                          But, I cannot seem to enter it in a way that it likes. If I try entering with : I get the error. The odd thing is the error message says that it should look like "11.09.03 PM" if I enter that text exactly, I get the error. Also if I leave off the AM or PM. The only way I don't get the error is by entering #.## and then it doesn't calculate right.

                          Even on the records that were entered in v6 that appear to calculating right, it won't display unless you click into the field. So confused... 

                          • 10. Re: Time field calculations messed up
                            raybaudi
                              

                            FlyByNight

                             

                            I think that you have an international version of FileMaker or that you work on a file NOT created with your version.

                             

                            Try to create a new DB with only those 5 fields and see what appens.

                             

                            Then you can try to save your old file as a clone and import all the records into the clone.

                            • 11. Re: Time field calculations messed up
                              Jens Teich
                                 Help says:

                              | When entering data in time fields, type the date in the system format
                              | used when the file was created

                              So you might be successful with saving a copy of your file with

                              File -> Save Copy as ...

                              And check the setting of

                              File -> File options -> Tab Text -> Data Entry

                              Jens






                              • 12. Re: Time field calculations messed up
                                raybaudi
                                  

                                Another thought...

                                 

                                While in Layout Mode, select all those 5 fields and:

                                 

                                Format >> Time... >> check: leave data formatted as entered

                                 

                                Go and see what now you can see.

                                • 13. Re: Time field calculations messed up
                                  flybynight
                                    

                                  Thanks everyone for your help on this. Still not quite making sense, but here is what I have found.

                                  As for international version... we buy our upgrades directly from FMP in the US, so don't think that is possible. As I mentioned, I started with a template that shipped with v4 or v5(?) and made modifications from there, using it up to v6. Then, we didn't upgrade until v9. So, unless the template that shipped with a US version was made with an int'l version, I'm going to rule that out.

                                  Creating a new file if my Sys Prefs time format is set to use . then I get the same behavior. If I set Sys Prefs to use : then I get the same behavior if I enter time with . but correct behavior if I enter time with :

                                  Saving file as a copy didn't change anything.

                                  File -> File options -> Tab Text -> Data Entry ... that's the setting I was trying to remember, I still can't get it act right. Now, I can't find that setting in FMP v6. Do you remember where they put it in that version. I was going to check to see what it was set to there.

                                  If I select leave data formatted as entered, then I do see the contents without clicking into the field.

                                  Notice how Monday and Tuesday look the same, but different results in the calculation. 

                                  OK, now I can see what it's doing (kinda), but that still is not the behavior that I am looking for and had with v6. All I want is to enter time as #.## and have it display as #:## and calculate hours and minutes. Maybe it's just not possible anymore? Entering time with the point makes data entry for hundreds of times much quicker - we do payroll monthly, so there are actually 5 weeks in the layout. I don't think I could do it if I have to enter colons. Maybe I'll just have to keep a copy of v6 around for payroll?

                                  • 14. Re: Time field calculations messed up
                                    raybaudi
                                      

                                    > Saving file as a copy didn't change anything.

                                     

                                    Not as a copy but as a clone.

                                     

                                    This will set definitely the file to the internal format of your copy of FileMaker.

                                     

                                    BTW: what check is checked into the tab "text" >> Data Entry of the file option ?

                                    1 2 Previous Next