Copying between tables - scripted and relationship

[buckbuck]

SEE NEXT POST - I HAVE REJIGGED MY QUERY.

I am trying to understand how I might move field data from one TABLE to another. I have an 'Orders' set up which I would like to replicate for producing quotations. It would be most convenient to keep the to TABLES separate. Otherwise I will end up with many orphaned Order numbers, or IDs.

I was thinking I could copy the TABLE, ORDERS, rename it QUOTES, and then on the occasion when the Quote becomes an order I could script a, copy the quote data to a new Order. My thought was to Set Field - target ORDERS field name, calc = PROFORMA field name. But it does not work. I do not have a relationship between the PROFORMA table and the ORDERS table

Is this possible? Or is there a better way to approach this objective?

[buckbuck]

Prelim

As many know, in a business one has Quotes and Orders. Quotes might become Orders but not necessarily. Quotes have many of the same data fields as Orders, however, creating Quotes on the Orders Table is problematic. Depending how much quoting you do you might find your Order database over populated by quotes, dead or otherwise. When you use an auto-entered serial or numbering system then you end up with many redundant Order/Quote numbers.

Consequently I am trying to configure my database such that I can move Quote data from a Quote Table to an Order Table at that point when a Quote becomes an Order.

My thought, through a button on Quote Form, is:

Open New Window

Create New Record: ORDER, layout Order Form

Set Field - target (ORDERS field name), calc (QUOTE field name).

... and Set Field for all the field data to transfer.

It does not work.

I am not sure whether the New Record is referencing the current QUOTE record.

My relationship tables look something like this:

CONTACTS_kp_ContactID - ORDERS_kf_ContactID

ORDERS_kp_OrderID - LINES_kf_OrdersID

PRODUCT_kp_ProductID - LINES_kf_ProductID

CONTACTS_kp_ContactID - QUOTE_kf_ContactID

QUOTE_kp_QuoteID - LINES_QUOTE_kf_QuoteID

PRODUCT_kp_ProductID - LINES_QUOTE_kf_ProductID

Since there is a common denominator in both - CONTACT_kp_ContactID, is this objective possible?

Thanks

Matthew

[Lee Smith]

I merged your 2 topic.

Please do not double post your questions, if you think of additional info that you want to add, just make it a reply to your current topic.

TIA for your cooperation.

Lee