1 (edited by haciamirumbo 2021-07-13 12:26:55)

Topic: From estimate to invoice convertion is possible with SQL query?

Hello, Let's say I have the Tables customer, company, products...then I have the tables estimate header, estimate details, invoice header, invoice details, all with the same fields. How could I convert from a button on the estimate form to an invoice? that way, I do not have to write everything again from the estimate to the new invoice. Is that possible with an SQL query instead to write a script? I do not know how to write scripts. Thanks for your help.

Re: From estimate to invoice convertion is possible with SQL query?

Hi Haciamirumbo,
If your estimates (headers and lines) and your invoices (headers and lines) all contain the same fields, one option (and the most straightforward without using either a script or having to re-enter data) would be to just use 2 tables - transactions (header and lines) and then use a transaction type to indicate what type of transaction it is (and you're not limited to just estimates and headers - you can use other transactions types as well).
All of this can be done as part of 'standard' MVD without any script.
The transaction can then be changed from 'estimate' to 'invoice' simply by changing the transaction type.
Please see the attached as an example of how it could work (but there are other options).
Regards,
Derek.

Post's attachments

Attachment icon haciam.zip 342 kb, 177 downloads since 2021-07-13 

Re: From estimate to invoice convertion is possible with SQL query?

Hello Derek, Thank you very much for your quick response and good solution but I will use this solution as a paid or not paid invoice. But in my case, I am a plumber and the customer wants an estimate for replacing the sink or vanity but when I generate the order(Identical as the estimate with your solution) he says I am going to replace also the toilet so now I have an estimate with a sink and an order with a sink and a toilet but when I am almost done he add the faucet then I have an estimate with sink, order with sink, toilet, and an invoice with sink, toilet, and faucet. That was the point to have different tables that could keep the original data and the others with the modifications in case he/she wants to check the different steps from a project and everything clear is good to avoid misunderstanding. Or tell me how to keep with this solution the estimate with the original products and the invoice with other additions or modifications. Thank you very very much for your help.

4 (edited by derek 2021-07-15 20:20:07)

Re: From estimate to invoice convertion is possible with SQL query?

Hi,
Rather than use a status indicator, another option might be to hold an estimate_date, order_date, invoice_date etc as new fields on the headers table.  Then the presence of a tick and valid date against any of these would indicate what stage the job was at. 
Additionally, add a line_date to the lines table to show when a line was added and compare it to the dates in the headers table to see whether it has been added to the estimate, to the order, to the invoice etc. 
This should enable you to see a sort of 'history' as to what went on.
In the attached example, I've used a 'calculated field' as a simple explanation as to what stage a line was added;  it's a 'nice to have' but not strictly necessary as you can work it out from the dates alone.
It's an approach that seems to give a fair amount of flexibility (you don't have to start with an estimate but can go straight to an order etc).
But as I said earlier, there's lots of other options but I'm minded to keep it to standard MVD rather than using a script.
Regards,
Derek.

Post's attachments

Attachment icon haciam a.zip 344.4 kb, 179 downloads since 2021-07-15 

5 (edited by haciamirumbo 2021-07-16 13:50:12)

Re: From estimate to invoice convertion is possible with SQL query?

Thank you very much Derek for your great help and for the tips and tricks of MVD. As a newbie, I will try to add some of my thoughts(as an end-user, not as a programmer) to the community.