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.
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)"?
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.
"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. :^)
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?
"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.
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?