5 Replies Latest reply on Jul 7, 2014 12:47 PM by philmodjunk

    Unstored calculation field does not update after importing data

    Wicktor

      Title

      Unstored calculation field does not update after importing data

      Post

           HI all,

           First time I need to post for help... the forum is very helpful and until now I was able to find an answer to all my questions just browsing the various posts.

           Over time I developed a complex solution (34 tables, some  230 layouts, with some 500.000 records) running FMPro 12 Server with 7 Macs using FMPro 12 Advanced.

           Two months ago I made a clone of the solution, I changed layouts, and I added some fields (but did not change the original fields and did not change the relations).

           Now I am trying to import data from the working solution into the clone with updated layouts.

           Everything works fine except a significant detail:

           Importing data into one specific table (File->Import Records->File...) and using Matching fields I get a correct import of all data BUT a specific calculated field unstored (since it retrieve data from a related table) does not show any information. Interestingly if at the end of import I run a single line script "Refresh Window" (with "Flush cached join results" checked) the unsorted calculated field show correctly the information.

           Ok... I could just use that simple script after importing and all done....but the fact that the unstored calculation does not update makes me very worried that something else would cause the issue. Could it be corrupted even if consistency check is fine ? Or anything else ?

           Thank you for your kind help,

           Victor

        • 1. Re: Unstored calculation field does not update after importing data
          philmodjunk

               Seems a bit odd that it didn't update, running a consistency check and a recover on the file as a way to check for possible issues would not be a bad idea... You might try this first on the clone as this will be much quicker than a file where a table has a half million records.

               You can also set up a copy of this file on a computer and let recover run on the copy so as to not tie up the working copy. With some of my large files, I've even run this kind of check on a copy overnight and checked back on it the next morning...

          • 2. Re: Unstored calculation field does not update after importing data
            Wicktor

                 Thank you PhilModJunk for your answer.

                 I already did consistency check and showed no problem on both source and target.

                 On target file (the clone) I did a recovery: The result was fine: "Recover built a new database without detecting any problems....0 items modified in both schema and structure".

                 However opening the recovered file I saw a a new Table called "Recovered Library" with 2 fields and 15 records. One field id called "Recovered Blob" and the other "From Table" (in the past I saw posts regarding recovered Blobs but never had such a problem). Both fields refer to a Table that have nothing to do with the table where I have the calculated field issue. In specific they refer to a table where I have stored global fields containers with colours that I use for backgrounds and buttons in all the layouts, in fact the Recovered Blob field shows 15 colours that are stored into the containers fields.

                 Anyway...I just used the recovered file to import a sample of data from the original: same exact problem, the unstored calculated field does not show anything and again if I run the script "Refresh Window" (with "Flush cached join results", then the unstored calculated field immediately shows data properly in all records.

                  

            • 3. Re: Unstored calculation field does not update after importing data
              philmodjunk

                   It could simply be that you have a complex calculation that references fields from a table occurrence that is not immediately adjacent to the field's "context" table occurrence and thus fails to update automatically due the need to "tunnel" through intervening table occurrences.

              • 4. Re: Unstored calculation field does not update after importing data
                Wicktor

                Hi PhilModJunk,

                     I checked the solution also with Inspector Pro: no flags.

                     As always I want to dedicate and solve... On a copy of the clone I made an exact copy of the Table from which the unstirred calculation brings data.

                     I made on that Table the same exact relations of the original table and modified calculations to bring data from that Table.

                     Tried to import and... it works !...... I am already remaking all the other internal calculations and then will delete the trouble Table.

                     My personal conclusion, but I might be wrong, is that there is some kind of corruption that even Recovery is not able to detect (I saw many posts that Recovery is not always "proof").

                     BTW, do you have any suggestion about the Recovery Library and Blob ? Is it safe to simple delete that Table ?

                     Thank you for being with me during this trouble,

                     Vic

                      

                • 5. Re: Unstored calculation field does not update after importing data
                  philmodjunk

                       Things to keep in mind about Recover:

                       While Recover almost always detects and fully corrects any problems with your file...

                         
                  1.           The recovered copy may behave differently even if recover reports "no problems found".
                  2.      
                  3.           Recover does not detect all problems
                  4.      
                  5.           Recover doesn't always fix all problems correctly
                  6.      
                  7.           Best Practice is to never put a recovered copy back into regular use or development. Instead, replace the damaged file with an undamaged back up copy if this is at all possible. You may have to save a clone of the back up copy and import all data from your recovered copy to get a working copy with the most up to date information possible.

                        

                       And here's a knowledgebase article that you may find useful: What to do when your file is corrupt (KB5421).

                       I see no reason not to delete that table, but I wouldn't use the recovered copy here in the first place if I can come up with a practical alternative to doing so.