Moving Datasets From Table to Table

[datalink]

Hey Guys,

I've got a fairly standard system that has the following tables:

Orders

OrderLineItems

Quotes

QuoteLineItems

In a script intended to convert a quote to an order I need to duplicate the QuoteLineItem records in the OrderLineItems table assigning them the new order number (probably held as a script variable).

What do people think is the most elegant way to do this. I'm sort of up in the air about using an import step or looping through each of the quote line items and using set fields commands to create the records in the orderlines table.

I'm just wondering what people feel represents "best practice" in this sort of situation.

Edited November 1, 2006 by Guest

[mz123]

What if you just had a LineItem table that had a status so it would be automatically set to "quote" and once its been set as an order, the status field to "order"?

You could then have a quote# field and an order# field, if you really need two separate numbers. Otherwise, you could use the same# for both.

Would that work?

[Søren Dyhr]

What do people think is the most elegant way to do this. I'm sort of up in the air about using an import step or looping through each of the quote line items and using set fields commands to create the records in the orderlines table.

Martha, is quite right, every thing you suggests or have come up with so far is relational unhealty - why do you need to store things twice, when all it takes is an attributal change in each relevant record. There is no need to create records what so ever, they're there already - it's not as you header suggest a movement from table to table!

--sd

[datalink]

You both make excellent points. However, in this case I'm not sure that it applies. (The method that you mention is what is currently used to generate invoices comprised of order line items.)

In this case however, there are typically significant deviations between quotations and actual orders. The idea of recreating the data set from QuoteLI to OrderLI is for ease of use for the operators. One click and they convert a quoation to an order to then modify to reflect the final order.

Often operator will want to look back at the quotation history to get a sense of how the project eveloved from quotation to quotation to final order. So if there is only one record that simply undergoes an attibute change, but then gets altered the quotation history is damaged.

So, unless I've missed something, my goal really is to recreate the dataset in a new table. Any thoughts on the best practice for that?

It's currently set to import. I'm just wondering if that's what people prefer.

Edited November 2, 2006 by Guest

[Ender]

Those issues can be dealt with in a single table that does both, but it does take a little work. On the other hand, using separate tables for Quotes and Invoices in some ways is a simpler structure. You just have to weigh any possible difficulties of needing to add an item to both or to keep both updated simultaneously for any reason. If these aren't a concern, then you might try the separate table design.

If you go that route, I'd recommend an import. You just have to remember to check the import order any time fields get deleted.

If the items being added to the destination table are serialized sequentially, then a loop could be used that generates a record for each number in the sequence, populating the key and initiating a Lookup for data in the other fields. However, I don't think this would be appropriate in this case, as there could be gaps or Line Items from other Quotes in any serialed Line Item set.

[normanicus]

I do this differently in that I have a quotes table the data from which is used in transactions that later get invoiced. The data transfer problem is the same except for one point - invoices are documents with legal implications. I am very careful to ensure that there is no way invoice information can get inadvertently updated.

Anyway, here is how I transfer the data:

There are three tables involved:

Quotes

Transactions

Sales Items

Both 'Quotes' and 'Transactions' use the 'Sales Items' tables for their line items.

In 'Quotes' I have a button that runs a script to create a transaction from a quote. This runs a normal 'New Transaction' process amended to use the quote data. I do this by passing the quote record ID to the transaction and then, from in the transaction I put the Transaction ID into each quote item (via a relationship Transactions::Quote ID > Quotes::Quote ID).

You could do this for invoices and invoice items, though, as I've said, I try to isolate invoice data from other information as much as possible. My method also has the advantage that a quote can form the basis of a transaction that can later be amended. This has the knock on advantage that a 'Duplicate Quote' script can then form the basis of multiple transactions that may have slight differences.

Hope this all makes sense,

Norman