A Lookup is a field that pulls a value from a related field. Unlike a regular calculation field, a Lookup will grab the related field value, and store it, never changing again, unless the key field relating the two fields changes.

Benefits of lookups:

1. When the data may change a later date, but you need to remember the data at the time an event occurred (e.g. you want the price of an item at the time the invoice was created)

2. When the data must be indexed for performance reasons, since you cant index unstored calculation fields (and all calculations that refer to a related value must be unstored).

3. Lookups can be forced to "recalculate" with the "Relookup" command, giving you some script-based control.

4. Automatic -- you don't need to remember to script a lookup.

Disadvantages

However, lookups have some major disadvantages.

1. No calculations. A lookup pulls the related value, period. You can't create a lookup which does a Sum() or other calculation.

2. Relookup command is clunky -- the command actually is specified by the Key field, not by the lookup field. So, if you have 3 lookup fields that all use the same key field, you have to relookup all of them.

Alternative Method

Wouldn't it be nice if we could have a field that functioned like a Lookup, but with more control and features? Filemaker 7 added some new features to calculation fields that make this possible. Here is a technique I'm testing, with generally good results.

1. Make a regular, indexed field. This means it can be fully indexed for fast searches, sorting, and summary statistics.

2. Set the field an AutoEnter Calculation that pulls the value from your related table, with any calculations you wish.

3. Check "calculation replaces value if any".

At this point, we are nearly there. However, we have a big problem -- if you change the data in the related table, your auto-enter calc field will not update, even if you have the checkbox set as in #3. The reason is that FileMaker doesn't cross relationship boundaries when deciding if a field needs updating. If you are using this field for the purposes of indexing, having your data be out of sync is an issue. The solution:

4. Create another field in your table, which I like to call a "Trigger" field.

5. In your autoenter calc field, reference the Trigger field using either the Evaluate or Let functions.

e.g.

// 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.

  X = 

    Let( 

      // the next line sets up the trigger.  

      dummyVariable = TriggerField ;



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

      // whatever you need. 

      Sum(relatedTable::invoiceTotal) * ThisTable::TaxRate + Surcharge

)

After adding #4 and #5, your field suddenly becomes much more powerful. Now you can manually cause the field to "refresh" it's calculation just by setting the Trigger field. As opposed to the Lookup field, you have more flexibility here. You can have several fields which all use the same Trigger field, or you could create multiple trigger fields and have groups of calc fields that use them.

I'm using this technique in a large solution, and I'm finding it rather helpful. It has allowed me to speed up some operations in the database by a factor of 10 or perhaps 100 times.

Comments?

Hi Xochi,

you cant post a sample with a couple of fields can you? I was trying to follow your instructions, but got stuck at point 5.

I think this could be very useful indeed.

Many thanks

Jalz

Is there a reason the final closed parenthesis is on a separate line? Can you give some examples of uses e.g. currentdate,perhaps city to zipcode? Also, do you need to enter data in the trigger field, 1 = go, 0 = leave as is?

Thanks fo your help.

Al

I'm sorry I'm too busy to do a sample right now, but will try to answer questions:

1. As for the location for the location of the closing parenthesis, I've been trying to use a more C-like syntax for my functions. I think that the formatting was a little messed up, so it should look more like this had I done it right:

Let(

  // the first part

  blah blah blah ;

  // the second part

  blah blah blah 

)  // <-- note that I try to align this paren with the L in Let

2A. To cause the trigger field to "fire" (i.e. to update the calculation) you just need to set the value to anything (including the value it already has).   So if your trigger field holds "1", you can cause it to update by setting it to "1" again with a SetField or ReplaceFieldContents step.     



2B. Trigger fields -- another idea I had was to make the trigger field a TimeStamp field, and set it to the current timestamp when you update it.  This would make it easy to debug by answering the question "when did I last update this record?"  I've not tested this, however.



3. Confusion at step 5 -- remember that the general format of a Let statement is 

Let(

  // variable assignment comes first

  x = y ;



  // the result of the Let statement comes next

  N * x

)

Things get trickier if you have multiple variable assignments, in which case it looks like this:

Let(

  // multiple variable assignments come first

  // and are surrounded by brackets []

  [

    a = b ;

    c = d ;

    x = y

  ]  ;   // note how this semicolon goes OUTSIDE the bracket



  // the result of the Let statement comes next

  N * a * b * c * d * x

)

In my original description (up thread),  we are using a "dummy" assignment as the first part of the Let statement.  We don't care about the value assigned -- the only purpose is to make sure that whenever we change the Trigger field, the calculation will update.



The second part of the Let statement should be whatever calculation you wish.  If you are trying to emulate a Lookup, it might simply be a single related field, e.g.

