Calculation can not be stored.

[dangkevin]

Hi all,

I am new to database development and run into an unexpected error. I am sure someone here can help.

I got this error when I tried to store a calculation:

"The calculation "field_name" 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 think the problem occurred because I am doing a calculation and reference a field in another table so it does not allow me to store the result of the calculation.

It works like this:

Table 1 has a date field.

Table 2 has a calculation: year = year (table1::date_field).

The error will pop up if I try to store the calculation.

The "Auto Enter" option with calculation specification seem to work, but it does not get update if the table1::date_field change so it does not work in my case. FileMaker does not seem to have any trigger so I can create some script to run if the table1::date_field change.

Is there a way for me to store this in a calculation?

Any help would be appreciated.

Thanks.

[comment]

It's not an error - that's how Filemaker works (and for a good reason).

Why is the calculation being unstored a problem?

[dangkevin]

Hi,

I know that is the way FileMaker work, but I was trying to do a calculation, store the calculation to use as a key between the two tables. By not being able to store this calculation, the relationship does not work.

Does anybody know a way to get around this?

Thanks.

[comment]

There may be a way, but you need to describe your solution and your need in more detail.

[dangkevin]

Hi,

The database that I was trying to create has 2 tables like the example I used above. The primary key consist of 3 fields with a calculation like this:

__pk__table2 = field1 & field2 & month(table1::field3).

field1 and field2 are already in table2, but field3 is a date (mm/dd/yyyy) in table1. When I tried to store the key, it failed because the "month(table1::field3)" is reference from table1. I can use a script to copy the month(table1::field3) to another field in table2, but FileMaker does not seem to have any kind of trigger to run the script when the date changed.

If there is a way to store this primary key that would be great.

Thanks.

[comment]

I am sorry, but I cannot follow this. Please use some meaningful names for your tables and fields, and explain a little about the background and what you're trying to accomplish.

Obviously, if the date is coming from another table, then the two tables must already be related. It seems you want to establish ANOTHER relationship using the date - but you didn't say anything about that.

BTW, I am not sure that a key that includes a related attribute can be properly called "primary".

In any case, if you wanted to use such key on the parent side of a one-to-many relationship, it would work.

[dangkevin]

Hi,

I created a test file which is the same as what you think.

In the T4 table, there is field called "cYear" that can not be store and index. I wrote a script that would save the calculated value in "cYear" to "storedDate", but to me that does not seem to be best way to do it.

Thanks.

test.zip

[comment]

Try it this way.

test2.fp7.zip

[dangkevin]

Thanks, that works. I will try to fit that into my database.