Are lookups old-school?

[David Head]

When creating a new record, some fields may be set to auto-enter values such as a serial number or date of creation (creation of the record that is, not the universe! - even FileMaker Pro doesn't know when that was).

 

One auto-enter option that has been in FileMaker Pro since it was a boy is the lookup. Personally, this has always been confusing for me because it seems to be written backwards to the way I think. But that is for another thread.

 

Traditionally, a lookup is used to look up a value in another table based on a relationship and to 'copy' that value into a field in the current record. In this way, the new record contains the related value at the time it was created.

 

The classic example is for a line item on an invoice. When the product code is entered, the unit cost is looked up in the product table and copied into the line item record. In this way, the item price is not dependent on the product price so when the product prices are updated, the invoice items stay at the old price.

 

Quite some time ago, FileMaker added a new auto-enter option - the calculation. Using this option, the user can enter a calculation, the result of which is auto-entered as soon as it can be evaluated. So let's say the calculation for the UnitPrice field in an ITEM record is:

 

PRODUCT::SellPrice

 

The standard relationship from ITEM to PRODUCT is:

 

ITEM::ProductID = PRODUCT::ID

 

So as soon as the ProductID is filled in for the new ITEM record, the relationship becomes valid and the UnitPrice is filled in with the related product SellPrice.

 

Hmmm, that works just like a lookup!

 

The auto-enter calculation option has a checkbox called "Do not replace existing field value (if any)". If this is checked, the existing field value will never be replaced, even if you change the ProductID. If it is unchecked, the value will be updated each time you change the ProductID (just like a lookup works).

 

So back to the original question - are lookups old-school?

 

To answer that, we need to look at the way lookups work and see if they offer any advantages to auto-enter calculations.

 

One option lookups do offer is what they do if there is no exact match. you can set it to do nothing (default), copy next lower value, copy next higher value or use some specified data. OK, if I need that option, I will use a lookup. Maybe.

 

Any other advantages? Nope.

 

So what of the advantages of an auto-enter calculation?

 

First, they make sense. Just like any calculation you construct with related fields.

 

Second, because you have open access to the FileMaker calculation engine, you can do much more than lookup one value via one relationship. This is something many people request of a lookup. Consider the following calculation for ITEM::UnitPrice:

 

Case ( 
 ITEM::type = "product"; PRODUCT::SellPrice;
 ITEM::type = "service"; SERVICE::HourlyRate;
 ITEM::type = "comment"; ""
      )

What you have here is a smart lookup - it can use one of several relationships to lookup a unit price. In this case, that depends on the specified item type for the line item.

 

So can you get by without using lookups at all? I think you can. smiley_cool

[LingoJango]

OTOH, the mechanics of updating in lookups are far clearer to the non-expert user: relationship changes, lookup contents change. That's it. With Auto-Enter, the ins and outs of what gets changed when are still hard for me to remember, let alone deal with adequately.

 

So, for the novice a simple lookup is convenient - as long as they steer clear of unintended relookups.

[AHunter3]

It reminds me of the use of If () as opposed to Case () in calculation strings: redundant at this point for the most part, but a fixture that predated what now exists that could replace it.

 

As recently as FileMaker 6, auto-enter calculation as an auto-enter option did NOT come with the choice of replacing existing values. Auto-enter calc happened at record creation time and that was that, if you wanted otherwise you found a way to do it via lookup or as an actual calculation field (or quite often via some convoluted combo of the two plus a selfjoin relationship).

 

Once that checkbox for allowing the replacing of existing values came into being, the Lookup became virtually obsolete. I still use 'em all the time where they do the trick just because I, too, am old-school, but I would not teach them except for the special exception case cited by David, the "what to do if nothing matches", which lets you do a few obscure & cute tricks.

[egulk2000]

i think i am noticing a good reason to have lookups over calculations, and please correct me if i am missing something..

 

a lookup enters data automatically as it is an auto-enter function. but what makes the lookup different is that it will update the fields information based upon the lookup relationship, which is something the calculation will not do.

and let me explain..

 

if you are using the lookup to input a contacts address based upon the contact fk of "bob", if you were to change the contact fk to another contacts "john" the address will update to john's address and not bob's.

 

while if this type of lookup was done with a calculation it would only enter it the first time it was entered and not continually update it. and to uncheck the option "do not replace existing value" would cause the fields contents to be basically unmodifiable, constantly updating the information based upon the contacts address. which can be a problem if we were talking about a phone number and the user wanted to change the phone number in this instance but keep the other data...

 

the reason why i am coming across this is that i am trying to auto-enter contact information into an order sheet but i am having trouble with entering the phone numbers because i have a auto-enter calculation to format the phone number correctly, with the option "do not replace existing value" unchecked, and i am trying to integrate the lookup into that formatting calculation, but i am running into a wall where i cant change the phone number for this order because when reformating the number it re-enters the original contact phone number....

 

am i missing something?

[David Head]

if you are using the lookup to input a contacts address based upon the contact fk of "bob", if you were to change the contact fk to another contacts "john" the address will update to john's address and not bob's.

True enough.

 

while if this type of lookup was done with a calculation it would only enter it the first time it was entered and not continually update it.

Only if the "do not replace existing value" is checked.

 

and to uncheck the option "do not replace existing value" would cause the fields contents to be basically unmodifiable, constantly updating the information based upon the contacts address.

That is where you are wrong. If this option is unchecked, the data in the field can be modified just as it could for a lookup. The only time it would be updated is when the "contact fk" was changed - the data would overwrite any changes made. IOW, it acts just like a lookup would.

 

which can be a problem if we were talking about a phone number and the user wanted to change the phone number in this instance but keep the other data...

Not at all. Why would the other data update if you changed the phone number??? Unless you are using the phone number as the foreign key! And you wouldn't do that would you?

 

the reason why i am coming across this is that i am trying to auto-enter contact information into an order sheet but i am having trouble with entering the phone numbers because i have a auto-enter calculation to format the phone number correctly, with the option "do not replace existing value" unchecked, and i am trying to integrate the lookup into that formatting calculation, but i am running into a wall where i cant change the phone number for this order because when reformating the number it re-enters the original contact phone number....

I saw your other post and didn't have time to respond to it. You say you are trying to "integrate a lookup". I would suggest that you don't use a lookup at all but use a related field value where one does not already exist. Something along the lines of:

 

Let ([
field = if ( IsEmpty (yourfield); Table::relatedField; yourfield )
];
calculation to format the field here
)

 

I haven't had the time to test it properly, but it should not override the phone data with the related value is phone data already exists. Make sense?

 

am i missing something?

Yes smiley-wink you are or were!

[LingoJango]

I would suggest that you don't use a lookup at all but use a related field value where one does not already exist. Something along the lines of:

 

Let ([
field = if ( IsEmpty (yourfield); Table::relatedField; yourfield )
];
calculation to format the field here
)

 

I haven't had the time to test it properly, but it should not override the phone data with the related value is phone data already exists.

 

For the sake of simplicity, let's say the phone number is a field in a contact record which is linked to a project record. The contact's phone number gets entered for the project. Then it turns out the contact will be dealing with this project while in Switzerland, and gets a personal Swiss cell phone. Phone number is manually updated.

 

Then that person leaves the company and someone else takes over the job. With your calc, the phone number does not in this case get updated, because there is content in there already. A lookup does get updated. And an autocalc won't get updated if it's in a "parent" record - i.e. updating of an autocalc will be triggered by changes to fields in the same table, but not to fields in a related table.

[kirkrr]

It sounds like there is an ER deficiency here. Is the phone number assigned to a person or to a role?

 

If a role, then the # would change with the person.

If a person, then the person assigned the role would change, along with the phone number. In today's day and age, 3-4 phone # per person is not at all unusual, so maybe the phone fields should be a related record???

[Norma_Snockurs]

Just my one pence (that's two cents if you live here in the U.K.), whilst I agree that there is a deal more flexibility using a calculation to perform lookups there is one niggle for me.

In the Define/Manage Database window the Options/Comments listed next to fields will only flag your field as a Lookup if it is indeed defined as such. Using the Auto-enter calculation method leaves the field Options titled as just that, an Auto-enter Calculation.

Admittedly this has no bearing on the actual functionality gained but as a cosmetic detail it can be annoying at times as, two-months later when you come back to modify your solution, it leaves you guessing as to the general workings of the field unless you either open the Options for each one or trawl through your DDR looking for the calc details.

[kirkrr]

If, in the Manage>Database>Fields screen, you select all fields and pick PRINT from the lower left corner, you get far more detail than what the screen display shows. This may be the documentation that you desire.....

[David Head]

...there is one niggle for me.

In the Define/Manage Database window the Options/Comments listed next to fields will only flag your field as a Lookup if it is indeed defined as such. Using the Auto-enter calculation method leaves the field Options titled as just that, an Auto-enter Calculation.

... it leaves you guessing as to the general workings of the field unless you either open the Options for each one or trawl through your DDR looking for the calc details.

That is true and a reasonable feature request to be able to see more options if available. However, the same comment could be made for a lookup - when a field is defined with a lookup, it doesn't say what is it looking up and from which relationship. Same difference?

[imacuser]

One more thing to consider is the concept of the database as a data repository.

 

Here's what I mean:

 

Some data is meant to be stored and never modified by either calculation or lookup/relookup. You may be required to store data for a definite/indefinite length of time. As such, you may want to COPY data (using temporary global fields or somesuch method) as opposed to linking the fields by calculation or lookup.

[kirkrr]

Some data is meant to be stored and never modified by either calculation or lookup/relookup. You may be required to store data for a definite/indefinite length of time. As such, you may want to COPY data (using temporary global fields or somesuch method) as opposed to linking the fields by calculation or lookup.

 

EXACTLY what a lookup function (be it old-school or David's neat approach) - when the record is created, default values are assigned from the lookup. This is a one time thing to assist in data entry. You can then overwrite the fields with anything you want, and the lookup source data does not change.

 

e.g. customer has a bill to and ship to address in the customer record.

An invoice is created, which, by lookup, pulls these addresses into the invoice form shipto and billto address fields. In this case, shipto exists in 2 places, by design - the customer record (default) and the invoice instance. However, the item is being shipped to someplace else. Type over the invoice shipto data that currently holds the default information. This edit is only in the invoice form, and does not change the customer record. The invoice is a stored instance that will never change without user interaction to overwrite the fields. Lookup will never re-execute unless you issue a re-lookup command specifically to do this, and Filemaker will always warn you about these actions.

 

Personally, I am going back through some current code and seeing where I can apply David's alternative-to-lookup approach effectively. I like the aspect of control, particularly the antithesis of the lookup - being able to dynamically change by turning off the "do not replace" flag when (if?) that change is desirable on parent record updates, and on, when it needs to work like super-lookup (or even simple lookup).

 

Unless I am missing something, I think that is what you were asking for.....

[Josh Ormond]

OTOH, the mechanics of updating in lookups are far clearer to the non-expert user: relationship changes, lookup contents change. That's it. With Auto-Enter, the ins and outs of what gets changed when are still hard for me to remember, let alone deal with adequately.

 

So, for the novice a simple lookup is convenient - as long as they steer clear of unintended relookups.

 

I thought I would chime in just keep this rather entertaining thread going!!!

 

I partly agree with LingoJango...but at the same time agree completely with David. At first I used Look-ups everywhere, then discovered the benefits of Auto-enter calcs. Now it is rare that I use look-ups at all. In fact I have just about replaced every instance of lookups in any of my solutions with Auto-enter Calcs.

 

Originally Posted by egulk2000

the reason why i am coming across this is that i am trying to auto-enter contact information into an order sheet but i am having trouble with entering the phone numbers because i have a auto-enter calculation to format the phone number correctly, with the option "do not replace existing value" unchecked, and i am trying to integrate the lookup into that formatting calculation, but i am running into a wall where i cant change the phone number for this order because when reformating the number it re-enters the original contact phone number....

This actually sounds like your problem is the fact that your Auto-enter calc references itself. If you accommodate for the variables involved you can create a calculation that does the format, but still lets you make the change.

 

Here is an auto-calc that I use to make that happen (Note: I have FM Advanced and use a custom function named PhoneFormat...you would just need to replace that function with the actual formula you are using):

Case (
   IsEmpty (Invoice::Phone); PhoneFormat (Customer::Phone);
   Customer::Phone = Invoice::Phone; PhoneFormat ( Customer::Phone );
   Customer::Phone ≠ Invoice::Phone; PhoneFormat (Invoice::Phone)
)

 

For the most part you can accomplish anything you wish, sometimes you just need to be open to an "odd" way of doing it. Something I have learn from these forums. You can get upset because something isn't a built in function...or you can find a work-around until it is.

Edit: Also, one feature, that was a huge help, I started implementing to avoid accidental "re-lookups" was a field to indicate that a record was closed [Yes/No]. Then incorporated that into the formulas: If (Closed = "Yes"; FieldName; Calculation)

[tesserac]

One option lookups do offer is what they do if there is no exact match. you can set it to do nothing (default), copy next lower value, copy next higher value or use some specified data. OK, if I need that option, I will use a lookup. Maybe.

 

Is there a way to copy the next higher or lower value through a calculation? I've just upgraded to 9 from 8, and I was surprised to discover that these lookup options no longer seem to work.

[Josh Ormond]

You should be able to create an auto-enter calculation to do this.

 

Check out these functions in Help:

Lookup ( sourceField {; failExpression} )

 

LookupNext ( sourceField ; lower/higherFlag )

 

 

If you need help with the calc, post back some details about your structure and what you are trying to do. Someone should be able to get you moving.

[tesserac]

It's for a field that enters a client name automatically when you start typing it in that field. The client names are part of a separate database that is looked up.

 

This is what I've just tried:

 

Contact_First = LookupNext (Clients::First_Name; higher)

 

-- field remains blank

 

Contact_First = Lookup (Clients::First_Name; "fail")

 

-- "fail" gets auto-entered every time, even when entering known matches.

[David Head]

Both these are working as expected for me:

 

Lookup ( Customer::Customer Name; "Unknown" )

LookupNext ( Customer::Customer Name ; Higher )

 

Both are calc fields with a text result.

 

What is your relationship from Contacts to Clients?

 

You said "It's for a field that enters a client name automatically when you start typing it in that field". Does that mean you have this set up as an auto-entered calculation? :confused:

[Inky Phil]

Good thread this. Thank you David for kicking it off

 

Phil

[tesserac]

What is your relationship from Contacts to Clients?

 

Contact_xx is the name of the field in a work request tracking db (I have separate fields for first name, last name and email).

 

Clients is a second database of all potential clients (with fields for first name, last name, email, and other pertinent info)

 

You said "It's for a field that enters a client name automatically when you start typing it in that field". Does that mean you have this set up as an auto-entered calculation? :confused:

 

Exactly. The aim is to speed up entry in the tracking db (and ensure it matched the spelling entered in Clients db) by simulating an auto-complete feature. It would defeat the purpose if I turned this text field into a calculation field.

 

Right now, I've been using the Insert from Index menu command as a workaround, but it's obviously not as ideal as the behaviour under FM 8.0.

[AHunter3]

The ability of a field to auto-enter a value looked up from elsewhere has been around since Macintosh System 3 running on a Mac Plus & booted from an 800K floppy was the hot system to have.

 

Made its debut in Nashoba Systems "FileMaker Plus". It was a killer feature and one of the things that propelled FileMaker beyond the other little flat-file nonrelational database apps of that time. You could enter info in ONE database file, like contacts and their tel numbers and stuff (probably not email, who the heck had email back then?), and when you entered the same contact in a different file, you could have the telephone number LOOK UP from the other file!

 

As with a lot of things this venerable, lookups might not be included in the package if FileMaker were being designed from scratch now — insert calculated value could just be expanded to handle the tiny bit of extra options still native to only the lookup —*but mostly I think they are "old school" in a good way, an old friend, a long term success story. (Unlike, say, repeating fields which are also old school but not from a very good school system and with a really awful report card)

[Techphan]

For Al-

 

I have seen questions and answers regarding repeating fields (and your comments that you do not like them).

 

So... that prompts me to ask:

 

1) What benefit were they when introduced (or - what were they used for)?

2) Why does FM perpetuate them in v9 (or would it create havoc with upgrading older databases that had used them)?

[AHunter3]

When they were introduced, there was no such thing as a portal; there was no such thing as the ScriptMaker either. There was such a thing as a lookup (repeating fields were introduced in FileMaker Plus at the same time as lookups) but you could not script the process of opening a different file and doing a FIND to bring up the records with the same corresponding keyfield.

 

So repeating fields were effectively the only way to do things like line items of an invoice, contact people at a company, etc.

 

(Well, OK, you could create a huge raft of NON_repeating fields, I guess. ContactName Line One, ContacttPhone Line One, ContacttTitle Line One, etc, then ContacttName Line Two, ContactPhone Line Two, ContactTitle Line Two...not an improvement!)

 

So they served a purpose when introduced.

 

They became a lot less necessary with the advent of FileMaker Pro 2.x which gave us the scriptmaker. The scriptmaker could COPY a field (such as the serial number) in the current file record, then perform a remote script in the other file that would go into Find Mode and PASTE it there into the Foreign Serial field and perform that find. Zip Zingo, you are now in the world of Line Items, and if each line items record looks up all the parent-file info, you can see that too and put it at the top of your layout.

 

It was perhaps awkward and klunky — to move from the parent level of analysis to the child-records level, you had to LEAVE the layout and go to a totally different file and find the child-records, and once there you could not directly see or edit the parent-level info, only lookup fields based on them. You also could not sum up aggregage line-item level data in any live way; for someting like a grand total of line item prices, you had to script the pasting in of a total back in the parent record after editing the child record(s) and hope your users used the buttons and not just close the window or do a manual find in child-record-land, therefore bypassing that scripted updating!

 

But unlike repeating fields, these line-items-in-separate-file records could be usefully searched (like all Active employees; you try that with repeaters you get a found set of Companies that have an Active employee; you can't filter out the inactive employees that are in the repetitions above and below the active ones), and especially in complex finds (like all the North Carolina based employees who are currently of status "Active"; you try that with repeaters you get all the records of Companies that have at least one North Carolina employee and have at least one active employee, not necessary an active North Carolina employee), and sort them (just try sorting repeaters!), etc. So there was a case to be made for not using repeaters even if there was a case to be made in their favor.

 

But where they really fell by the wayside as being of any legitimate user for data was with the coming of FileMaker Pro 3: now you could put actual portals on a layout. You could stay on your Clients screen and see their employees in a portal. You could do your data entry right there. You could sum up values from related records. There was now very little you could do with repeating fields you could not do with an external table and a relationship instead.

 

Why does FileMaker Inc continue to keep 'em around? Backwards compatibility yeah, but that didn't keep them from ditching the Today function. (the Today function was a stored, and indexable, version of Get(CurrentDate) that would auto-update when you first opened the file a day or more later than the last time it had been opened; didn't work well on FmServer; cumbersome and time-consuming to wait for it to update in the mornings even on non-served local databases. They ditched it in FileMaker 7; converted files get a not-really-compatible replacement combo of a field named "Today", which is a date field auto-enter option Creation Date, and the original field that referenced the Today function now references the Today field).

 

So I don't know why they don't throw out repeating fields as well. To be sure, it's less obvious what you would do with converted files that had them. Be a bit awkward to create entirely new tables and relate them on-the-fly during conversion. Maybe that's why.

[Ender]

Repeating fields still have utility purposes. Just keep them away from data-entry screens.

[AHunter3]

I agree that they can be useful for things like graphic elements used for interface purposes, in different reps of container fields, etc.

 

But not data.

[Techphan]

Repeating fields still have utility purposes.

 

Please tell... what kind of "utility" purposes?

[Josh Ormond]

what kind of "utility" purposes?

One way I have used them in variables to do data transfer data.

 

Basically 3 variables. One that is actual data, and another that tracks the repetition, and one the tracks the value. Grab the data in one swoop, in a return delimited list, one record per repetition. Then drop the data in a new record and increment the repetition and value as you go. Works beautifully and faster than many other techniques I have tried (obviously not transferring a million records or anything).

 

While that idea is taboo for most things, we have an ad database that simply "duplicating" an ad on renewal is not functionally appropriate.

 

I am sure ender has a storehouse of awesome little tricks that he uses them for...hopefully we can get him to spill the beans. He has always been generous with sharing of knowledge. And ender, we appreciate, GREATLY! Along with Alan, Maarten, David, Chris, Michelle, Harry and all the others.

[AHunter3]

I prefer $variables for data transfer. I've done large arrays all in a single variable and used Middle to pick values back out on the other end.

[Josh Ormond]

I prefer $variables for data transfer. I've done large arrays all in a single variable and used Middle to pick values back out on the other end.

I kinda do the same thing, but I am still working on my Middle skill set. It is just easier, right now, for me to grab each value and progress through the reps.

 

Someday I'll get time to play around with the Middle function and figure it out.

[Ender]

Please tell... what kind of "utility" purposes?

 

Repeating fields can be good for certain things that don't require user-interaction. There are a few different things I can think of.

 

I think somebody mentioned using them to hold container graphics. This might be a library of possible graphics to use, where you reference a particular cell in the library anytime you need that specific graphic. Or you can use them in a more dynamic fashion, where the cell number is calculated or stored as a preference, and a calc pulls out the referenced cell for showing which button to use on the layout. For example, if the user changes from color scheme 2 to 5, all the buttons change to repetition 5 for their respective graphic, and voila, the button colors are dynamically changed from hot pink to lime green.

 

 

Starting with FM7, there's great utility in the get(calculationrepetitionnumber) function. I utilize this in this simple progress bar example:

 

http://www.filemakertoday.com/com/showthread.php?t=19144

 

Though others, like our friend comment, have come up with other clever techniques that can, in a limited way, replace CFs. For example, here's a simple Fibonacci calc:

 

n (number)

 

reps (repeating calculation, number result) =

 

Case(Get ( CalculationRepetitionNumber )

Get ( CalculationRepetitionNumber )

reps[Get ( CalculationRepetitionNumber )-1] + reps[Get ( CalculationRepetitionNumber )-2])

 

 

The nth Fibonacci number would then be the last() value.

 

 

Then there are the very speedy cell-splitting import properties that FM has. If you have data in repeating fields, FileMaker will ask if you want to split repetitions into separate records upon import. If you had a need to populate a large number of child records, you could use repeating fields to hold all the bits of information that should be different for each new child record. This might be the days of the year in a calendar database (a simple repeating calc using get(calculationrepetitionnumber) for 366 cells). Or maybe Attendance records for a classroom.

 

 

Sometimes repeating fields are also useful for displaying data to users. Maybe where you're trying to summarize a lot of related data, you use scripts to populate the repetitions and then display the repeating field instead of portals and related fields. One advantage here is display speed. Repeating fields are quick to redraw on the screen, where lots of portals or aggregate calcs can be slow. Another advantage is having some different options on how to format the information.

 

As an example, we saw Matt Navarre give an interesting session this year at DevCon about implementing a "google-like" search in FileMaker. The main difficulty with this sort of thing is that searches in different tables are done in different layouts, and there's not really a way to show the resulting records together, in one window. Matt's approach was to take the top results from each table, and grab bits of information to insert into a bunch of repeating fields for a Results page. The results were grouped and labeled by which table they were found in, and you could easily jump to a found record or found set from a particular table by clicking on a row.

 

 

In all these cases, scripts or the calcs themselves are what's populating the repetitions. The user doesn't get to poke around in them. And that's the way to keep it.