Lookup vs Auto Enter

[Ron Cates]

My question is essentially what is the difference if any between an Auto Enter with replace field contents and a lookup field? I've read a lot of posts that describe using lookup fields but In all the cases I've read the same thing can be accomplished using an Auto Enter with replace field contents. Is this correct?

[Ocean West]

Yes you can do a bit more with auto enter calculation vs straight lookup - including branching logic and use the Lookup ( sourceField { ; failExpression} ) function to replicate the actual lookup function.

In addition using lookup calc - you can use variables such as $id and then set in a script if you change layout context when you create a new record in the table it will insert the value in $id into that field.

I have found it a bit faster in some cases it may be better performance to set a bunch of variables then navigate to layout and just create a record then it is to set variables go to layout and explicitly set fields.

[typewriter]

Most developers I talk to dismiss the lookup functionality as a relic from the past. Although I prefer the auto-calc, the lookup has a feature that may be just what you need in certain conditions: the next available value. Don't have an example at hand, though.

[Fitch]

That's what LookupNext is for:

http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref3.33.17.html#1028387

It's OK to use the Lookup option for auto-entry, but it's really just there for backward-compatibility: I don't think it does anything that can't be replicated with the Lookup and LookupNext functions, and the functions can be extended as part of more complex calculations, and can be used in scripts, etc.

[Ron Cates]

Thanks. That pretty much conferms what I thought :)

[We Can Deb]

I am wrestling with the same issue: What if I want to have the user's initials inserted into a field on a layout? If I use an auto-enter calc, then when new accounts/users are set up, the calculation must be changed with the new initials. I believe having a Table and Layout that functions as a lookup will allow a non-programming user to add new people without my intervention. Am I missing something? I am working on this right now and may come up with something else as a solution, but I hate to put these values into a calculation. I apologize if this is a different situation from this thread.

[comment]

It does seem like a different situation. Why don't you start at the beginning? Users should be allowed to add new people without the intervention of the developer, and you should not be hard-coding values into a calculation.

[We Can Deb]

Never mind, you were right--didn't need a lookup.

[We Can Deb]

Thank you for your reply. I now have a layout that allows the user to enter initials for any new account/user, but I grab the initials using a calculation, not a lookup. Thank you again!!

[Ron Cates]

Ok, now let's talk about how an Auto enter with replace field compares to an unstored calc. Can we?

Under what conditions will an auto enter with replace field contents update and when won't it?

As I undstand it, it will update whenever one of the fields in the calculation changes. For instance, if I use an auto enter with replace field contents to generate someones initials and down the road I change the name, the initials field updates. The advantage being that it stores the result until another change is made instead of being unstored as you would have with a calc field for the same purpose.

Of course the above example is using fields from within the same table. How does using fields across a relationship effect it?

I often use an auto enter with replace in a child record to pull in the primary key of it's grandparent. I use this to display all line items for a given Job in a portal without having to go through my invoices table. I just relate Jobs::ID to Lines::JobID. If an invoice is relocated from one job to another by changing the foriegn key in Invoices, am I correct in believing that field will be updated? Would it behave differently if I was pulling from the Job foriegn key field in invoices, in which case the triggering field is being modified directly as aposed to the value of the Job primary key changing due to establishing a new relationship?

Thanks for playing

P.S. Wonder if there is a shorthand for Auto Enter with replace field contents? AE/replace maybe? :geek:

[comment]

I am tempted to answer "no" to your first question, i.e. can we compare auto-enter to an unstored calc. Auto-enter is stored, and it does NOT "update whenever one of the fields in the calculation changes". It re-evaluates only when a field in the same record is modified (this includes a global field, so far as it influences the current record).

[Ron Cates]

Thanks for clearing that up comment. For further clearification, when you say on the same record, is that meant to include parent records and possibly on up the one side of the relationship? As i mentioned, I use it in that way all the time in my solution and it re-evaluates when a refferenced parent changes? For example, I have a status field in my lines table that auto enters the status from the status field in invoices and the value in lines updates whenever the value in Tickets is changed.

[comment]

when you say on the same record, is that meant to include parent records

No.

For example, I have a status field in my lines table that auto enters the status from the status field in invoices and the value in lines updates whenever the value in Tickets is changed.

Not sure I understand your example (where is Tickets?). I think I can say for sure that you do NOT have a stored field in one table that updates as a result of changes in another table.

[Ron Cates]

Ok, scratch that example. I was trying to reproduce it and realized I am setting that field in the script that fires when status is changed in tickets. The other way I've been using it to extend foriegn keys down the line so to speak is always set on record creation and never need to be updated because the record creation is controlled For instance when an invoice is created a job has to be selected and connot be changed after creation, and if an invoice is created under the wrong Job we delete it and recreate it under the right one.

Sorry for the confusion. That's what I was trying to clear up myself I guess

It re-evaluates only when a field in the same record is modified (this includes a global field, so far as it influences the current record).

And that clears it up nicely

Thanks comment :)