Auto-enter calc updates when?

[xochi]

In FM7 the new Auto-Enter calc functions in combination with the

Evaluate(expression;trigger fields) seem very powerfull.

However, I'm a bit confused as to under what circumstances exactly an

auto-enter calc will update itself. Does it always update if the

fields referenced in the calc change?

Or, do you need to explicitely use the Evaluate() function along with

trigger fields.

I've heard that if you use Evaluate(), that the trigger fields only work

if they are local to the table? I've read another statement that the

creation order of your calc fields matters?

Also, how do empty values and invalid relationships work? I.e. does

deleting a value from a trigger field count as a change? What about

deleting the trigger field's record entirely? What about changing the

relating key field(s) so there is no longer a related record?

Basically, I'm trying to do the same thing as the "Lookup" function, but

I want more control.

I want to be able to have Field A in Table A (TA::FA) then copies a

value from a related record's Field A in table B (TB::FA)

But I want TA::FA's value to not change or go away, when I delete the

related record in TB, or change the relating key field(s).

Thanks in advance if you can help!

[xochi]

In FM7 the new Auto-Enter calc functions in combination with the

Evaluate(expression;trigger fields) seem very powerfull.

However, I'm a bit confused as to under what circumstances exactly an

auto-enter calc will update itself. Does it always update if the

fields referenced in the calc change?

Or, do you need to explicitely use the Evaluate() function along with

trigger fields.

I've heard that if you use Evaluate(), that the trigger fields only work

if they are local to the table? I've read another statement that the

creation order of your calc fields matters?

Also, how do empty values and invalid relationships work? I.e. does

deleting a value from a trigger field count as a change? What about

deleting the trigger field's record entirely? What about changing the

relating key field(s) so there is no longer a related record?

Basically, I'm trying to do the same thing as the "Lookup" function, but

I want more control.

I want to be able to have Field A in Table A (TA::FA) then copies a

value from a related record's Field A in table B (TB::FA)

But I want TA::FA's value to not change or go away, when I delete the

related record in TB, or change the relating key field(s).

Thanks in advance if you can help!

[xochi]

In FM7 the new Auto-Enter calc functions in combination with the

Evaluate(expression;trigger fields) seem very powerfull.

However, I'm a bit confused as to under what circumstances exactly an

auto-enter calc will update itself. Does it always update if the

fields referenced in the calc change?

Or, do you need to explicitely use the Evaluate() function along with

trigger fields.

I've heard that if you use Evaluate(), that the trigger fields only work

if they are local to the table? I've read another statement that the

creation order of your calc fields matters?

Also, how do empty values and invalid relationships work? I.e. does

deleting a value from a trigger field count as a change? What about

deleting the trigger field's record entirely? What about changing the

relating key field(s) so there is no longer a related record?

Basically, I'm trying to do the same thing as the "Lookup" function, but

I want more control.

I want to be able to have Field A in Table A (TA::FA) then copies a

value from a related record's Field A in table B (TB::FA)

But I want TA::FA's value to not change or go away, when I delete the

related record in TB, or change the relating key field(s).

Thanks in advance if you can help!

[Leader]

Wow, good questions. I'm working without a reference handy, but I'll take you as far as I can.

AutoEnter calc wasn't new with 7, it went back to at least FM3, if not earlier. The Evaluate() function is much newer and frankly I had problems with it in 7.0.0.

You are on the right track: If you use AutoEnter without Evaluate(), you'll get a snapshot at the time the record is created. If related information is later deleted, the field will remain unchanged, assuming the relationship isn't flagged to delete in a reflective relationship.

If you use Evaluate(), be sure the actually trigger is rigidly controlled so it won't update at an inappropriate time. In this regard, think of Evaluate() as the Relookup menu command

[Leader]

Wow, good questions. I'm working without a reference handy, but I'll take you as far as I can.

AutoEnter calc wasn't new with 7, it went back to at least FM3, if not earlier. The Evaluate() function is much newer and frankly I had problems with it in 7.0.0.

You are on the right track: If you use AutoEnter without Evaluate(), you'll get a snapshot at the time the record is created. If related information is later deleted, the field will remain unchanged, assuming the relationship isn't flagged to delete in a reflective relationship.

If you use Evaluate(), be sure the actually trigger is rigidly controlled so it won't update at an inappropriate time. In this regard, think of Evaluate() as the Relookup menu command

[Leader]

Wow, good questions. I'm working without a reference handy, but I'll take you as far as I can.

AutoEnter calc wasn't new with 7, it went back to at least FM3, if not earlier. The Evaluate() function is much newer and frankly I had problems with it in 7.0.0.

