Trouble getting a calculation field to evaluate.

[jdu98a]

I'll try to make things as clear as possible. Here's my setup.

Table 1

[color:gray][Field A] - Account id, same value across several records

[Field B] - unique record id

[Field C] - numeric value

Table 2

[color:gray][Field D] - Account id, auto-enter value based on variable value at the time of import script

[Field E] - record id directly imported from Table 1

[Field F] - calc field that retrieves the value of [Table 1::Field C] based on the following relationship

Relationship

[color:gray][Table 1::Field A] = [Table 2::Field D]

[Table 1::Field B] = [Table 2::Field E]

My script imports records from [color:gray]Table 1 into [color:gray]Table 2. The values of [color:gray][Table 2::Field D] are auto-entered from the value of a variable and the values of [color:gray][Table 2::Field E] are imported from [color:gray][Table 1::Field B]. When the script is finished, the calculation field ([color:gray]Field F) in [color:gray]Table 2 has not evaluated. I am assuming this has something to do with the order in which Filemaker evaluates things like auto-enter fields. Any modification made to [color:gray][Table 1::Field C] in any record causes all records in [color:gray]Table 2 to evaluate [color:gray][Field F]. How can I work around this?

[comment]

I'll try to make things as clear as possible.

Things could be a LOT clearer if you had used names instead of cryptic codes:

----------------------------------------------------------

Source

AccountID - Account id, same value across several records

DataID - unique record id

Value - numeric value

Target

AccountID - Account id, auto-enter value based on variable value at the time of import script

DataID - record id directly imported from Source

cValue - calc field that retrieves the value of Source::Value based on the following relationship

Relationship

Source::AccountID = Target::AccountID

Source::DataID = Target::DataID

My script imports records from Source into Target. The values of Target::AccountID are auto-entered from the value of a variable and the values of Target::DataID are imported from Source::DataID. When the script is finished, the calculation field (cValue) in Target has not evaluated. I am assuming this has something to do with the order in which Filemaker evaluates things like auto-enter fields. Any modification made to Source::Value in any record causes all records in Target to evaluate cValue. How can I work around this?

----------------------------------------------------------

Now we could use additional clarification regarding the "auto-enter value based on variable value at the time of import script" and the exact formula "that retrieves the value of Source::Value based on the following relationship".

Also, if the DataID value is unique, why cannot it be used as the sole matchfield?

Edited November 18, 2010 by Guest

[jdu98a]

Regarding the auto-enter value of the AccountID field in the Target table:

When the script is triggered a variable is set ($accountid) and the AccountID field of the Target table is set to auto-enter that value.

Regarding the formula of cValue in the Target table:

It is simply

Source::Value - 'Source' being the related table and 'Value' being the field who's value I want to retrieve.

Also, if the DataID value is unique, why cannot it be used as the sole matchfield?

I should clarify. DataID in the Source table is only unique within a particular account. So it is possible that the same value could be used in two different accounts. That is why I use both fields in the relationship.

Edited November 18, 2010 by Guest

[comment]

Regarding the auto-enter value of the AccountID field in the Target table:

When the script is triggered a variable is set ($accountid) and the AccountID field of the Target table is set to auto-enter that value.

OK, but where is this value coming from and why would it match the AccountID field in the Source table?

I am also puzzled regarding the overall purpose of the entire operation.

Edited November 18, 2010 by Guest

[jdu98a]

The value comes from whatever account the user is currently working with. The field is set to auto-enter to keep the record creation process in the Target table consistent. I actually tried simply importing the values from the AccountID field along with the DataID to see if it was the auto-enter that was causing the issue. But it did not fix the problem.

The purpose of the operation is to populate the Target table with records who's cValue field values will be updated whenever Value field values are updated in the Source table.

[comment]

The value comes from whatever account the user is currently working with.

I am afraid I still don't follow: how can you be sure that you only import records of the current account?

The purpose of the operation is to populate the Target table with records who's cValue field values will be updated whenever Value field values are updated in the Source table.

Wouldn't it make more sense to show the source records directly in a portal placed on a layout of Accounts table, based on matching AccountID?

[jdu98a]

I am afraid I still don't follow: how can you be sure that you only import records of the current account?

Before the import step I make sure that the current found set of the Source table contains only records from the current account.

Wouldn't it make more sense to show the source records directly in a portal placed on a layout of Accounts table, based on matching AccountID?

In most cases, yes, but in this solution the Target table needs to actually contain the records to facilitate other purposes not spelled out in my question.

Edited November 18, 2010 by Guest

[comment]

Well, I cannot reproduce your issue, so there must be something else. Does this work for you?

ImportTest.zip

[jdu98a]

It appears that your sample is set up exactly the way my solution is. The calc field in your sample works just fine. I'm looking for another factor that is interfering with my solution.

[jdu98a]

SOLUTION FOUND!

I messed around a lot with your sample, and I too was never able replicate the behavior. However, I finally correct the issue in my own database by using a Refresh Window script step immediately after the import. Specifically one with the 'Flush cached join results' option selected. It's a rarely used option, at least in my own experience, but it did the trick.

What it was exactly that was causing the behavior is left to someone with more knowledge than myself, but it's working now. Thanks for your effort in helping me work through the issue.