Triggered Stored Auto-Enter Calcs Technique

[xochi]

This thread has been lively! Thanks to all for contributing. I think perhaps several different important issues are being mixed here, which may account for the confusion.

I find two main reasons for needing stored calculation fields:

(1) Performance issues. In this case, you are intentionally de-normalizing your data so as to allow indexing and/or to allow faster display of summary data. Although this is "improper" from a relational database design theory point of view, it is also "practical" and a widely used technique, even in other SQL based databases.

(2) Archival data. There are many cases where you need a snapshot of a set of data that will not change. The most common example used to illustrate this point is a sales invoice : You can't have a formula that says InvoiceTotal = Number of Widgets * WidgetTable::WidgetPrice, because if the widget price changes at a later date, your invoice suddenly is incorrect. This is what filemaker provides Lookups for.

However, I find that filemaker's Lookup features are limited (as described in my first post) so I went searching for a more flexible technique.

To be clear -- this technique is NOT about having a stored calculation auto-update when a related (child) record changes. In fact, it's about having absolute control over when the calculation updates.

[comment]

You are describing a classic many-to-many situation, I think. You need three tables, PEOPLE, EVENTS and TICKETS.

[bikergeek]

Why would I need a third table? TICKETS would still reference the EVENT_ID and the PERSON_ID, and I'm not tracking the events themselves, only the persons, number of events they've attended and number of tickets they've purchased to each event (and total).

[bikergeek]

Field order is irrelevant because my auto enters only reference the summary field on the same table:

Unstored Calculation field: PERSONS::TOTAL_TIX = Sum (EVENTS::NUM_TICKETS)

Auto-Enter field: PERSONS::TOTAL_TIX_INDEXED = Let ( z = PERSONS::TOTAL_TIX ; PERSONS::TOTAL_TIX )

When the EVENTS table is changed, PERSONS::TOTAL_TIX updates, but PERSONS::TOTAL_TIX_INDEXED does not.

[Ocean West]

Thanks comment for your detailed explanation.

In my case I need to also include a multi-predicute relationship.

based upon using a value list i am able to filter by user entry and by a type field. And have the portal update with out using a script.

Look at the file the one in RED is I think is working well. I am not certain the limitations and how a large database would respond.

Filter.fp7.zip

[Ocean West]

In my example I suppose the g_company_ID field could be changed to keep to the intent with the original post... :

I think the net result is the same.

Change This

ValueListItems ( Get(FileName) ; "company_id" ) &

Left(gType;0) & Left (gFilter; 0) // this forces the relookup when either field is modified.

To This

// this is a triggered auto-enter calc field.

// it will update if any of the fields (local to this table) referenced below are changed

// it will also update if the Trigger field is changed.

// It will NOT update if a related field in another table is changed.

Let( 

// the next line sets up the trigger.

[

a = gType ;

b = gFilter];



// the next part is the calculation, replace this with

// whatever you need. 

ValueListItems ( Get(FileName) ; "company_id" ) 

  )

Edited December 21, 2005 by Guest

[comment]

I don't think the type of relationship should make a difference in this context.

The person to ask about how large files respond to this is LaRetta - I believe she has implemented a a very similar method in a huge file.

[comment]

Why would I need a third table?

For one thing, it would enable you to find all People who bought tickets to a particular Event instantly, using GTRR.

[bikergeek]

That's the one thing I do NOT need to do. Why would I want to populate yet another table with ID numbers when the only value to the data that would exist in that table is in associating it with a PERSON, not with an EVENT?

You're talking about a theoretical application, and I'm trying to solve a real problem.

[LaRetta]

There were many suggestions on that thread and I tested each one. But my tests were a bit inconsistent and sporatic so I didn't trust the results. However, I implemented lastNrows if that's the demo in which you make reference.

It constricts the portal relationship in Contacts displaying LineItems. It also includes a few other globals. Since the relationship is never over 25 records, display is instantaneous. I haven't tested it with large numbers. But if proportion holds (number of related to speed) then I am still convinced it is much quicker than all the other things I've tried.

And yes, it feels good to let go of some scripting here. LineItems is a beast. :wink2:

[LaRetta]

You're talking about a theoretical application, and I'm trying to solve a real problem.

'scuse me?

Well, it's known as contingency theory = planning ahead and allowing for solution growth. And just because it ALSO solves a problem you don't have does that mean you don't want to use it solve your existing problem? You will regret not splitting to finite on this piece.

Edited December 21, 2005 by Guest
Added last afterthought

[comment]

That's the one thing I do NOT need to do.

Perhaps I misunderstood you when you wrote:

I need those fields to be indexed so that PERSONS can be found using events attended

In any case, I am under the impression that you're yelling at me for some reason, so I'll bow out.

[skrying]

Field order is irrelevant because my auto enters only reference the summary field on the same table:

Unstored Calculation field: PERSONS::TOTAL_TIX = Sum (EVENTS::NUM_TICKETS)

Auto-Enter field: PERSONS::TOTAL_TIX_INDEXED = Let ( z = PERSONS::TOTAL_TIX ; PERSONS::TOTAL_TIX )

When the EVENTS table is changed, PERSONS::TOTAL_TIX updates, but PERSONS::TOTAL_TIX_INDEXED does not.

In this case, since PERSONS::TOTAL_TIX is unstored, the "trigger" functionality doesn't work. Rather unfortunate, as I was hoping to do something very similar to your attempt here.

[HumblePie]

I have done this using either the auto enter calc or validate by calculation, triggering a script (using Dacons scriptfire, Waves events, Troi activator etc...) which sets the field. Any of the fields referenced by the field in question would need to have this in their definition.

You could even have an uninexed calc with the same kind of thing, setting a counterpart indexed field whevever its value changes? Come to think of it, that would be better.