1 2 Previous Next 18 Replies Latest reply on May 2, 2017 12:53 PM by JackRodges

    'force' calculation to update

    tleitzke

      My bid app is running with my fields split up into separate tables to help organize things. I have my 'Totals' (Creating final cost) on its own table, with everything (besides Bid ID, ID, and Rates (3 fields)) as Unstored Calculations.

       

      I will make changes on a separate, but related, table (Such as, add equipment or add a worker), but the calculations to not update automatically. They do update if I click on any calculation field that is displaying. I tried (probably stupidly) having a script to update a text field of "refresh" hoping it would update the calculation fields.

       

      Equipment, Accessories, Workers, Credits and Other Expenses use portals to add those parts onto bids- and they do work with all the calculations. But the changes do not force any updates of Totals calculation fields.

       

      Capture.PNG

      Capture.PNG

        • 1. Re: 'force' calculation to update
          philmodjunk

          Try commit records instead. You can manually test this without making any changes to your database design by clicking a blank area of your layout outside of any portal objects. If that causes your fields to update, you need only script in a commit records step.

          • 2. Re: 'force' calculation to update
            tleitzke

            I have never used "commit records" and have been rather puzzled by it since I first saw it. I went and read the filemaker page about it, and it is still not 100% clear what it does exactly (does it just force fields to update, does it push them to server/datebase causing them to update), but that isn't important I hope.

             

            So, do I just simply have a script do:

            Go To Layout[Totals];

            Commit Records [no dialog];

            Go To Layout[original];

             

             

            I don't want to screw myself over and create a malicious script that breaks my database somehow.

            • 3. Re: 'force' calculation to update
              philmodjunk

              Please follow my directions.

               

              Edit some data and then click a blank area of a layout. This causes FileMaker to commit your changes.

               

              Does this update your calculated values?

               

              If so, you can use a script and script trigger to make the update automatic using the commit records script step.

               

              Commit Records "saves" your changes from the layout where you edited some fields objects to enter new data back to the data base file itself.

              1 of 1 people found this helpful
              • 4. Re: 'force' calculation to update
                tleitzke

                That worked, Thank you so much. I was very annoying having to 'manually' updating those fields.

                 

                I just have to adjust where I place it (obviously several places, as I am using tabs to switch between each section on my bid form (equipment, information, accessories...etc)).

                • 5. Re: 'force' calculation to update
                  JackRodges

                  You might be amazed at the arguments i've had with clients when I told them to put a commit record at the end of every new record or set field, etc. Especially in loops.

                   

                  Also worth adding are Enter Browse Mode and Refresh Window at the end of your scripts.

                   

                  Note: some people believe FIleMaker saves ever change automatically. It does not and it is those rare instances that will cause problems. Also, the layouts have an option for save and don't save which can cause a problem.

                  • 6. Re: 'force' calculation to update
                    philmodjunk

                    FileMaker does save automatically. But it doesn't save immediately and that can cause issues--such as a crash that then loses the data not not yet committed, but doing a commit after every set field is overkill and can have detrimental results in some cases.

                    2 of 2 people found this helpful
                    • 7. Re: 'force' calculation to update
                      JackRodges

                      I've had this discussion many times... 

                      dbase, foxbase, etc. where the safest databases to use because they were created when computers were 1000 times slower and memory a million times less. They had to write to disk immediately. I tested FoxPro by entering data in fields and then pulling the plug. Never lost more than a few fields worth of data. This changed once Microsoft tried to speed up FoxPro and saved to memory...

                       

                      When dbs began saving to RAM rather than disk, 15 minutes, half an hours worth of data would be saved to RAM but not to disk. If the file was crashed, all that work was lost.

                       

                      I had a big argument about this (I had previously run numerous tests) on the 4D list and was told I was wrong. I suggested that the doubters check and report back. None did but I was never challenged on this again.

                       

                      There is a setting to force a cache flush when I believe saves everything to disk but who wants to do that when instead you can have the entire work for the last half an hour of 100 clients lost? 

                       

                      This is a characteristic of all dbs in the last 20 years.

                      • 8. Re: 'force' calculation to update
                        philmodjunk

                        Yet saving too soon can cause issues in a DB system. Consider the following scenario:

                         

                        Your script needs to create 50 new records and update data in 100 more. The script is set to commit data after every set field as you described.

                         

                        Partway through this process, a "glitch" disconnects or crashes the client. You now have your data in a state where only by carefully reviewing the data in many fields of many records, can you determine what records were created and what data was modified.

                         

                        Then consider this option:

                        The script is designed to do the batch update without any data committed until the last set field is executed. Should a catastrophe interrupt the process, none of the data is saved to the DB and you simply need to re-run the script to get your records created and updated as needed. This is a much simpler and leads to better data integrity.

                         

                        See the articles on Database Transaction on the Geist Interactive site if you'd like to learn more.

                        • 9. Re: 'force' calculation to update
                          JackRodges

                          There was an example of how to clear all of the data as in your second example. I asked how a customer might feel if after having the clerk ring up 100 items bought for gifts if the application found one item not in the db and then canceled the entire order?

                           

                          So, there is no arbitrary rule for your two conditions that makes one better than another.

                           

                          One consideration is to add an updated flag for the record so that in the event of a crash, that item would be ignored.

                           

                          Every idea, every script is perfect until it is deployed... 

                           

                          I remember back in 1986 writing a basic program for the Tandy 100. I was hoping to use it for creating estimates for our air conditioning company. After much testing I gave the 100 to my dad to test and he entered a 0 and immediately crashed my app... 

                           

                          Also consider that after successfully completing the script that recorded all of the transactions in FileMaker or other db app, if your file crashes before the data in memory is saved to disk then its lost anyway which is the original point made.

                           

                          For instance, and invoice item stands alone. An in between file creates a link between the invoice item and the inventory table. The invoice item has a calc to determine if the in between record does not exist and at any time can create a new one. This real time check provides a bit more data integrity than using a transactional loop only. The item, inbetween and inventory records can check for missing records.

                          • 10. Re: 'force' calculation to update
                            philmodjunk

                            I think you missed my point. If a problem occurs, it's better to have none of the data changed than a random portion of the process completed. The update can then be repeated to correct for the interruption--something that is far more complex to do if you have to check data, logs, backups, audit log system or whatever to figure out what "piece" of the update took place.

                             

                            Committing the data after every field change prevents that option by making every change it's own transaction. It also makes reverting a group of changes much more complicated and difficult.

                             

                            So, I stand by my opinion that following every Set Field with a commit is not really a good idea as a general rule.

                            • 11. Re: 'force' calculation to update
                              tleitzke

                              philmodjunk wrote:



                              So, I stand by my opinion that following every Set Field with a commit is not really a good idea as a general rule.

                              So, it would be safer/better to commit at more important parts (Like changing tabs), or even less frequent than that?

                              • 12. Re: 'force' calculation to update
                                philmodjunk

                                You really have to read over both our arguments Pro and Con, look over your own current design and then decide for yourself.

                                 

                                Looking up the articles on Database Transactions over on the Geist Interactive site might not be a bad idea either. It was a recommendation to read that article that opened my eyes to this potential issue. Much depends on what should make up a single "transaction" to post data back to your file.

                                 

                                We were discussing general applications of commit records. You have to look at the specifics, including the fact that you sometimes have to commit records to get things to work the way that you want.

                                • 13. Re: 'force' calculation to update
                                  JackRodges

                                  I respect everyone's right to have an opinion and believe that it is better than mine... 

                                  • 14. Re: 'force' calculation to update
                                    JackRodges

                                    Another idea would be to have a transaction table with an id for the transaction, a start time and an end time and a validation for it being finished.

                                     

                                    Now wrap the transaction inside this id and if the transaction isn't successfully created you can backtrack, etc.

                                     

                                    Just don't cancel that stressed out mother's purchase of 100 party items at the checkout register just because your database shows that one item is not in stock...

                                    1 2 Previous Next