Relationship products and invoices

💬 General Discussion
1

Good evening, I asked if you can create a relationship between a product at an invoice. From the invoice I can go to the product, but on the contrary, can I know in which invoices a product is contained? I created a relationship between products and invoices and under the products I now have the invoices underwell, but I can only select them in manual. Can you do automatically?
Grazie Massimo

2

Products will be shown under Line items on invoices.

3

I would like the opposite, that invoices were under the products

4

To add Invoice records automatically in Invoice subpanel of products module need to do some customization and for that need to add/update the files as below suggested path:

  1. Change get_subpanel_data attribute value and add function_parameters attribute in layoutdef file as suggested path:

<Your SuiteCRM>/custom/Extension/modules/AOS_Products/Ext/Layoutdefs/aos_products_aos_invoices_1_AOS_Products.php

966×310 30.5 KB 

<?php

// created: 2022-03-02 17:12:34

$layout_defs["AOS_Products"]["subpanel_setup"]['aos_products_aos_invoices_1'] = array (

'order' => 100,

'module' => 'AOS_Invoices',

'subpanel_name' => 'default',

'sort_order' => 'asc',

'sort_by' => 'id',

'title_key' => 'LBL_AOS_PRODUCTS_AOS_INVOICES_1_FROM_AOS_INVOICES_TITLE',

'get_subpanel_data' => 'function:getInvoiceData',

'function_parameters' => array(

'import_function_file' => 'custom/modules/AOS_Products/GetInvoiceData.php',

),

);
  1. <Your SuiteCRM>/custom/modules/AOS_Products/GetInvoiceData.php

1053×166 20.6 KB 

<?php

function getInvoiceData()

{

$productId = $_REQUEST['record'];

$query = "SELECT aos_invoices.* FROM aos_invoices JOIN aos_products_quotes ON aos_products_quotes.parent_id = aos_invoices.id WHERE aos_invoices.deleted = 0 AND aos_products_quotes.deleted = 0 AND aos_products_quotes.product_id = '".$productId."'";

return $query;

}
1 Like
5

Thanks Jessica.
Excuse me, I’m not practical of changes to finish. Could you help me with a more detailed explanation?

Grazie Massimo

6

Hi @jessica1
I´ve used this code to show just the records related to the PRODUCT in the module. It was just the way that I needed. Thanks a lot.

To expand a bit… Im noticing that this uses a module (Table, I guess) named AOS_PRODUCTS_QUOTES and saw that Im missing (pretty sure) a record based in a pure INVOICE that was emitted without being recorded as QUOTE before.

In our business this happens rarely, but could happen. Do you know whats missing to worj the query just based on AOS_INVOICES modules and AOS_PRODUCTS without using the AOS_PRODUCTS_QUOTE as “bridge”?

Thanks a lot, this was for great help!

7

It’s relation table between Invoices & Products module & without using AOS_PRODUCTS_QUOTE table we’re not able to get Products related invoices in subpanel.