8 Replies Latest reply on Nov 30, 2009 11:57 PM by tas

    Unstored calc fields based on global are being cached - bad!

    etripoli

      Title

      Unstored calc fields based on global are being cached - bad!

      Post

      Database hosted on FMS9, client is FMA9.  Table 1 has a global field that is used to calculate values for 3 unstored calculation fields, by pulling values from a related table (Table 2).  Together, those 4 fields are used in a relationship to Table 3.  Further unstored calculated fields in Table 1 perform Average, Min, Max, StDev functions based on the related records in Table 3.

       

      Everything works fine the 1st time - with 960 records in Table 1, and 20,160 in Table 3, finds are quick, and the "Processing Query" only pops up briefly.  However, if I change the global field, I can see the calculated fields updated, but the finds are being performed on the cached data from the previous calculated values.  Also, the 'Process Query' box doesn't appear.

       

      It seems the only way to fix this is to go to 'Manage Database', double-click one of the fields in the table list, click 'OK', then 'OK' to save the changes. Somehow this must flush the cached data.

       

      My questions are: where is the data being cached, since none of the fields in Table 1 are index or stored?  Why doesn't the 'Refresh Window' or 'Flush Cache to Disk' commands work?  Is this a bug, or is there a fix, even a workaround that I can have normal users perform?

       

      Definitely a show-stopping problem.

       

      Update:

      Same problem with FMA10.  Also, using 'Manage Database', to force an update/flush on one client fixes the problem on other clients.

      One possible workaround: create a number/text field, no indexing, write a script to show all records and then replace the field contents of that 'junk' field with nothing.

       

        • 1. Re: Unstored calc fields based on global are being cached - bad!
          RSchaub
            

          Where the original globals set when you pushed the file up to tje server? If so they are the default value of the global.

          Globals should be empty at the time you move the file up to to server and set in a startup script. 

           

          Next does your find have the restore option on. If so it will find the same set everytime.

           

          Finallym why aren't you using relationships to do your finds... A field with calculated as "All" & ¶ &YourFieldName  will gather all the info in that field.

          Then use the new field as a filter for the portal. When the filter is set to all it shows all in the portal. If the filter is set to John it shows only the record withJohn in that field.

          Then use Go To Related Record (GTTR) option in a scripted Button.

           

          • 2. Re: Unstored calc fields based on global are being cached - bad!
            philmodjunk
              

            I use Go To Related Record frequently, BUT be advised that it can produce unexpected results when there are no related records to "go to". If you use this technique, you will need to either specifically check for the absence of matching related records using a count function or use Get(lasterror) to check immediately after the GTRR step.

            • 3. Re: Unstored calc fields based on global are being cached - bad!
              etripoli
                

              I created the global after the file was hosted, so it is blank upon opening the database.

               

              I'm not sure if I've ever seen a 'restore option' when using 'Find Mode ⌘F', but that almost sounds like the option in the 'Perform Find' script function from FM6. I'm not using a script to perform finds.

               

              Not using a portal either.  Table 1 is a static table with 960, and only 960 records.  Entering a value in the global field 'loads' the data into the layout from Table 3. Once the data is loaded, then the user performs their finds.  I have the tables and layouts setup this way because the users will only be working with one set of data at a time, and need to see the records from Table 3 in an aggregated format.

               

              It would appear that the displayed calculated data is cached/indexed as soon as 'Find Mode' is selected.  I can change the value of the global as many times as I want to and have the correct found set, but once I've performed the first find, subsequent finds are based on the previously cached/indexed data.

              • 4. Re: Unstored calc fields based on global are being cached - bad!
                etripoli
                   Now that you mention that Phil, I've had similar problems with GTRR, bringing up an incorrect set of records.  I tend to stay away from using it, unless absolutely necessary.
                • 5. Re: Unstored calc fields based on global are being cached - bad!
                  mrvodka
                    

                  etripoli wrote:
                  Now that you mention that Phil, I've had similar problems with GTRR, bringing up an incorrect set of records.  I tend to stay away from using it, unless absolutely necessary.

                  Why? I use GTRR all the time...


                  • 6. Re: Unstored calc fields based on global are being cached - bad!
                    etripoli
                      

                    But are you sure it's working?  I've been remote viewing a user's computer, and have them run a script that contains a simple GTRR, from records in the same database, and have it bring up the wrong set of records.  Especially when 1 record in the calling table should bring up multiple records in the called table.  Another one of our FMP designers have reported similar issues.

                     

                    Back to the subject at hand, it seems I'm the only one using static tables to load info from related tables based on globals.  Yay.

                     

                    I'm making a sample DB available for testing. But in order to test it properly, it must be uploaded to FMS.  Only then are the finds incorrect.

                    • 7. Re: Unstored calc fields based on global are being cached - bad!
                      comment_1
                         Refresh issues can always be expected when one relationship depends on the results of another. In most cases, Refresh Window [Flush cached join results] is sufficient to handle the issue.

                      I cannot test your file on a server, but I'd be curious to know what happens if you change the definitions of the cn_member_id_x fields to =

                      Let (
                      trigger = gn_group_id
                      ;
                      GetValue ( List ( Members::member_id ); x )
                      )


                      In any case, I don't see what this has to do with GTRR (your file doesn't even have a script or a button using GTRR). I would suspect that if GTRR seems to fail in this setup, it's only a symptom of the real issue.







                      • 8. Re: Unstored calc fields based on global are being cached - bad!
                        tas
                          

                        I was wondering if the problem I have is related to the one you are describing.

                         

                        I have two related tables: Items(ID,...) and Transactions(ItemID, Date, Quantity).  I want to compute the total quantity of transactions of an item before a specific date. So I add a global in Items (gEndDate) and add another relationship Transactions_OnDate where ID=ItemID AND gEndDate >=Date

                        I then add a calculated field in Items Qty_OnDate = Sum(Transactions_OnDate::Quantity)

                        Everything works perfectly.  I see correct records in both portals.  I am also able to search in the calculated field Qty_OnDate.

                        BUT when I move the database to server, portals and records display correct amounts but when I search for Qty_OnDate >0 after changing global gEndDate I get the wrong results: I get results of previous search, as if the Qty_OnDate has not updated even though I see correct results. If I make any changes to a record, then Qty_OnDate is updated properly and that record appears correctly after find. 

                         

                        Is this manifestation of the same problem? Did you find any other workarounds?