You are on the right track: If you use AutoEnter without Evaluate(), you'll get a snapshot at the time the record is created. If related information is later deleted, the field will remain unchanged, assuming the relationship isn't flagged to delete in a reflective relationship.

If you use Evaluate(), be sure the actually trigger is rigidly controlled so it won't update at an inappropriate time. In this regard, think of Evaluate() as the Relookup menu command

[bikergeek]

I've been struggling with this myself. I've tried to get auto-enter fields to reevaluate based on changes in related fields, but have been forced to update them by script instead so that I can have fully indexed contents.

For example: I have a main table of individuals, and a related table of events, related using the ID of the individual. The records in events have the fields ID, Year, Event, Num_Tix. On the individuals layout I have a total tickets field which is indexed to facilitate searching for individuals who have attended a specific number, or more than a threshold number of shows. That field is an auto-enter evaluation based on a calc field that is defined Sum ( events::Num_Tix ). Whenever a new event is added, I want the indexed auto-enter field to update to reflect the new value of the calc field, but I can't seem to get it to happen.

Attached is a sample file. The sample data uses the same events with differing years.

[bikergeek]

I've been struggling with this myself. I've tried to get auto-enter fields to reevaluate based on changes in related fields, but have been forced to update them by script instead so that I can have fully indexed contents.

For example: I have a main table of individuals, and a related table of events, related using the ID of the individual. The records in events have the fields ID, Year, Event, Num_Tix. On the individuals layout I have a total tickets field which is indexed to facilitate searching for individuals who have attended a specific number, or more than a threshold number of shows. That field is an auto-enter evaluation based on a calc field that is defined Sum ( events::Num_Tix ). Whenever a new event is added, I want the indexed auto-enter field to update to reflect the new value of the calc field, but I can't seem to get it to happen.

Attached is a sample file. The sample data uses the same events with differing years.

[bikergeek]

I've been struggling with this myself. I've tried to get auto-enter fields to reevaluate based on changes in related fields, but have been forced to update them by script instead so that I can have fully indexed contents.

For example: I have a main table of individuals, and a related table of events, related using the ID of the individual. The records in events have the fields ID, Year, Event, Num_Tix. On the individuals layout I have a total tickets field which is indexed to facilitate searching for individuals who have attended a specific number, or more than a threshold number of shows. That field is an auto-enter evaluation based on a calc field that is defined Sum ( events::Num_Tix ). Whenever a new event is added, I want the indexed auto-enter field to update to reflect the new value of the calc field, but I can't seem to get it to happen.

Attached is a sample file. The sample data uses the same events with differing years.

tickets.fp7.zip

[bikergeek]

Any further thoughts here? The script take a long while to go through and do the update of the indexed fields, and I'd love it if there were a better way.

[bikergeek]

Any further thoughts here? The script take a long while to go through and do the update of the indexed fields, and I'd love it if there were a better way.

[bikergeek]

Any further thoughts here? The script take a long while to go through and do the update of the indexed fields, and I'd love it if there were a better way.

[Fenton]

I would guess that the slowness (and it is slow as heck) is because of:

Min ( tickets summary::record ID ) = record ID

If you're wanting to mark the 1st (and only the 1st) record of the year for someone, there are far faster methods. You don't really need Min(), which is the real slowdown. Because a self-relationship always gets the 1st entry. So the above is much the same as:

Case ( tickets summary::record ID ) = record ID, 1, "")

Then all you have to do is either Replace on the Record ID, with itself. Or Replace the above calculation directly in the marking field. The latter may be faster, as it won't trigger other lookups you may not want.

If you're going to be doing 2 Replaces, you might find 1 Loop to be faster. Especially on networked files.

If you are also going to be entering records manually, there is another method, somewhat the same, but using a lookup. If you need it post again. It's an old technique however. If someone knows a new 7'ish one please chime in; I'd like to see it.

You have a lot of fields with auto-enter calculations, all depending on each other, some of them Summary fields. Perhaps this was experimental. In any case, it's going to be really slow, so I'd try and only do that when necessary. It is not really necessary if you're running scripts after every import, and imports are the only way data is entered.

[Fenton]

I would guess that the slowness (and it is slow as heck) is because of:

Min ( tickets summary::record ID ) = record ID

If you're wanting to mark the 1st (and only the 1st) record of the year for someone, there are far faster methods. You don't really need Min(), which is the real slowdown. Because a self-relationship always gets the 1st entry. So the above is much the same as:

Case ( tickets summary::record ID ) = record ID, 1, "")

Then all you have to do is either Replace on the Record ID, with itself. Or Replace the above calculation directly in the marking field. The latter may be faster, as it won't trigger other lookups you may not want.

If you're going to be doing 2 Replaces, you might find 1 Loop to be faster. Especially on networked files.