// 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.  

      dummyVariable = TriggerField ;



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

      // whatever you need. 

      RelatedTable::FieldA

  )

Edited December 8, 200520 yr by Guest
fixed typo

You can find examples of this technique in these (and probably some other) threads:

http://fmforums.com/forum/showtopic.php?tid/168252

http://fmforums.com/forum/showtopic.php?tid/171301

http://fmforums.com/forum/showtopic.php?tid/166308/

I have a question about this. Does FM always evaluate a let statement on comitting a record? Is that the secret to triggering cross table references in an auto enter? Or am I missing a component to this that always modifies the trigger field even if the target record remains untouched?

For my purpose, I have a portal looking into the Invoices table from the Customers table. In the Sales table I have a number field that auto calcs as SUM(Invoices::GrandTotal). I had a lot of trouble getting it to update when I had not actually modified the Customers table but only a dollar amount in the Invoices table. I know there are other ways to do this but I required a hard data field in my Customers table for legacy import. (love legacy data!)

Not sure I understand your question, but I believe that this technique only updates under the following conditions:

1. New record creation.

2. Changed either the trigger field or another field that is used in the calculation that is in the same table.

3. Import data with the "auto enter" checkbox on.

I like it because it's fairly easy to know when your calculation will, or will not update.

Maybe I don't understand the subtlety of this technique, but wouldn't using a Refresh Window (flush cached join results) script step accomplish the same thing?

Deleted because I stand corrected.

Edited December 14, 200520 yr by Guest

Okay,

I read the thread again and I asked a redundant question. My Bad. It will not update unless a field in the local table has changed.

Again, this is the reason I feel compelled to use a script triggering plugin. I almost have no choice still.

In the Sales table I have a number field that auto calcs as SUM(Invoices::GrandTotal). I had a lot of trouble getting it to update when I had not actually modified the Customers table but only a dollar amount in the Invoices table.

