3 Replies Latest reply on Jun 5, 2009 9:39 AM by philmodjunk

    Missing index

    pvhaute

      Title

      Missing index

      Post

      Hi,

       

      My portal doesn't show any records.

       

      Layout table: 'Inv' - Linked field: 'GLO_Yes' (=global text variable that is always "Yes")

      Portal table: 'Auf' - Linked field: 'Inv_flag' (=calculated variable (text) that containes "Yes" or "No" based on the value in other fields of that table)

       

      It seems that the portal doesn't show the records of 'Auf' as i cannot index the 'Inv_flag'-field.

      Re-defining the 'Inv_flag'-field to a text field with calculated value (replacing existing values) does not seem to be a solution either as the recalculation isn't triggered appropriately.

       

      This seems to me like a relatively common situation ... isn't there a solution?

       

      Many thanks for your help,

       

       

       

        • 1. Re: Missing index
          philmodjunk
            

          From your post I don't see why your field can't be a stored calculation. It will automatically update whenever a referenced field is changed. The only time you can't store and index the field in this way is if your calculation refers to a field that is in a different related table.

           

          Note that this is a field of Type calculation--not a field with an auto-entered calculation.

          • 2. Re: Missing index
            pvhaute
              

            Thanks Phil!

             

            The calculation field indeed receives its value from a related table ... which made me change the relationships so that the portal-table is linked to the layout-table through this 'third' table (from wich the calculation receives the value) ... works perfect this way!

             

            A similar issue (of not be able to store a calculation) i have when using a global field in my formula (at least FM says it cannot store this) ... why is this? Doesn't a global field have its value fixed and stored in the same table (and hence receive the same treatment as an ordinary other field)?

            (i found a workaround for this, but just isn't as nice as could be)

             

            To these 'storable' calculations: i tried to find a way around the issue by using a 'calculated value' (as mentioned in my first posting) ... can you tell me what the use/difference is between a 'calculated value (replacing existing values)' and a 'calculation field'?

             

            Thanks a lot,

            Piet

            • 3. Re: Missing index
              philmodjunk
                

              I've banged my head on this issue a time or three myself. I don't have any inside track on why the FMP software engineers do what they do, I can only make informed speculations based on my own observations and knowledge of basic database design.

               

              Calculation fields will automatically update whenever the fields they reference change. If all the referenced fields are not global and are part of the same record as the calculation field they may be stored and indexed. An index is an internal data structure that is used by the database application for sorting, finding, for matching related records and for populating certain value lists. Given that global fields not only store values that are not local to a specific record but also may store a different value for each user in a shared database, I can't imagine there is any easy way to "store and index" any calculation that references a global field.

               

              Consider, if you change the value of an ordinary data field, your change affects a single record and all users will see that change. If you change a global field, hundreds, thousands or even millions of records may be affected and those changes are only visible to your session if the file is hosted over the network. Ever try to find records in a networked file where there are hundreds of thousands of records in the table and you are entering criteria in an unstored field? You have to wait while Filemaker builds an index based on that field before it can find your records. If we could store/index calculation fields that reference global fields or fields from a related table, we would likely see those same performance hits repeatedly each time you modify the contents of a global field that was referenced by a stored calculation field.

               

              Using an specified calculation as an auto-enter field option is one way to avoid the problem with unstored/unindexed fields, but it has it's own built in limitation. An auto-enter operation normally happens only when the record is first created. The field will not automatically update when one of the fields referenced in the calculation are updated. That's why you can store and index the field, but now you have to explicitly manage any updates to that field.

               

              I know of two basic work-arounds; neither are perfect.

               

              1. Redesign your database so that your calculation key fields only refer to local fields.
              2. Design scripts that update the key field at "strategic" times. How you implement this trick depends on the version of filemaker you are using, the structure of your database and the needs of your users.

               

              Hope that helps.