5 Replies Latest reply on May 25, 2010 6:35 AM by VinceDolan

    FMSA 11 failing to search un-stored Calc field correctly.

    VinceDolan

      Summary

      FMSA 11 failing to search un-stored Calc field correctly.

      Description of the issue

      Folks Having a strange problem in any our databases that is hosted on a Mac Server OS X 10.5.8 Xserve. After the server has been up for a month or so, it starts giving us wrong found sets, when searching some of the unstored calc fields. Restarting the server solves the problem (closing and reopening the file does not). If memory servers me correctly (which if often doesn't) FMSA does the number crunching prior to the search on any non-stored calc fields, if all the calc references are local to that file. Is the server getting loopy or is it the file. No errors in any logs btw. Win and Mac FMPA clients show same issue, until restarting of server of course. This problem manifests itself on both Windows and Mac v11 clients, I haven't tested it on IWP or CWP.Vince Dolan Waymar Industries

        • 1. Re: FMSA 11 failing to search un-stored Calc field correctly.
          philmodjunk

          I would suspect the file over the server. Filemaker indexes do get corrupted from time to time and you may need at least to re-index the problem field. In you case, your ustored calculations may draw data from a stored field with a bad index.

           

          You ultimately may need to replace the file with an older back up as there could be problem with the file that causes the index to go bad.

           

          You can re-index fields individually by turning indexing for that field on and off. You can re-index all fields by importing your records into a clone of your file. Recover also rebuilds your indexes though I don't recommend using a recovered copy for regular use.

          • 2. Re: FMSA 11 failing to search un-stored Calc field correctly.
            WoodApple

            Try to reindex the key fields in the relation that your unstored calc referrers too.

            • 3. Re: FMSA 11 failing to search un-stored Calc field correctly.
              VinceDolan

              Folks

               

              Sorry tried that (reindexing that is). All the databases (all 124 files) show this issue. I can create a new database, upload it and it will exhibit the same issue. It is only resolved by restarting the FMSA database server. It is not an index issue (not on the file level anyway.) When it starts happening again in a month or so, I will have a chance to do some more exploration and get back to you all.

               

              Vince Dolan

              Waymar Industries

              • 4. Re: FMSA 11 failing to search un-stored Calc field correctly.
                philmodjunk

                A more detailed description of the calculation fields involved would also help us explore the issue.

                • 5. Re: FMSA 11 failing to search un-stored Calc field correctly.
                  VinceDolan

                  Phil

                   

                  Any find on any unstored calc will fail as long as all of the references are contained within the file that contains the calc. This is what leads me to believe that the Server is at fault because:

                   

                  A: Only a server restart will fix the problem (other than opening the files locally with a client -Win or Mac)

                  B: In the above scenario, it is the server, not the client that solves the calculation prior to searching the results.

                   

                  When I created my test database (this was done to remove any variables introduced by using a corrupt file), I just created 2 tables (Master and Items). I then created a key field in each table and created a relationship based on those fields. I then added a number field called "QTY" in the items table. I then created a calc field in master that Sum(Items::QTY) with its result being a number. Prior to restart I could not search that field and get results, after restart of server, it perform as expected.

                   

                  Our server hosts 124 files, about 10 of which are multiple table files. Initially this solution first began life in FMP 3.0, hence the number of files required to normalize the data. The issue only arises in the multi-table files that have been either developed or consolidated post FMP 7

                   

                  Vince Dolan

                  Waymar Industries