You have to explain why the Sum( field can't be unstored calcfield??? I might be abel to see some purpose in indexing each invoice's sum - if you should search for invoices at exactly $45.10 or such ....but a figure for the total sales for all invoices doesn't have to be stored or indexed at all, since it's only for eyeballing.

So again why is it you need an autoenter calc for a Sum( ...other fieldtypes exists :

--sd

I don't know where you get this stuff. Refreshing a window (with flushing the cache or without) will NOT cause the auto-enter field to re-evaluate. It evaluates once at record creation (and that would include import with auto-enter option on). After that, it will re-evaluate only when a referenced field IN THE SAME RECORD is modified. Even if the calculation references a global field, and that global field is modified, only the CURRENT record will be re-evaluated.

IN THE SAME RECORD is modified. Even if the calculation references a global field, and that global field is modified, only the CURRENT record will be re-evaluated

Exactly!! - It must be SQL migrators we're dealing with here, since unstored calcfields are out of the question??? But it certainly explain the urge to event scripting!!!

--sd

Okay,

I have a question then. If I have a calc field in my MainTable called SUMRELATEDTOTALS

That calculates:

Sum(RelatedTable::SumOfAllValues)

and I need this field to be stored for value list generation, (I think), I could have an intermediate Auto-Enter field:

MainTable::StoreSumRelatedTotals

That just gets MainTable::SumRelatedTotals, and it (will? will not?) update immediately because it references a field that updates automatically?

Yes, I can work this out for myself, but I think the conversation is of benefit. I remember hacking around the problem already and I do not remember how. I would have to open the old file and check. I know that I did at one point just put a button next to the field "Update Total" just to get over the hump. That was an ugly hack in my opinion.

There are many reasons (in my opinion) one may want a calculation based upon a related field and have the results be indexed. How do we get that result to update automatically WITHOUT ever touching the local record but only the foreign record?

Filemaker does not monitor related records for changes. That's just the way it is, and there's no magic word that will make Filemaker do something that it does not.

In most cases, the need for indexable calculation based on related records is an indication that the solution is not following the "Filemaker paradigm" (for lack of better expression). So far, I don't recall anyone presenting a real problem that could not be solved within this "limitation". I put "limitation" in quotes, because I don't truly see it as a limitation - to me it is merely a natural result of the application's underlying philosophy.

Okay,

Thanks Comment for the input. I did just finish figuring it out for myself and I'll quit whining about it now.

I was just dealing with this now... Invoices have a "status" open/closed and the items are created prior to the change of the status but in another portal I need to look at all my items by status via a muti-predicute relationship thus requires a status field in item table to be indexed.

The only two ways I see ensuring the child table to have the correct info is to at some interval run a script that does a replace on all records getting the current status from the parent. Or change the ui so that you have to run a script when changing status thus looping thru the related records and setting the appropriate status.

This solution will work in multi user environments because my trigger field is a global or variable thus I am not actually doing a replace on a real field and have the potential of coming across a locked record.

I just need to go to each record and re-declare my trigger and it should update for the CURRENT record.

A relationship can be filtered by unstored field.

I'm surprised nobody mentioned the Lookup() function, which was introduced in FileMaker 7. Unlike a calc that simply references a related field, calcs that use this function can be indexed.

I think you still have problems with updating when it references related tables though.

As to why the need for stored calcs, it's either because a field needs to be used in a relationship, or because displaying and summarizing lots of unstored calcs with large data sets is just too ******* slow.

Yes I have used the Lookup() function but again it only evaluates for the current record I guess I am was looking for a way to FORCE a lookup to all children records not by script but by calculation, this is at the moment not possible. The only solution is via a script.

Stephen,

I'm glad I'm not the only one "stuck" with this issue. It seems so obvious a requirement to me. Now at least I have a better understanding WHY it does not work and I can plan around it in the future.

Thanks again to everyone for the ongoing education.

The only solution is via a script.

Is it?

FilterByUnstored2.fp7.zip

BRAVO!

--sd

Oh now.

I respect both of you guys for your knowledge and experience, but I think that's just a silly sematic game you're playing now. The effective difference between a button and a script is pointless here since the user still has to manually force the update. I'm no database wizard but are you arguing now just for the sake of principal or do you have a genuine solution to the user experiemce issue?

One can go on and on about the logic of FileMaker, and what may or may not be possible, but we're developing for a user who expects a certain behavior when they enter or change information. Bottom line, if they expect a certain result and for some reason the result does not instantly occur, or the result is, worse yet, undesired, they'll be quite annoyed.

I suggest you take a good hard look at the principle being displayed in that demo. If you study (and truly understand) it, you'll appreciate the power of this. :wink2:

If I'm not mistaken, the starting point of this particular discussion, was to automatially refresh the results of auto-enter calcs that reference foreign fields in the event that only the foreign field has been modified. We have not yet acheived that. (I think)

On the above example file, Actually I do (I think) understand and appreciate the technique. The child has an unstored calc field that becomes the basis of a self relationship. However, the resulting "conditionalchild" set of the relationship does not refresh without actually "clicking" a button. In fact, the relationship does not refresh at all, even when switching records in the parent table. Because the child calc is unstored, it updates instantly when the parent record is changed, however, when the global is changed, nothing happens.

If the point is to NOT update a particular view unless desired, then maybe I can see the point. But relationally, in the example, we are still only looking "through" the parent record in a somewhat convoluted way.

So anyway, because I might have been a bit obtuse here, please help me understand why one would not just do it like this:

FilterByUnstored3.zip

You have to explain why the Sum( field can't be unstored calcfield??? I might be abel to see some purpose in indexing each invoice's sum - if you should search for invoices at exactly $45.10 or such ....but a figure for the total sales for all invoices doesn't have to be stored or indexed at all, since it's only for eyeballing.

So again why is it you need an autoenter calc for a Sum( : ...other fieldtypes exists :

--sd

Soren,

In response to your earlier question, I needed to import and retain legacy data that was manually entered at the time. That legacy data will not have any child records from which to calculate. Further, I wanted to be able to update that data during the transition by manual entry, so I needed a number field that auto entered a new value IF, and only if, it had valid child records.

I can think now of a half dozen ways better to do it, but, for me now it is more of a theoretical discussion than whether or not that particular problem was solvable by other means.

Hi Nathan,

I won't get into the points about the various techniques here ... you all are doing a find job in that aspect. But ...

If I'm not mistaken, the starting point of this particular discussion, was to automatially refresh the results of auto-enter calcs that reference foreign fields in the event that only the foreign field has been modified. We have not yet acheived that. (I think)

Comment already told you it's against FM's nature. A different table will NEVER change because a related field changes. This is rather moot, I think.

When a wise Developer hits one of these points, they simply go around the wall. You have been handed a lateral solution which bridges a gap in graph manipulation and options. Say "Thank You." No. It is not an answer to your original question ... you were already given that answer ... but don't let walls stop you dead in your tracks. As already pointed out, if you need something that FM won't do, you need to reconsider your approach.

LaRetta :wink2:

Thank You

:-)

I think that's just a silly sematic game you're playing now. The effective difference between a button and a script is pointless here since the user still has to manually force the update.

Oh, no. The difference is much, much deeper than that. Let's put user experience aside for a moment and look at it from the perspective of data structure.

In a relational database, there is no redundant data. Data is only entered once, and kept in one place only . If you want to know the address of the customer who bought a particular LineItem, you look at the InvoiceID of the item. Then you relate to that invoice. From there, by CustomerID, you relate to the customer record, where the address is stored.

As a result, when an address is modified in a customer's record, it is also implicitly modified for all line items purchased by the customer. The line items "don't know" of the change yet - but any time they "need to know", they will be referred to the only place where this data lives - and the data there is always up-to-date.

There you have "unstored" in a nutshell. Any other model would require keeping the same information in more than one place. And any modification would have to be propagated to all of them. So data entry would become a two-staged process:

1. Enter the data;

2. Find all the other places where the same data needs to be STORED, and change them.

Such process is vulnerable. A script attempting to do this might come up against records that are locked by other users. So it would need to remember these records, in order to return to them later. In the meantime, there's conflicting information in the system. If the system crashed now, before the update is completed, there would be no way to know which records were updated, and which were not.

In the current example, it's not such a big deal, since we can re-duplicate everything from the customers' records. But consider a system that uses a script to update the quantity-on-hand after each sale. There's no audit trail here. In the event of failure, the only resort is to go out and count the actual physical items. And failure is difficult to prevent: you can make every effort to assure that the script is run when needed, and (no less important) not run when not needed. But this depends on your ability to predict every possible situation. In a complex solution, that's very difficult. As the saying goes, "when you think you have made it foolproof, someone will build a better fool."

That's why scripts are fine for assisting in data entry and data display, but when your core data depends on scripts, you're taking a big risk by departing from the fundamental paradigm of relational database.

Now, let's talk about my demo file. You say "when the global is changed, nothing happens". You are very much mistaken about that. In fact, when the global is changed, everything has already happened. Data has been changed. It will not be changed anymore. The only problem is that the new data is not yet being DISPLAYED, because Filemaker has not detected a cause to refresh the screen.

Your file is better than mine in this aspect. You have managed to put the global directly into the relationship chain. So any change to the global makes Filemaker refresh the portal. But that is a purely cosmetic improvement. In both files, the DATA is the same at all times. Refreshing the window does not update the database in any way - it merely replaces the buffer of your computer's graphics card. So both your file and mine are on one side of the divide against a scripted replacement of stored data.

Finally, let's return to user experience. This is a demo file. I wanted to show the relational aspect, and didn't want to hide the refresh issue. In a practical implementation, this would be hidden from the user, for example by making the global field a button that toggles it - and refreshes the window at the same time. You have shown a way to do without in this case - but I have used the same technique in many other situations, and it's not always possible to eliminate the need to refresh the window at the end (or at least I haven't found a way to do it yet). However, as I have (hopefully) shown, it's a very minor issue.

Thanks, but the credit should go mainly to Ugo. I don't know if he intended this when he showed how to de-dupe a portal, but I have adapted his idea to many other situations - I think by now I have posted around 20 demos, all using the same basic principle.

Admitted I do not tear everything you make appart ...which I obviously ought to ...but for the moment do I usually trace IPW and "do shell" stuff ...I do deeply apologize for not being all over the place : ...But it makes Lee sleep well ...since my apparent but not deliberate condescending style, makes him worry.

--sd

Comment,

I do truly and honestly say thank you! When one is new to this subject, one tends to make some fundamental assumptions about how things must/should/could go together. If I challenge a statement, it is often because I have not received a good explanation why and am still trying to figure it out for myself. I do really appreciate your input and I believe that I really learned something here.

P.S. The response you gave on Recursion was exactly the kick in the pants I needed to get over the bump. Thank you for that too.

I've been following this discussion and I've tried to make something work using the information provided, but it keeps escaping me.

I have a main table of PERSONS, and an associated table of EVENTS, related by PERSON_ID. For each time a person has purchased tickets to an event, a record is added to the EVENTS table with the PERSON_ID, YEAR, EVENT_ID, and the NUM_TICKETS purchased.

On the main layout, I show a record from PERSONS and a portal with related records from EVENTS, showing which events that person bought tickets to. Below the portal, I have fields which have the total number of events that person attended, and the total number of tickets that person bought. I need those fields to be indexed so that PERSONS can be found using events attended or tickets purchased as criteria.

I cannot seem to get indexed numbers to update in those fields when records are added to the events table, or when NUM_TICKETS on EVENTS is modified.

My only functional solution is to run a script whenever the EVENTS table is changed which updates totals on related PERSONS records. Using "Evaluate" and/or "Let" functions, even referencing a summary field in the PERSONS table, is not working. What am I missing?

FYI, Filemaker 8.0v2 claims to fix a bug with regards to auto-enter calcs. In short, sometimes the result would be dependent on field creation order. They claim to have fixed this. More details here: http://fmforums.com/forum/showtopic.php?tid/170519/post/187687/#187687/

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.

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

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).

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.

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

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, 200520 yr by Guest

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.

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.

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.

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:

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, 200520 yr by Guest
Added last afterthought

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.

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.

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.