3 Replies Latest reply on Dec 30, 2014 12:09 PM by jormond

    unindexed field search not working

    NancyAdelman

      All,

      FM 12, database with a table of invoices and related table of invoice line items

      The invoice table has an un-indexed calc field (Invoice Total) that sums the related invoice lines to get an Invoice total.

       

      I know the find in the Invoice Total field will be slow but it doesn't work. Example; find ">1000" in the finds all invoices.

      The un-indexed find works when the file is taken off the server and used locally. the un-indexed find does not work when the file is hosted.

      Any ideas how to fix this?

       

      As a temporary work around I created an indexed field and am using a nightly script to move the Invoice Total data to the indexed field. I do not want this to be a permanent solution.

       

      Nancy Adelman

      www.adelmandatabases.com

        • 1. Re: unindexed field search not working
          wimdecorte

          NancyAdelman wrote:

           

          As a temporary work around I created an indexed field and am using a nightly script to move the Invoice Total data to the indexed field. I do not want this to be a permanent solution.

           

           

          I think you must, Nancy.  Having the invoice total as an unstored calc is going to have so many downstream performance issues (reporting,...).  Creating invoices is a typical use-case for a transactional approach where you set the invoice fields based on the invoice items and then lock it down.  All scripted, no calc fields.

          Any change to an invoice should be a controlled and audited workflow anyway, not something left up to calculated fields.

          • 2. Re: unindexed field search not working
            keywords

            Re: "Any change to an invoice should be a controlled and audited workflow anyway, not something left up to calculated fields."

             

            Quite so. While an invoice is being prepared there may be a case for the total to be fluid, but surely business rules should require that once the invoice is completed and issued it gets locked down.

             

            Re: "using a nightly script to move the Invoice Total data to the indexed field"

             

            Why not simply use an auto enter calc? You should be able to devise a calc which allows it to update if a line item is changed/added/deleted and then lock once an invoice is finalised.

            • 3. Re: unindexed field search not working
              jormond

              I will also lend my voice to Wim's suggestion. Go scripted. There are a lot of pitfalls to even using the Auto-enter calcs if you aren't 100% certain what and when FileMaker updates them. Scripting give you full control over the actions.

               

              This was a lesson I learned the hard way several years ago. It's a touch more work up front, but easier to maintain in the long run. And easier to debug when something goes astray. Plus the difference in performance both with data entry and reporting will be very significant.