Copying Data From One Table To Another

[Mavhack]

Right here's the dilemma.

I have 3 tables...

1 with customer data in

1 with product data in

1 to log orders on

What I need to do is the following....

When a button is pressed on the customer data table it, passes the customer data from that record to the orders table.

I have tried several ways todo this, looked up values etc but not of which seem to work!

ANybody got any ideas? I've racked my brain for several days over this and it's probably something simple, but I just can't seem to get it to work!

[T-Square]

Mavhack--

Welcome to the Forums!

There are many threads that address exactly this kind of ordering application. Basically, the short answer is that you don't necessarily need to copy data from one table to another. With a relational structure, any record that is related to another via a unique ID can draw information from the related record.

For example, if you relate your Order record to your People table by including a People ID field in your Order table, you can place the name, address, phone number, account number, etc. from the person record--without copying the data from one place to another. Every time you copy data into multiple places, you run the risk of having old data pop up when you don't want it!

WRT structure (you didn't bring this up, but I will), you will probably want to add a table for LineItems, so that you can build an invoice on an as-needed basis. The LineItems table (called a join table) will link a single Order to multiple Products. At its most basic, it has fields for OrderID and ProductID; additionally, it likely would have QTY, Price, Discount, among others.

Note that there ARE circumstances where you will want to copy information from one table to another--for example, you will probably copy the Product Price into each LineItem record, so that if you have to raise your prices, your past invoices won't get thrown off. In that situation, you will want to look into LookUp options.

As for interface, you could set up an Order layout so that it has a dropdown list of customers for you to select. Your users select the customer, which puts the CustomerID in the order record and allows all the data from the People table to be displayed (although you might want these fields copied in as well for recordkeeping purposes, using that Lookup feature). Then, you add a Portal to the layout that is based on the LineItems table; on this portal, you can put a dropdown for the LineItems ProductID that selects the Product (from the Products table), fields for quantity and discount, a lookup for price, a calculation for line amount [calculated as Price * Qty * (Discount/100)].

This is somewhat longwinded, and only scratches the surface. I hope it helps.

David

[Mavhack]

I tried the lookup but it doesn't work!

Without going into too much detail I have the following....

The customer databse is for a call centre, dealing with general public not companies, it has over 3000 entries! The product database has 200 models of product, that I can ref through a drop down list. The snag is without it sounding disrespectful is the system needs to be idiot proof, so I need a 1 click button that COPIES the info from, the customer record through to a Order database. WITHOUT it changing on any future reference, by displaying the field from one table to another, if I make a change to the customer info it changes in all the orders for that customer, which can not happen, hence the problem, I had it displaying the info rather than copying, but for recording purposes, this is no good! It needs to have a static history. A Brief breakdown is.

In customer table I have

Customer Name

Customer Address

Customer Postcode

Customer Mobile

Customer Phone

Customer EMail

Account Ref <-- Generated by a simple ref number

These need passing to a table called Upgrades

Where

Customer Name

etc are all called the same.

I tried a script with set field, but that didn't work either.

Loosing the plot somewhere!

Cheers

Simon

[LaRetta]

Loosing the plot somewhere!

Plot: I see no CustomerID (unique serial).

If you have/had one, then an Order would be assigned this CustomerID. When that happens, the customer information would set the like fields in the Order. You need to explain 'what didn't work' when you tried a lookup. Because a Lookup is the way to go (or an auto-enter by calculation) if you wish to plant data in your Orders (which can't change if the Customer information changes).

From your Customer table, you have button 'Create Order.' Attach your New Order script to this button and list a script parameter as: CustomerID. Script would then be:

Freeze Window

Go to Layout [ layout based upon Orders ]

New Record Request

Set Field [ Orders::CustomerID ; Get ( ScriptParameter ) ]

Commit Records/Requests

The customer data fields (in Orders) would look up through the Customers::CustomerID = Orders::CustomerID relationship and plant into the Order fields.

Edited December 28, 2006 by Guest
Added script

[Mavhack]

What I have is a record ID, for the customers, which is just a 1 2 3 4 etc through to 3002 at the mo.

So the way I am reading your script, (haven't tried it yet), is that I pass the record ID through to the orders table, and use the other fields as lookups! If I'm getting the confused just shout, I think my head is fried at the moment.

I could send you an empty copy of the DB if that would make things easier to look at, but in the mean time I'll give the script a try!

CHeers

Simon

[LaRetta]

That's the idea but I wouldn't call it RecordID which has specific meaning (to most Developers). The unique serial should reflect what entity it is identifying as unique - in this case, the customer. Usually CustomerID, ClientID etc works better. But your order would have an identical field (CustomerID) which would be standard field (the field type should match the field type of Customers CustomerID).

Your Order fields, such as Ship Address, would either Auto-Enter as: Customers::ShipAddress or use Lookup. I prefer Auto-Enter because it appears to be faster (in most instances). When you create a new order and insert the CustomerID, the Auto-Enter will fire and fill the Order fields with the Customer data; much easier than attempting to pass all the Customer data via script parameters, variables or globals.

Only use Auto-Enter or Lookup (allow redundant data) if it is data which will change over time. For instance, you want to plant ship address because later the Customer could move; ir if they were Prepaid Terms but later became Net customer. You wouldn't want all their prior Orders to change the ShipAddress to the current one nor change the terms of the invoice.

LaRetta