Look Up vs. Calculation vs. GetField -- Pros and Cons

[geraldh]

Forgive me for adding another topic so quickly.

When wanting to populate a certain field with the contents of another field I understand that you can us Look Up or a Calculation.

I understand the basic difference between using Look Up and Calculation.

But then I ran across GetField today, and now I am wondering what route is best to you use in certain circumstances.

Can anyone warn me about the dangers of using a certain method in certain cases?

Thank you

[Vaughan]

The GetField() function allows the developer to put the name of the field in a calculation, so it's not hard-coded. It can be used for instance, to allow a user-selectable export.

Eg, Record has fields title, first, last with values "Ms", "Elizabeth", and "Windsor" in them.

GetField( FieldSelect ) will return "Ms" when FieldSelect = "title".

[Agnes Riley]

I generally try to script this process. I only use calculations when you cannot script something or the procedure forwards and backwards would be insanely complicated.

[Ocean West]

Lookups are FileMaker legacy function that has its merits -

however the auto enter calculation method is something I tend to lean more towards

the legacy functions such as Lookup ( sourceField { ; failExpression} ) and LookupNext ( sourceField ; lower/higherFlag ) can still be used.

Also lookup can only reference ONE source field where as the calculation you can have branched logic and pull in data from different sources based

on other known values.

With the new SQL features in 12 or ( FMP<12 + Plugins ) you can also now use the auto enter calc to in effect "lookup" values for fields with out having to add table occurrences or relationships on the graph.

For example...

instead of having customer >--< zipCodes based on zip code to lookup the City & State fields.

City (auto enter calc):

Select ( city from zipCodes where ? = ? ; "" ; "" ; customer::zip ; zipCodes::zip )

State (auto enter calc):

Select ( state from zipCodes where ? = ? ; "" ; "" ; customer::zip ; zipCodes::zip )

*note my syntax may be off as the caffeine hasn't set in. :cofeebrake:

[Agnes Riley]

Believe it or not, a lot of people still use lookup, even though it can generally only be used once and difficult to update. There are a lot of in-house databases that are leveraging lookups.

Sometimes it's hard to tell whether I'm better off using a calc, and sometimes it's easy, I guess I'll look at it from a scripting perspective first and then resort to a calc if it seems t make more sense. :-)

[David Jondreau]

With the new SQL features in 12 or ( FMP<12 + Plugins ) you can also now use the auto enter calc to in effect "lookup" values for fields with out having to add table occurrences or relationships on the graph.

This isn't entirely true. Auto-enter calcs won't allow you to use unrelated fields. You could put the fields in quotes, but then if you change a field or table name, you take a hit.

If you've got a squid relationship graph, you're ok. With Anchor Buoy thoug, it's tougher. If you've got "some" relationship between the tables, like in a functional TOG, you're ok.

If you're really into the Separation Model, I suppose you could have one TO for each table all related to each other in a chain and go from there.

[Rick Whitelaw]

At first I was loath to use Lookups . . . Many years ago. I have a solution that generates payrolls and writes cheques among other things. Although this isn't the only way, I have a table, call it Cheques. A cheque has an address of the payee. Looking up the address from a Contacts table into an address field in the Cheques table preserves history. I can select any record and see the address that was current when the cheque was generated. This can be important for many reasons. Like I said, it's not the only way, but I figure it's as good as any.

RW

[geraldh]

Thanks for the illuminating discussion.

Currently my interest in Lookups is because I need to populate the unit price of a certain Good or Service in an Estimate or Invoice Line.

As this price is certain to change yearly or more frequently, and as it is important to preserve a record of the prices at the time of the creation of the document or transaction, I think Lookups is most appropriate in my case.

The reason I started wondering about using other methods was that I believe you can use a calculation that is not necessary dynamic? A one-time deal? But hearing that calculations can slow the performance of your computer, I was just wondering about the trade-offs of each possible method.

[Rick Whitelaw]

I suppose one could use an auto enter calculation that uses the Lookup FUNCTION and check "do not replace existing records", but I don't see the advantage.