Transfer data in tables

[Evert]

Hi

There are probably very easy solutions for this, but being new i dont have a clue myself.
I set up a quotation from and append the required products to it. If the customer accept the offer, I want to post the details into a invoice form straight away. I want to get the possibility to alter everything, but I shouldn't have to amend anything manualy.

I presume the products from the quotation_items need to be transferred to the invoice_items and the quotation number needs to change in a invoice number, but obviously they need to be unique.

Thanks for any help!

Evert vink

[Mario Prieto]

Re: Transfer data in tables

If I'm not mistaken, what you want to do is the following:
Have a "Quote" form that shows products and price.
if the forms is accepted then form "Invoice" will show with the products on the "quote" form.

Both forms should be able to modify.

Solution in Pseudo code:
Create table products
Create table quote
Create table quote_details
Create table Invoice
Create table invoice_details


Show quote form with a new ID
Add products to quote_details table using the quote form
Accept Quote form
Open Invoice form --Should create a new entry in the Invoice table with an ID
Invoice form should have a button that will allow you to enter a quote Id and should be able to pull the records from the quote_details based on that Id.

Hope this gives you an idea.

[Mike Wilson]

Re: Transfer data in tables

Evert,
Or you could have one field that identifies it is a quote, or as an invoice, and not have to transfer anything. Just a thought.

Mike W

[Evert]

Re: Transfer data in tables

Hi,

Thanks a lot for your considerations! Indeed, I created the following tables:

Customers
Products
Quote_Items
Quote_Header
Invoice_Items
Invoice_Header

And the forms

customers (to enter customer details)
products (to enter product details)
Quotation (to append a customer -in the header part- and to append products in the subform)
Invoice (to append a customer -in the header part- and to append products in the subform)

However I sometimes want to copy all the quotation information from the quotation form straight into the invoice form. So I thought about a button on the quotation form, enableling me to 'push' the information through to the invoice form. (May be somthing like posting the info?)

I�m wondering if a code or genie is available for this requirement. Every hint is welcome!!

Evert

[MoGrace]

Re: Transfer data in tables

Hi Evert,
Unless you have a need to save the original quote after converting it to an invoice, the same quote line items can be used in your invoice by setting referential integrity. All you need to do is get the next invoice number and change the quote id to that number and the line items will update. Then you can show the converted quote in a different form as an invoice. You might also want to have a flag that will differentiate between a quote and an invoice for filtering in reports.

[Bob Houle]

Re: Transfer data in tables

What you're trying to do is exactly what we do daily... create workorders (Jobs) from quotes.

A button press 'pulls' information from the quote form and places it in a new workorder.

I've included a txt file that:
- creates some temp variables and reads the quote info
- creates a new record in our workorder system
- places the temp variable information into the proper fields

Note: disregard the password area (this just lets the system know who created the workorder)

HTH,
--Bob

[Evert]

Re: Transfer data in tables

Hi Bob,

Indeed, this is exactly what i was looking after. Thanks a lot!!

However, I'm a real beginner so these scrips contain some difficult stuff. I wonder if you could explain me something.

I'm not in for a password, so actually I can erase that part anyway. I'm not to sure which part of the next bit I have to use to open my invoice form:

tbl2 = table.open("labmat")
indx = tbl2.index_primary_put("PASSDEX")
tbl2.fetch_find(pword)
fld = table.field_get("labmat->Empl_Name")
gName = alltrim(fld.value_get())
tbl2.close()

I presume I have to change tbl2 = table.open("labmat") into tbl2 = table.open("invoice"), is that right??
I'm wondering what the 'passdex' contains and if I just can erase it?
Actually, do I need anything of tbl2??

I also dont really no where this part point to

line1 = Main:line1.value
line2 = Main:line2.value
line3 = Main:line3.value
line4 = Main:line4.value
line5 = Main:line5.value

If you could give me some explanations I would be very delighted!

Thanks a lot

Evert

[Mike Wilson]

Re: Transfer data in tables

Evert,
Bob's code is very specific, although well written, I have a tough time deciphering it to convert to what you describe. I am working on an example that I believe will hopefully be more understandable for you, and that you might more be able to convert to your scenerio. Pease be patient.

Mike W

[Evert]

Re: Transfer data in tables

Hi Mike,

That is so great!
I'm really pleased you are taking the trouble to help me out of this. Maybe a can just explain you a bit better what I'm after:

I've got the tables:

Quote_Item:
- Quote_Number
- Product_Id
- Price
- Quantity
- Extension

Quote_Header:
- Quote_Number
- Customer_Id
- Date
- Sales_Tax
- Discount
- Expiration_Date

Invoice_Items
- Invoice_Number
- Product_Id
- Price
- Quantity
- Extension

Invoice_Header
- Invoice_Number
- Customer_Id
- Date
- Sales_Tax
- Discount

This tables are brought together in a quotation form (containing quotation items and quotation header) and in an invoice form (containing the invoice items and the invoice header)

I actually want a button on the quotation form, which post everything the quotation form contains straight onto an invoice form. However, I still want to be able to create an invoice without using the quotation form at all.

Once again if you could provide me anything near to this, I would be so delighted!

Thanks

Evert

[Bob Houle]

Re: Transfer data in tables

Evert,

I've re-done the sample to simplify it.

I have a button on my quote form called: Main (in sample file)

When pressed, it:

- Creates some temporary variables
- Stores quote information to these variables
- Opens an workorder form (invoice)
- Dumps the information from the variables into the proper fields
- Closes the quote form (since I've finished with it).

HTH,
--Bob

[Mike Wilson]

Re: Transfer data in tables

Evert,
Between Bob's and the attached, I'm thinking you should be able to get what you need. The codes between Bob's and mine do about the same thing a little differently. The field names are different but the structure I believe are similar and you shoud be able to change mine for yours and get the pattern of things to add the other fields that you have that are not in my example

Good luck and don't hesitate to ask for help.

Mike W

[Evert]

Re: Transfer data in tables

Hi Mike, Bob,

This is really stunning!! You both helped me so much, I now only have to work out the the code. But Im really helped and would like to give some inteligence back so in the future hope to be able to help other people myself.

Regards,
Evert