Copying record into new database

[Obbe]

Hi,

I'm having a headache. The problem I have is that I'm making a database for my company. We wish to have two separate databases; offers and invoices. Both of these contains a related table that is designed the same way for both of them (the invoice database is intact a copy of the offers skeleton); a custom product (which is defined in a new table). I want to easily copy values from my offers database, into the invoices.

Problem is, since the custom products are related, I can't find an easy way to copy values into the correct fields.

Example: A customer ask for an offer for custom products A, B and C. We create a new record in the offers table, create a new record for each of the custom products in the custom products table, and they both have the linking ID. (Offers now relates to the three products, in Database 1)

The customer is satisfied with the offer and wishes to make an order. We then want to easily make a new record in the invoice table (in a Database 2), copy some common values, plus linking the invoice to a copy of the three different products, in Database 2. How would I easily copy these values? (There's about 100 of them)

I'm stuck. Did anyone understand my problem? I guess I can relate the invoices to Database 1, and just create copies and relate them by a different ID (invoice number), but I'd rather have them separated. Is it possible?

Thanks for your time and patience!

[haensz]

Do you store the Offer ID (or whatever your primary field on offers table is) on the Invoice?

If so, you could create the new invoice record and tag it with it's Offer ID (store this Offer ID as a variable $offer). Now you have a relationship to pull the customer #, address, date, payment info, etc -- all of the basic info I'm assuming that your offer record would contain.

Then go to the table you mentioned that links the line items/products from your Offer ID. It sounded like this table was unique based on the Offer ID and the Product/Line Item. Perform a find based on the $offer variable you stored. If I'm reading your scenario correctly, you are now viewing a list of the items included on the offer in question

Finally, move over to your 2nd database where you want the invoice line items to be created. Import the records from the table above.

You'll probably need to tag these newly imported records with the new invoice # you created. But as long as you store that new invoice # as a variable up front when you create it, you should be good.

[comment]

Why do you need to do so much copying and duplication of the same data? Couldn't you simply mark an offer as Invoice?

See also:

http://fmforums.com/forum/topic/80957-serials-in-multi-user-environment/page__view__findpost__p__376133

[Obbe]

Thanks for your replies.

Do you store the Offer ID (or whatever your primary field on offers table is) on the Invoice?

If so, you could create the new invoice record and tag it with it's Offer ID (store this Offer ID as a variable $offer). Now you have a relationship to pull the customer #, address, date, payment info, etc -- all of the basic info I'm assuming that your offer record would contain.

Then go to the table you mentioned that links the line items/products from your Offer ID. It sounded like this table was unique based on the Offer ID and the Product/Line Item. Perform a find based on the $offer variable you stored. If I'm reading your scenario correctly, you are now viewing a list of the items included on the offer in question

Finally, move over to your 2nd database where you want the invoice line items to be created. Import the records from the table above.

You'll probably need to tag these newly imported records with the new invoice # you created. But as long as you store that new invoice # as a variable up front when you create it, you should be good.

This sounds like an interesting option. I tried a similar approach yesterday (using the Import Data-function in scripting), but I didn't know how to import only those records with a specific value. Can you please specify what you mean with "Import the records from the table above."? Do you mean the nice way where you just pair fields together, or do you mean manually copying values to each field?

Why do you need to do so much copying and duplication of the same data? Couldn't you simply mark an offer as Invoice?

See also:

http://fmforums.com/...post__p__376133

No, I'm afraid not. It's been requested to keep them in separate databases, to make them stand-alone applications.

[comment]

It's been requested to keep them in separate databases, to make them stand-alone applications.

But they are not stand-alone applications any way you look at it.

[Obbe]

But they are not stand-alone applications any way you look at it.

They are stand-alone in the sense that I can delete one database, but the other will remain functional.

[Obbe]

I think I got this to work.

1. Create as many records as you need to import custom products. (I.e. how many related records your offer has)
   
2. Assign a field copy = 1 to each of these records. Also assign a field number to each record. This should be the number of records for that invoice currently existing, +1. (So they will all be numbered 1, 2, 3, etc) Make sure that you assign the same invoice number as well, so that they are related.
   
3. Make sure that the offers custom products are numbered 1, 2, 3, etc, as well. Do this by assigning the next number (count +1) each time one is created. Give them a field copy, constant to 1.
   
4. Import the data from the offerings, matching copy to copy, and number to number.
   
5. Set the field copy in the invoices to 0. (This will prevent them from updating when you copy another offering to another invoice)
   
6. Pray that the user won't delete any custom product, but the last one, as this will get the numbering out of order.
   

Much easier if you can just keep them in the same database and copy records, assigning a new invoice-number.

[haensz]

Obbe -- sorry I disappeared on you for a while. Sounds like you have your issue in hand. But for future reference, I wanted to get back to you on your question on Scripted Imports.

Just remember that the import will bring in all records from the source table's current found set. So prior to your scripted import, make sure that you've scripted that source layout to display only the records you want to import. A few ways to handle this, basic way would be to:

1. Set Error Capture ON *** Very important step here! ***

2. Go to desired layout

3. Perform Find

4 Go to Layout (original layout)

5. Import away...

You may want to consider setting a variable (we'll say $found) to capture the found count after step 3. That way you can wrap an IF statement around the import and any related steps after it. If there's nothing to import, it might be good if your script is smart enough to skip that portion of your script.