Suppressing Self-Referencing Auto Enter Fields

By Daniel Wood, 17 April 2011

Using an auto-enter calculation on a field is a great way to populate its contents when other fields are modified, including itself. But what do you do if you want to use a calculation that references itself without having it trigger its own auto-enter calculation? The answer lies in thinking outside the calculation....

Setup aof an Example

In the attached example file I have concocted a scenario which illustrates this dilemma, although there are many situations where this may become an issue.

selfautoenter 1

Here is a basic table of products. Each product has two rates. In another table I have a product ID field with a value list attached displaying products from the products table. The idea is that when a product is selected, the rate field is populated automatically with the correct rate from the products table. The rules are:

  1. If product has a Rate 1 use that, otherwise
  2. If product has a Rate 2 use that, otherwise
  3. We wish to leave whatever is in the rate field at the time unchanged.

Here are some examples of what we expect when a product is chosen:

selfautoenter 2

selfautoenter 3

There is one more key rule for our Rate field and that is the rate must be able to be modified once a default is auto-entered

Building an auto-enter calculation for the rate field

With all of the required rules known, the first attempt at formulating an auto-enter calculation looked as follows:

Let ( [
TriggerField = Product ID ;
RateOne = Home to Products for Rate Auto Entry::Rate 1 ;
RateTwo = Home to Products for Rate Auto Entry::Rate 2
];
Case (
not IsEmpty ( RateOne ) ; RateOne ;
not IsEmpty ( RateTwo ) ; RateTwo ;
Self
)
)

Note that auto-enter calculations will trigger and re-evaluate when any field that they reference within the same table context is modified. We wish for the rate to change whenever the product ID is changed, however the product ID is merely a means to lookup the correct rate, we don't actually make use of the product ID itself in the calculation results. In order to have it trigger the auto-enter calculation we simply need to reference. This has been done using a Let statement where we set the Product ID field into a trigger variable whose only purpose is to fire the auto-enter calculation into action.

The rest of the calculation is straightforward and follows the rules defined. If there is a Rate 1 use it, otherwise if there is a Rate 2 use that, otherwise leave the field contents unchanged, as evidenced by using Self.

When this auto-enter calculation is used, everything appears fine.. Modification of Product ID fires the auto-enter, and the correct rate from the products table is entered.

But what about the final rule allowing modification of the rate?

This is where this auto-enter calculation fails. Recall that an auto-enter calculation triggers when any of its referenced fields are modified in the same table context. Well this even refers to its own self. If an auto-enter calculation references its own field, then any modification in the field itself will cause itself to auto-enter. Sometimes this can be a very useful behavior depending on your situation, but sometimes this can be a real pain in the arse!

If we were to attempt to modify the rate field now, then because our auto-enter calculation references Self then the auto-enter is re-evaluated. Given the current calculation, it is going to relookup the associated rate from the Products table, thus nullifying any modification we tried to make to the field - the auto-enter is essentially hijacked by our lookup from the products table, preventing any modification to the field by the user. The only time the user is free to modify the rate is if there is no associated default rate in the Products table. Again, this may be what you want, but sometimes it isn't.

So, how do we fix this?

Finally the answer, bit of an anti-climax perhaps after all of this build up :) If an auto-enter calculation triggers when any field it references in the given table is modified, including Self, then is there anyway to reference Self from somewhere other than the table context? The answer is of course yes, and can be achieved using a Self-Join relationship.

selfautoenter 4

Here is a simple self-join relationship matching on primary key. Essentially a self-join is a 1-to-1 relationship with itself.

Now, we make a slight tweak to the auto-enter calculation:

Let ( [
TriggerField = Product ID ;
RateOne = Home to Products for Rate Auto Entry::Rate 1 ;
RateTwo = Home to Products for Rate Auto Entry::Rate 2
];
Case (
not IsEmpty ( RateOne ) ; RateOne ;
not IsEmpty ( RateTwo ) ; RateTwo ;
Home to Home for Self Join::Rate Modifiable
)
)

The change is made on the last line. Instead of directly referencing the field using Self, we instead now reference it via the self-join relationship. By referencing self outside of the calculations context, we are able to use the value of Self, without having it trigger its own auto-enter calculation which is exactly what we want. Now, the only field in the auto-enter calculation which will trigger the calculation is Product ID.

Back on the layout, if a product ID is chosen the rate will be auto-entered if there is one. Once done, the user is free to then modify the rate to whatever they wish, and because the auto-enter calculation no longer directly references Self from the calculation context, it can do so without having the value revert to the default rate.

selfautoenter 5

Script Triggers

Yes, this could have been achieved with a script trigger on the Product ID field, but sometimes a script trigger may not be applicable, able to be used, or easy enough to work with. Because the auto-enter is tied directly to the field itself, then wherever the field is used the auto-enter simply works. With the script trigger it must be attached to every place the field is used in the solution.

I'm not saying a script trigger is not a good way of achieving this type of result, it certainly is and could be used. However I think auto-enters as a means to set a default value into a field, or enforce data entry rules on a field is still a great technique and very useful - especially in solutions Pre-FileMaker 10.

Final Thoughts:

Auto-Enter calculations are a great method for enforcing data-entry restrictions or rules on a field and for pre-populating a field with a default value. However because of their triggering properties there can arise situations in which you wish to reference the fields on contents but not have the field re-trigger itself when modified. A self-join can be used to reference the field in its own calculation - or indeed any field on the table - without having those fields force the re-triggering of the auto-enter calculation.

Example File

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.

Download Example File

Something to say? Post a comment...

Comments

  • Josh Ormond 20/04/2011 7:14am (9 years ago)

    I avoid hard-coding field names by always using a Let () statement anywhere I need to use a literal text string in a function. It allows FileMaker to make the FieldName update, but I can still utilize the power of Evaluate() and custom functions that take field names as parameters (like one of my new favorites CustomList() by Agnes Barouh)

    Something simple like:

    <blockquote>Let (
    ~Field = GetFieldName ( Table::Field )
    ;
    Evaluate ( ~Field )

    )</blockquote>

  • Fabrice Nordmann 19/04/2011 8:14pm (9 years ago)

    Hi,

    this is an awsome technique. Thanks !
    If you prefer custom functions to relationships, you can also use this one: http://fmfunctions.com/fname/AllowInputInAutoEnter

  • Kevin Frank 19/04/2011 7:11pm (9 years ago)

    Incidentally, Fabrice Nordmann has written a wonderful custom function called FM_Name_ID which can help avoid hard-coded name references... here's his blog entry: http://www.1-more-thing.com/FileMaker-Avoid-Hard-coding.html

    I like the CF so much that I posted a blog entry about it today here: http://www.filemakerhacks.com/?p=1447

  • Kevin Frank 19/04/2011 10:10am (9 years ago)

    I agree completely about hard-coding being bad, but I didn't want to obscure the example. I was going to post a correction, but I see Tom's beaten me to it.

  • Daniel Wood 19/04/2011 8:35am (9 years ago)

    Hi Tom, that's awesome thanks. This is one of the reasons I enjoy writing these articles because more often than not I end up learning stuff as a result too :) I've tested that code and it works great - could probably even put a fair chunk of that code into a custom function too. Thanks again :)

  • Daniel Wood 19/04/2011 8:27am (9 years ago)

    Hi Kevin, thanks for the comment & code. That's a very cool approach to the issue.

    One of the things I try to do wherever possible is to avoid hard-coding field names simply for the case if the field is renamed at any future stage the calculation will break. Of course it all depends on whether you can be sure it will change or not, and in a lot of cases I guess you can be sure. Working in a company with other FM developers however, who will pick up on a project in future, you cannot guarantee that unfortunately :)

    Having said that, I really like that solution, cheers !

  • Tom Elliott 19/04/2011 8:22am (9 years ago)

    Daniel

    The way I do this is to check whether the active field is the field in question

    Since Get ( ActiveFieldName ) returns an unqualified name and GetFieldName() returns a fully qualified name this requires a bit of fiddling, but is pretty straightforward:

    Let ( [
    TriggerField = Product ID ;
    RateOne = Home to Products for Rate Auto Entry::Rate 1 ;
    RateTwo = Home to Products for Rate Auto Entry::Rate 2 ;
    myFullName = GetFieldName ( Self ) ;
    myName = Replace ( myFullName ; 1 ; Position ( myFullFieldName ; "::" ; 1 ; 1 ) + 1 ; "" )
    ];
    Case (
    Get ( ActiveFieldName ) = myName ; Self ;
    not IsEmpty ( RateOne ) ; RateOne ;
    not IsEmpty ( RateTwo ) ; RateTwo ;
    Self
    )
    )

    by using the unqualified field name, this should work in any context

    cheers

    Tom


  • Kevin Frank 19/04/2011 8:16am (9 years ago)

    Hi Daniel, what about this for the AE calc? (I took your original calc, and added one new test at the top of the Case statement.)

    Regards,
    Kevin

    Let ( [
    TriggerField = Product ID ;
    RateOne = Home to Products for Rate Auto Entry::Rate 1 ;
    RateTwo = Home to Products for Rate Auto Entry::Rate 2
    ];
    Case (
    Get(ActiveFieldName) = "Rate Modifiable" ; Self ;
    not IsEmpty ( RateOne ) ; RateOne ;
    not IsEmpty ( RateTwo ) ; RateTwo ;
    Self
    )
    )

  • David Jondreau 19/04/2011 6:36am (9 years ago)

    Well, that's embarrassing. I could have sworn that worked.

    But Evaluate ( "Self" ) returns an EvaluationError() of 1225.

    You could reference the field itself in quotes, but then you're referencing field names literally which is generally a bad idea (though perhaps not in this case since it's tied directly to the field definition).

    Cheers,
    DJ

  • David Jondreau 19/04/2011 3:57am (9 years ago)

    Daniel,
    A self-join is one way around this issue, but even easier, in my humble opinion, would be to simply use Evaluate ( "Self" ) in place of Self.

    -DJ

  • Paul Spafford 19/04/2011 12:22am (9 years ago)

    That's cool, Daniel: A very creative solution!

RSS feed for comments on this page | RSS feed for all comments