Trying to copy custom fields from quote to invoice

Hi everyone,

I am trying to implement the code snippet from @jimshell which I found here

    $this->load->model('custom_fields/mdl_custom_fields');
    $this->load->model('custom_fields/mdl_quote_custom');
    $this->load->model('custom_fields/mdl_invoice_custom');

    $quote_customs = $this->mdl_quote_custom->where('quote_id', $this->input->post('quote_id'))->get();

    if ($quote_customs->num_rows()) {
        $quote_customs = $quote_customs->row();
        unset($quote_customs->quote_id, $quote_customs->quote_custom_id);

        foreach ($quote_customs as $key => $val) {
            $db_array = array(
                str_replace('quote', 'invoice', $key) => $val
            );
            $this->mdl_invoice_custom->save_custom($invoice_id, $db_array);
        }
    }

I’ve pasted it in “/application/modules/quotes/controllers/ajax.php” around line 378 after the quote_tax_rate for loop.
The browser console throws a 500 error.

Not exactly sure what’s going wrong as my php skills are fairly limited. The loop itself is still working I think but as soon as I add $this->mdl_invoice_custom->save_custom($invoice_id, $db_array); The 500 error gets thrown.

Can anyone help me out?

Without logs its hard to tell.
But please give way more infos about your setup:

  1. InvoicePlane version?
  2. LinuxOS? If yes which version`
  3. PHP version?
  4. Webserver?
  5. ServerLogs?

That would be a good startingpoint to debug it

Invoice Plane 1.5.11
PHP 7.3
MySQL 8.0.21-12
Webserver Apache 2.2

Thanks for pointing me to the logs, forgot about those :see_no_evil:
There seems to be a duplicate entry invoice_id

DEBUG - 2021-02-13 13:34:10 --> File loaded: /var/www/ud08_441/html/invoiceplane/application/modules/custom_fields/models/Mdl_invoice_custom.php
ERROR - 2021-02-13 13:34:10 --> Query error: Duplicate entry '37-7' for key 'ip_invoice_custom.invoice_id' - Invalid query: UPDATE `ip_invoice_custom` SET `invoice_custom_fieldid` = '7'
WHERE `ip_invoice_custom`.`invoice_custom_id` = 102

So I can only explain a little of this error.
In your ip_invoice_custom table there is already a fieldid with the ID of 7
That update command tries to do something with that fieldid of 7 and another field with the if of 37 (probably the invoice_id column) and fails.

I hope you’re doing everything locally on your development PC and not on your live-server.

I don’t know the solution yet, since I don’t know what you want to accomplish

thanks for the explanation.
I am trying to transfer my custom fields from quote to invoice. The same custom fields exist for both quote and invoice.

Ok, this is just a general solution.
Let’s say in your ip_invoice_custom table you have 5 records.
You will want to insert those 5 records in that same table.
Figure out how you recognize those 5 records.
Write down all fields of the table. I usually do an export of the structure, it will help me.

Please - please - please :pray: don’t do this in your live database. First: locally, test-environment, etc.

let’s say your fieldid= 7 is your thing how to recognize those 5 records.

insert into ip_invoice_custom select null, column, column, column from ip_invoice_custom where fieldid=7

Maybe it will not work, because it’s the same table.
Isn’t there a ip_quotes_custom?

insert into ip_quote_custom select null, column, column, column from ip_invoice_custom where ip_invoice_custom.fieldid=7

if you do this in a live environment and something goes wrong, I cannot help.

thank you! Ill try to get your solution to work.

Please - please - please :pray: don’t do this in your live database. First: locally, test-environment, etc.

No worries I am working on a dev-environment :grin:

Still trying to get this to work. I had a closer look at the save function of the ajax.php (“/application/modules/quotes/controllers/ajax.php”)
I thought I could reporpuse the save custom fields block from line 100 to copy my custom fields from quote to invoice.

 $db_array = [];
            $values = [];
            foreach ($this->input->post('custom') as $custom) {
                if (preg_match("/^(.*)\[\]$/i", $custom['name'], $matches)) {
                    $values[$matches[1]][] = $custom['value'];
                } else {
                    $values[$custom['name']] = $custom['value'];
                }
            }

            foreach ($values as $key => $value) {
                preg_match("/^custom\[(.*?)\](?:\[\]|)$/", $key, $matches);
                if ($matches) {
                    $db_array[$matches[1]] = $value;
                }
            }


            $this->load->model('custom_fields/mdl_invoice_custom');
            $result = $this->mdl_invoice_custom->save_custom($invoice_id, $db_array);
            if ($result !== true) {
                $response = [
                    'success' => 0,
                    'validation_errors' => $result,
                ];

                echo json_encode($response);
                exit;
            }

I am now getting the following error:
image
which is this one :

foreach ($this->input->post('custom') as $custom)

I am a bit clueless why this is an invalid argument here when it’s working fine in Line ~100 when saving a quote.

Sorry for the basic questions but I hit another wall here.

Try to var_dump $this->input->post() and let’s see what shows up.
Basically a post usually comes from a form.
If that form doesn’t have a field called ‘custom’, then it wouldn’t be in the post (if that makes sense)

thank you @UnderDog, it helped me understand it a little better and var_dump() helped me immensely.
I managed to copy my custom fields from quote to invoice now. So I’m posting this for anyone who’s trying to achieve the same thing or wants to improve the code :grin:.

I have written the following lines from around line 378 in “/application/modules/quotes/controllers/ajax.php. after the quote_tax_rate foreach loop.

            $this->load->model('custom_fields/mdl_quote_custom');
		    $this->load->model('custom_fields/mdl_quote_custom');
		    $CI = &get_instance();
		    $CI->load->model('custom_fields/mdl_custom_fields');
		    $db_array = $CI->mdl_custom_fields->get_values_for_fields('mdl_quote_custom', $quote->quote_id);
		    $formatted_date = date_from_mysql($db_array['myCustomDate'], true);
            $customCopy = array();
              
		        if (array_key_exists('myCustom1', $db_array)){
		        $customCopy[14] = $db_array['myCustom1'];}
                else {$customCopy[14] = null;} 
                if (array_key_exists('myCustom2', $db_array)){
                $customCopy[13] = $db_array['myCustom2'];}
                else {$customCopy[13] = null;} 
                if (array_key_exists('myCustomDate', $db_array)){
                $customCopy[16] = $formatted_date;}  
                if (array_key_exists('myCustom3', $db_array)){
		        $customCopy[15] = $db_array['myCustom3'];}
                else {$customCopy[15] = null;} 
                if (array_key_exists('myCustom4.', $db_array)){
                $customCopy[25] = $db_array['myCustom4.'];}
                else {$customCopy[25] = null;} 
                $customCopy[26] = null;

		    $this->load->model('custom_fields/mdl_invoice_custom');
		    $result = $this->mdl_invoice_custom->save_custom($invoice_id, $customCopy);

EDIT: I ran into some problems when I didn’t declare all invoice custom fields. The database then changes some values and some of your fields might get deleted. So its important to add them to your array already. Like in the example above $customCopy[26] = null;this field does not exist for quotes but it does for invoices so I just set it to null. If it was a boolean just set it to either 1 or 0 depending on if you want it to be true or false. I also left the order of the array exactly how it prints and didn’t reorder them. I dont think this is important because I think they will get reordered anyway but better be safe.

I know this isn’t the prettiest code and I should check if myCustomFieldX also exists for invoices and then copy it over, but I couldn’t get that to work and this solution works for me.

If anyone wants to implement this themselves, you have to find out the custom_field_id of your custom field. This is NOT the order number you manually give your custom field when creating it. You can do this a couple of ways. Either check directly in your db or copy the following code in “application/modules/invoices/views.php”

<?php
    $fieldValue = $this->mdl_invoices->form_value('custom[' . $custom_field->custom_field_id . ']');
    echo '<script>';
    echo 'console.log('. json_encode( $custom_field ).')';
    echo '</script>';
 ?>

once at line 409 after:

<!-- Custom fields -->
   <?php foreach ($custom_fields as $custom_field): ?>
   <?php if ($custom_field->custom_field_location != 1) {
   continue;
} ?>

and once at line 549 after the (almost) identical code block.

Now if you open up one of your invoices in your browser it will print your custom fields to the console with your custom_field_id (plus some more information).


You need to fit your ids from the invoice to the ones from the quotes.
So in the example above it would be:

  $customCopy = [
			        '3' => $db_array['Liefertermin'],
];

This worked for me. I know it’s not the ideal way but maybe it will help someone.

2 Likes