Jump to content

Triggered Stored Auto-Enter Calcs Technique


This topic is 6493 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

Link to comment
Share on other sites

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 by Guest
Added last afterthought
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 5 months later...

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.

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.