6 Replies Latest reply on May 28, 2011 11:10 AM by brett_

    How to set calculation field as stored?

    brett_

      Title

      How to set calculation field as stored?

      Post

      I have a calculation field, GHivsPrevClose, that uses the following calculation:

      GHi - table2::Close Price

      GHi is just a number and not calculated.  Its indexing is set as None with automatically create indexes as needed.  table2 is a self join on table1.  Close Price is also a non calculated number field and indexed.  

      I keep getting this error when I try to uncheck "Do not store calculation results..."

      The calculation “GHivsPrevClose” cannot be stored or indexed because it references a related field, a summary field, an unstored calculation field, or a field with global storage.

      I cannot see where FM is having an issue with this field.  Is there a way to force FM Advanced to show which field it is referring to?

        • 1. Re: How to set calculation field as stored?
          LaRetta_1

          FileMaker cannot index a calculation when it relies on a related table.  The reason is simply because, if the data changes in the related table, the current table (where the calculation resides) cannot *see* the change so it won't update.  For FileMaker to know that the related data changes, it must set the current calculation to unstored.

          Without seeing your structure I cannot say for sure, but it would probably work if the gHvsPrevClose field is instead a standard number field set as auto-enter calculation or lookup. But if you do that, it will update automatically if the table2::Close Price changes.

          Is there a reason you require that this be indexed?  Even if unstored, it can still be searched; it just can't be used as a value list or as a key in a relationship.

          • 2. Re: How to set calculation field as stored?
            brett_

            Thanks. I see what you are referring too.  To test it, I create a number field and selected Auto-Enter Calculated Value and pasted the calculation into there.  The field is empty.  I went back and unchecked "Do not replace existing value of field (if any)".  Still empty.  Do you have some idea what I'm doing wrong?

            If I want to modify the current field this way, I'd like all values in that field to bake in so they do not disappear or change and become static.  In that case, would I also select "Do not replace existing value of field (if any)"?

            • 3. Re: How to set calculation field as stored?
              brett_

              Actually, after modifying a row in table1, I see the new column is putting the value there.  So it seems the column is working correctly. I just need the existing values to come through as well. 

              • 4. Re: How to set calculation field as stored?
                LaRetta_1

                "I just need the existing values to come through as well. "

                Then you will need to find those with empty field and use Replace Field Contents[] to set them to the value.  Auto-Enter works when the record is created. If you leave 'do not replace' checked then it will 'bake in' and that is why prior records won't change just because you added the calculation.  :^)

                • 5. Re: How to set calculation field as stored?
                  brett_

                  Thanks LaRetta.  I understand where I need to go, I'm just not sure how to get there.  This calculation field needs to change to a number field.  But once I do that, all values in the field are wiped out.  So there isn't any 'bake in' process.  From what I understand, to reach the Auto-Enter calculation option, I first must change the field to a number.  Hence the dilemma.  I'm still don't follow how to keep existing values while this field is changed to a number type.  Do you have some suggestions?

                  Also,

                  "Then you will need to find those with empty field and use Replace Field Contents[] to set them to the value"

                  Those that have no value in this field are valid.  That's because the values to calculate off of are not there yet.  I'm still working on entering all needed values.

                  • 6. Re: How to set calculation field as stored?
                    brett_

                    Ok, looks like I've got it.  I created a new temp number field and copies all calculated values to it.  I then change the original field to number and copied the temp values back into it.  I then added the Auto-Enter calculation option.  Looks fine.  Is that the correct process?