13 Replies Latest reply on May 31, 2012 4:25 PM by david@elsupergrillo.com

    Auto-Updating calculation field

    david@elsupergrillo.com

      Apparently, there's no way to reply or comment on discussions in the archive. Using the information from the discussion at https://fmdev.filemaker.com/message/32518#32518, and the reference guide, I have created a calculation field to hold elapsed time since a record's creation. A time field, "TimeStart" is automatically entered upon record creation. A second, unstored calculation field, "CurrentTime" is defined as simply "Get ( CurrentTime )." Then the "TimeElapsed" field, also unstored calculation "Case( ( CurrentTime - TimeStart ) > 60; Round (((CurrentTime - TimeStart)/60) ; 0 ) & " minutes";"0 minutes"

      )."

       

      I have a script "ElapsedTimer" defined "Insert Calculated Result [Table::TimeElapsed], and another script "StartElapsedTimer" defined as "Install OnTimer Script ["ElapsedTimer"; Interval: 60]." This Install OnTimer script is assigned to the OnLayoutEnter script trigger for a layout which displays the TimeElapsed field. That field on the layout has been prevented from entry, but the ElapsedTimer script, when run, puts the cursor in the field anyway. If I uncheck "Go to target field" in the script's definition, it erases the field defined in the script. But there are a couple of problems with it: 1) if, after the script is run and the cursor is active in the field, the user doesn't click anywhere to remove the focus from the field, the script won't run again until the user does click somewhere else; 2) If, when the script runs, the user is actively typing somewhere on the layout, when the TimeElapsed field receives the focus, the typed data is sent to the TimeElapsed field, and the user gets an error because the field cannot receive the typed text.

       

      Is there a better way to cause the TimeElapsed field to update every 60 seconds without the field receiving focus on the layout?

        • 1. Re: Auto-Updating calculation field
          comment

          Refreshing the window would also refresh the unstored calculation - however, it could be equally distracting to the user.

           

          Why don't you use a web viewer to show the elapsed time? That would happily tick away without requiring any of this mumbo-jumbo.

          • 2. Re: Auto-Updating calculation field
            BruceHerbach

            Would it be  possible to put a merge Variable on the layout and have the OnTimer script update the value of the variable.   I believe that this would eliminate the interaction with the user. 

             

            The original discussion was about FM10, if you are still using 10 then this suggestion would not be helpful, but should work in 11 and 12.

             

            HTH
            Bruce

            • 3. Re: Auto-Updating calculation field
              comment

              Bruce Herbach wrote:

               

              Would it be  possible to put a merge Variable on the layout and have the OnTimer script update the value of the variable. 

               

              It doesn't matter if it's a merged variable or a field (merged or not) - you still need to refresh the window in order to see the change.

              • 4. Re: Auto-Updating calculation field
                david@elsupergrillo.com

                A basic question that comes to mind is, "Why is there a checkbox next to the button where you specify the target field to get updated that says, 'Go to target field,' if you can't define a target field without entering the field. It seems like just basic bad interface design to appear to give the user a choice where none actually exists.

                 

                Anyway, I figured it out. Just flipping through the various scripting options, discovered that on some other commands, the checkbox doesn't say, "Go to target field." So I made the TimeElapsed field a number instead of calculation and rewrote the script to Set Field instead of Insert Calculated Result. Then I put the calculation in the script instead of the field. Viola! The field updates on the layout, but doesn't enter the field and steal the focus.

                 

                I know the stock answer for these things seems to always be a web view, but I'm going to be billing customers based on that number and I want them to get an accurate reading on their time from the same source that the bill is going to be calculated from, so I wanted to avoid giving them a timer that didn't come from the database itself.

                 

                Thank you all.

                • 5. Re: Auto-Updating calculation field
                  BruceHerbach

                  David,

                   

                  Glad you figured it out.  Attached is a test db with a merge variable showing the time.  The time will update every 10 seconds.  you can enter and modify the text field,  and the refresh that displays the time update doesn't affect what is in the text field.

                   

                  HTH
                  Bruce

                  • 6. Re: Auto-Updating calculation field
                    Vaughan

                    Yes, use Set Field, not the Insert script steps.

                     

                    To measure the elapsed time you also need to record the date of creation and the current date, or use timestamps instead (creation and current) which include date and time together.

                     

                    Otherwise a record created exactly 24 hours ago (or an exact multiple of 24 hours) will display an elapsed time of zero.

                    • 7. Re: Auto-Updating calculation field
                      Vaughan

                      david@elsupergrillo.com wrote:

                       

                      I know the stock answer for these things seems to always be a web view, but I'm going to be billing customers based on that number and I want them to get an accurate reading on their time from the same source that the bill is going to be calculated from, so I wanted to avoid giving them a timer that didn't come from the database itself.

                       

                      What makes you think the web viewer won't be displaying the time from the database?

                      • 8. Re: Auto-Updating calculation field
                        comment

                        david@elsupergrillo.com wrote:

                         

                        I know the stock answer for these things seems to always be a web view, but I'm going to be billing customers based on that number and I want them to get an accurate reading on their time from the same source that the bill is going to be calculated from, so I wanted to avoid giving them a timer that didn't come from the database itself.

                         

                        In your original post, you described a timer that measures "elapsed time since a record's creation". I don't think you would want to bill your customers based on that, since such clock will keep ticking until eternity.

                         

                        In any case, as Vaughan noted, the web viewer in this case would be used only as a display device; the clock is the same computer clock, and the starting value would be coming from your data.

                         

                         

                        See also:

                        https://fmdev.filemaker.com/message/75639#75639

                        • 9. Re: Auto-Updating calculation field
                          david@elsupergrillo.com

                          The clock starts ticking when the customer logs in. The customer layout contains a logout button that stops the clock ticking, populates an actual stop time field and (when I get that far) will build and print an invoice, then destroy that record in the database.

                           

                          As for what I could do with a web viewer, I would love to be able to create a web viewer that displays records from a table or layout in my database within another layout. I don't suppose that's possible?

                          • 10. Re: Auto-Updating calculation field
                            comment

                            david@elsupergrillo.com wrote:

                             

                            The customer layout contains a logout button that stops the clock ticking, populates an actual stop time field ...

                             

                            Well, that's not what your calculation does - it does not contain a reference to a stop time field. But that's a minor point, since both a calculation field and a web viewer can be adjusted accordingly.

                             

                             

                             

                            david@elsupergrillo.com

                             

                            ... then destroy that record in the database.

                             

                             

                            I don't think you'd want to do that. After all, the record is an item of your invoice, isn't it?

                             

                             

                            david@elsupergrillo.com wrote:

                             

                            As for what I could do with a web viewer, I would love to be able to create a web viewer that displays records from a table or layout in my database within another layout. I don't suppose that's possible?

                             

                            Not sure what exactly that means. If the records are accessible, say through a relationship, there is no reason why they couldn't be displayed inside a web viewer.

                            • 11. Re: Auto-Updating calculation field
                              david@elsupergrillo.com

                              I have, actually, 4 "time" fields in my table ("time" in quotes, because 2 of the 4 are actually number fields): TimeStart (creation time), TimeElapsed (number field, populated every 60 seconds my script triggered by the Timer, TimeEnd (populated by the script that triggers when the customer clicks the logout button, and TimeRented (calculated from TimeEnd - TimeStart when user clicks logout button.

                               

                              This is a simple system to help control use of rental computers at my cafe. The idea is that a customer sits down and logs in. They are presented a full-screen interface that contains a web browser (in a web viewer on a FM layout), buttons for common websites, a small advert for some things in the store, and a timer so they can watch their usage. When they finish, they will click the logout button, which will calculate their tab and print an invoice at the cash register. Since there is no way to connect the database behind all this to my POS system (Quickbooks POS), there is no point in keeping the data beyond that visit.

                               

                              My thought is that a table would contain the common website button's URLs, names, and icons. Hopefully, I can find a way to do that, then have the buttons displayed in part of the layout that is the user's interface (maybe with a portal, but I'm not sure how I can create a relationship where none actually exists).

                               

                              Then, another table would contain small banner ads and/or videos in container fields that would rotate on the UI layout. That, I'm pretty sure, should be fairly simple. It's the website buttons I'm most unsure about. In a pinch, I can just applescript the building of a simple HTML page on the desktop and load it into a web viewer beside the main website display.

                               

                              Thank you all for your ideas

                              • 12. Re: Auto-Updating calculation field
                                comment

                                david@elsupergrillo.com wrote:

                                 

                                My thought is that a table would contain the common website button's URLs, names, and icons. Hopefully, I can find a way to do that, then have the buttons displayed in part of the layout that is the user's interface (maybe with a portal, but I'm not sure how I can create a relationship where none actually exists).

                                Use the x relational operator, with any 2 fields acting as matchfields. That will make all records in the URLs table related (i.e. a Cartesian product relationship) and shown in a portal.

                                • 13. Re: Auto-Updating calculation field
                                  david@elsupergrillo.com

                                  Thank you. That worked (I think). That is, I didn't understand much of what you said about x relational operators and Cartesian products. But I fumbled my way through, creating a relationship between the BookmarkID field in my Bookmarks table and the RentalID field in my Rentals table, using the x operator. On my layout, I created a portal showing records from the Bookmarks table, including only the Icon field and Name field with no lables. I placed a button on top, with it's Button Setup set to run a script (Set Web Viewer [Object Name: "BrowserWindow"; URL: Bookmarks::URL] Set Field [Rentals::ThisURL; BookMarks::URL]. When the user clicks a Bookmark button, the script loads the appropriate page into a large Web Viewer and updates a field above it that shows the URL that the Web Viewer is displaying. Of course, that URL doesn't update as the user clicks links, but they can type another URL into it and go to that address. I even scripted the field so that if the user didn't type the required "http://", it adds it before submitting the URL to the Web Viewer. My little project is coming together, despite my inability to wrap my head around some Filemaker concepts. And I'm learning a lot along the way.