How do I copy data from one table to another based on specific record?

[Tim Clawson]

I know I'm overlooking somethin simple.

I have a table that tracks an asset. Each asset has a description and other static info to describe the asset. Although we have many assets that have a unique serial number, basically, thier descriptions are the same, i.e. cell phones. We have many Model 6010s, each has a unique EMSI number, but all have the same descriptions.

I have created a related table with a buch of item descriptions (4 or 5 fields). In the main asset form I would like to select a product (by a drop down list, which I have done), then be able to complete the rest of the input of the form. However, to speed data input, I would like to create a button tied to a script that will pick up the product name, match that to a product name (fKey) in the description table and copy the 4 field's of description information into the form I am filling out. A quick automated process to minimize the effort of inputing redundent data.

Yes, I can use 'duplicate record' and update the necessary fields, but we are getting a lot of data entry errors because people are not modifying all needed fields and saving the record with inaccurate data. They would rather simply have a button that will populate specific fields based on the product name.

Can anyone point out the obvious to me?

PS: I just found and joined this Forum and I'm already finding it invaluable. Thanks organizers!

[comment]

I don't see why you would need to copy anything. Simply place the related fields from the description table on the layout of assets (and disable entry, so that users do not inadvertently modify the description for all products of the same type).

If you do want to be able to modify the description for each asset, define the same fields in the assets table, and make them lookup the data from the description table.

[bcooney]

..and don't match by product name. Have your relationship match by IDs.

Inventory.zip

Edited April 2, 2008 by Guest
Added demo file

[TimC]

Thanks for the direction. I did go ahead and use an auto fill option with a lookup to the descriptions table. The only thing I don't like is that it's not optional. A button would make the copying optional. I would still like to explore putting this into a script and attaching to a button. Any thoughts?

[TimC]

I would still like to explore the possibilities of a button and script if anyone has some ideas.

Edited April 3, 2008 by Guest

[TimC]

Thanks for the sample file, however, it appears locked and I can't open it to look at your programming. Could you resend the file unlocked?

[comment]

Sure, you could make a script that sets asset fields to the related values from description. Or you could make the lookup conditional (see attached).

LookupOnDemand.fp7.zip

[Tim Clawson]

Thanks...great example. I'm goint to play with that one. You gave me some ideas about the "Case" function.