Populating multiple fields from one lookup

[Halvo]

Does anybody know how I can automatically fill out multiple fields on a layout based on a single lookup? e.g. I want a person's name to be looked up and copied into a person field in another table, but I also want it to copy the data from the person's address fields into corresponding fields in the other table automatically at the same time.

[Kent Searight]

When a relationship exists between two table occurrences, you can have as many lookups from 1 table to the other as you'd like. Example: When the key in TableA relates to the key in TableB, make "name" in TableA lookup "name" in TableB, make "address" in TableA lookup "address" in TableB, etc., etc.

Alternatively, you could use auto-enter calculations instead of lookups.

[T-Square]

Why duplicate the data in the first place? Adding duplicated data into a database ensures (based on Murphy's Law) that your most mission-critical process will somehow get the outdated version, not the current one. It also wastes storage space.

If you have the person's ID number, that ensures that you can retrieve all the information as you need it.

David

[LaRetta]

I agree in theory, David, but there are times one would want the data duplicated. Example would be an Invoice or Contract - something where the entire document must be planted in time. Relying on related data which may change (such as Customer's address) simply wouldn't be sufficient.

L

[Kent Searight]

As LaRetta points out, capturing data from a specific point in time would be a likely use of multiple lookups or auto-enters. Another example would be an estimating program that uses production standards values and pricing values that are pulled from a Preferences table. Also, a startup script that requires many fields to be populated. You could either SetField many times or create a temporary relationship(s) that pulls in the data more efficiently.

Having said all that, I can't speak for Halvo.

T-Square, you raise a valid issue that Halvo should consider (if not considered already) if duplicating data is for good reason or not.

[T-Square]

Good points, LaRetta and Kent.

Just to play devil's advocate here, in an invoicing system, once you've printed the invoice and shipped the item, why do you need a historic record of the address that an order was shipped to?

And then, in a contract system, wouldn't you need a hard copy of a contract for legal purposes? I.e., you need a hard copy for legal purposes to plant the contract in time. And if there's a hard copy for the legalities, the question of why the database has to do this is valid again.

Just thinking out loud.

David

[Kent Searight]

Just to play devil's advocate here, in an invoicing system, once you've printed the invoice and shipped the item, why do you need a historic record of the address that an order was shipped to?

Here's a scenario:

You've shipped a product to your customer's shipping address. A few days later, that company notifies you that their address has changed (they've moved). You then update shipping info in their Contact table. 2 days later the company calls to tell you that they haven't received their shipment and they speak with one of your coworkers who isn't aware that they moved. He/she pulls up the order info in the db and says, "Our records indicate we shipped it on 10/10/2005 to [new address]" In the meantime the shipment has actually been sent to the original address, but there's no record of that happening since shipping info was updated everywhere.

And then, in a contract system, wouldn't you need a hard copy of a contract for legal purposes? I.e., you need a hard copy for legal purposes to plant the contract in time. And if there's a hard copy for the legalities, the question of why the database has to do this is valid again.

Where would you file the hardcopy so that your office in Boston can view it from San Francisco? :qwery:

[LaRetta]

To add further ... Data within a system IS the hard-copy version. Even scanned signatures are acceptable (in some instances) and are considered legal (in some instances). Many businesses, me included, work hard at the paperless office theory. Many auditors and accounting firms concur it is a waste of natural resources and electronic storage is a viable, sound options. It also saves some poor person from filing thousands of hard-copies a day, not to mention the time in trying to find it if it is misfiled. FM saved one business $2,400 per year in paper & ink alone; not to mention staff time in dealing with the paperwork.

Perfect example, Kent. Pricing changes, even product descriptions may change. The ability to look back at an old invoice to see the specific product description has protected me in a product recall. Having an electronic hard-copy is the only way to go and I know some people print final copies, file those documents and delete the data but the ability to search by any field and retrieve a document instantly is worth keeping on the system.

Redundancy is important.

CEO,

The Department of Redundancy Department