If you are also going to be entering records manually, there is another method, somewhat the same, but using a lookup. If you need it post again. It's an old technique however. If someone knows a new 7'ish one please chime in; I'd like to see it.

You have a lot of fields with auto-enter calculations, all depending on each other, some of them Summary fields. Perhaps this was experimental. In any case, it's going to be really slow, so I'd try and only do that when necessary. It is not really necessary if you're running scripts after every import, and imports are the only way data is entered.

[Fenton]

I would guess that the slowness (and it is slow as heck) is because of:

Min ( tickets summary::record ID ) = record ID

If you're wanting to mark the 1st (and only the 1st) record of the year for someone, there are far faster methods. You don't really need Min(), which is the real slowdown. Because a self-relationship always gets the 1st entry. So the above is much the same as:

Case ( tickets summary::record ID ) = record ID, 1, "")

Then all you have to do is either Replace on the Record ID, with itself. Or Replace the above calculation directly in the marking field. The latter may be faster, as it won't trigger other lookups you may not want.

If you're going to be doing 2 Replaces, you might find 1 Loop to be faster. Especially on networked files.

If you are also going to be entering records manually, there is another method, somewhat the same, but using a lookup. If you need it post again. It's an old technique however. If someone knows a new 7'ish one please chime in; I'd like to see it.

You have a lot of fields with auto-enter calculations, all depending on each other, some of them Summary fields. Perhaps this was experimental. In any case, it's going to be really slow, so I'd try and only do that when necessary. It is not really necessary if you're running scripts after every import, and imports are the only way data is entered.

[bikergeek]

I changed the min(ticket summary::recordID) = recordID to

ticket summary::recordID = recordID, just because the extra complexity isn't needed, but that's not what the challenge here is.

The challenge is in having the "number of events," "tickets total" and "tickets per year" fields indexed so that they can be quickly searched, and in having the "one per year" field be indexed so that it can serve as a match for the summary portal. Auto-enter calculations based on relationships don't seem to happen properly on import, only on changes made to single records manually, but I need to preserve both options. Most records will be imported, some will be manually modified, added or deleted, and I don't want either the summary values or the portals to incorrectly reflect the actual data.

Each field is based on a relationship which makes them not directly indexable as calculated values, so the solution I came up with was to run a script that replaces values in a couple of the fields, and let evaluate statements result in the other static fields being updated.

I'm looking for a way to get indexed, static values based on aggregate data, that update on either import or manual modification. I appreciate your response, Fenton, but I can't see anything there that addresses these needs. Looping through records instead of using Replace doesn't work any faster, even with the auto-enters turned off.

[bikergeek]

I changed the min(ticket summary::recordID) = recordID to

ticket summary::recordID = recordID, just because the extra complexity isn't needed, but that's not what the challenge here is.

The challenge is in having the "number of events," "tickets total" and "tickets per year" fields indexed so that they can be quickly searched, and in having the "one per year" field be indexed so that it can serve as a match for the summary portal. Auto-enter calculations based on relationships don't seem to happen properly on import, only on changes made to single records manually, but I need to preserve both options. Most records will be imported, some will be manually modified, added or deleted, and I don't want either the summary values or the portals to incorrectly reflect the actual data.

Each field is based on a relationship which makes them not directly indexable as calculated values, so the solution I came up with was to run a script that replaces values in a couple of the fields, and let evaluate statements result in the other static fields being updated.

I'm looking for a way to get indexed, static values based on aggregate data, that update on either import or manual modification. I appreciate your response, Fenton, but I can't see anything there that addresses these needs. Looping through records instead of using Replace doesn't work any faster, even with the auto-enters turned off.

[bikergeek]

I changed the min(ticket summary::recordID) = recordID to

ticket summary::recordID = recordID, just because the extra complexity isn't needed, but that's not what the challenge here is.

The challenge is in having the "number of events," "tickets total" and "tickets per year" fields indexed so that they can be quickly searched, and in having the "one per year" field be indexed so that it can serve as a match for the summary portal. Auto-enter calculations based on relationships don't seem to happen properly on import, only on changes made to single records manually, but I need to preserve both options. Most records will be imported, some will be manually modified, added or deleted, and I don't want either the summary values or the portals to incorrectly reflect the actual data.

Each field is based on a relationship which makes them not directly indexable as calculated values, so the solution I came up with was to run a script that replaces values in a couple of the fields, and let evaluate statements result in the other static fields being updated.

I'm looking for a way to get indexed, static values based on aggregate data, that update on either import or manual modification. I appreciate your response, Fenton, but I can't see anything there that addresses these needs. Looping through records instead of using Replace doesn't work any faster, even with the auto-enters turned off.