Copy/paste record from one file to another-newbie

[Mango1]

Aloha - We have a simple problem. We have a vendor database that contains names, addresses etc for the vendors. Our other database is for purchase orders. All we want to do is find the correct vendor, and copy their info onto the purchase order so that we do not have to type in the info each time. So simple yes?

My question is this: Which direction should I be going in to accomplish this? 1. Write a script (that was fun and almost worked) 2. Set up a relational database 3. Use portals.

I want to to focus on the one correct area since I will be teaching myself how to do this.

You all sound so advanced, I hope this question is not too FMP 101. Mahalo

[ESpringer]

Hi Mango!

There is really a choice between two strategically different approaches. Both involve the same kind of relationship, but the choice you need to make is: do you want to have the vendor address information "frozen" into each invoice, or do you want it to remain "flexible" as your vendor information changes? Either way, you'll have a relation matching the Vendor ID field in the purchase order record and the Vendor ID field in the Vendors file. Note, if your vendor names are long, you may want to have the Vendor ID be some shorter ID code for the vendors, or at least use a VALUE LIST of all vendor names to make data entry clean. I'll assume you're able to set up that relation. OK, so the options:

Choice A, using Lookups, uses ordinary text fields in your PurchaseOrders file, but fills them in automatically based on the data in the Vendors field. Use the Define Fields > Options button to set up auto-enter Lookup process for the Vendor Address field(s). A Lookup is much like a script, but FM standardizes this process and does it for you when you enter the vendor name. This address data will now be "frozen" in the purchase order (unless you do a Re-Lookup). In other words, if you change your Vendors record by updating address, correcting phone, deleting a field, whatever, this will not affect the data in the purchase order's record. This is useful if your file serves as an archive: if you ever want to look at a five-year old purchase order needing to know where the vendor *WAS* at that time, this is the way to go.

Choice B, using display of related fields (either through portals, or by placing the related fields directly on the layout), uses the relation "live" to display the corresponding data in the vendors file, and actually does not put *any* data directly into the purchase order record. To do this, simply add a field to your purchase order layout, but use the "Specify Field" process (you can control-click on the field to bring this up) to point over to the related file (see the drop-down list at the top of Specify Field) and choose the address field from the Vendors file. This kind of data viewing is "flexible" in that if the vendor's information changes, your purchase order will immediately show the new data. This is a more efficient data structure because you don't have the same address text being replicated all over the place. If you are just using this address info so that the vendor info shows up right and prints to paper when you process the order, then it's more straightforward. When you find an old purchase order record, the vendor's *current* address data will show up. (This could also be useful, as when you need to follow-through with a vendor on an old order.)

Feel free to ask more: others will have additional insights.

[Mango1]

ESpringer you are the best. Thank you so much for taking the time to respond. We will go with Choice B. It sounds perfect. Wish me luck.

Mahalo nui loa, (thank you very much)

Mango1

[justcosmic]

Just read your very informative reply on the above topic.....what interested me was the suggestion about using a value list for the match field....do you have to keep updating the value list every time you ad a new record, or is there a calculculation or something to "list all" contents of said field?

Equally grateful for any advice you're able to offer on this...

....EDIT....few hours later, & I've discovered how to use the value list...its real easy, and an excellent tool....thanx again for the expert tips...