Combinations Grid: Adding a field from a table outside the products table

Target

We would like to add the field 'BIO Label' which will allow to differentiate this type of products in the multi store combination grid .

For this example, this field is located in ps_label table id_product_attribute ; label_bio (0/1) ; id_shop ).

We will then be able to:

• use the filters in the grids to optimize the selection of combinations
• mass-edit this field using Store Commander's copy/paste option

Setup

To add the field to the list of available fields for your products grids, click on the in the 'Add a field' panel and enter the following information:

What is the field ID?: label_bio (should be exactly the name as it is in the database).

SC creates the field, you now need to populate the grid with:

Field name: BIO Label
Table: -
Type: multiple choices

From the Advanced Properties panel on the right handside:

- select the menu SQLSelect and enter:

return ' ,lab.label_bio';

- select the menu SQL Left Join and enter:

return ' LEFT JOIN '._DB_PREFIX_.'label lab ON (lab.id_product_attribute = pas.id_product_attribute AND lab.id_shop=pas.id_shop) ';

- select the menu PHP definition and enter:

$product_attr['label_bio'] = $product_attr_by_shop['label_bio'];

- select the menu PHP onAfterUpdateSQL and enter:

if (isset($_POST['label_bio'])){
    $sql = "UPDATE "._DB_PREFIX_."label 
            SET label_bio = ".(int)Tools::getValue('label_bio',0)." 
            WHERE id_shop = ".(int)$id_shop." 
            AND id_product_attribute=".(int)$id_product_attribute;
    Db::getInstance()->Execute($sql);
}

- to add a yes/no choice, select the menu List of choices and enter:

return array(1=>_l('Yes'), 0=>_l('No'));

Exit the editing window.

The new field is now present in the list of available fields and you can add it to your combination grid.

To use the list of choice, select 'Type: multiple choice (Type A) for this field.