Products Grid: Adding a field from a table outside the products table (editable)

Target

We would like to add the field 'specific action'' store in ps_specific_action table (by default this table does not exist in Prestashop).

We will then be able to:

- use the filters in the grids to optimize the selection of products
- mass-edit this field using Store Commander's copy/paste option
- rapidly export the products grid with this field

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?: specific action (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: specific action
Table: another table
Refresh combinations: No
Type: multiple choice

Taking the example of table ps_specific_action includes the following fields:

id_specific_action, id_product, specific_action

From the Advanced Properties panel on the right handside:

- select the menu List of choice and enter:

return array(0=>_l('Aucune'), 1=>_l('Colis fragile, emballer avec carton A1'), 2=>_l('Colis long, emballer avec carton B3'));

- select the menu SQL Select and enter:​​​​​​​

return ' ,sact.specific_action';

- select the menu SQL Left Join and enter:​​​​​​​​​​​​​​

return " LEFT JOIN "._DB_PREFIX_."specific_action sact ON (sact.id_product= p.id_product)";

- select the menu PHP onAfterUpdateSQL and enter:

if (isset($_POST["specific_action"])) {
    $sql = "SELECT * FROM " . _DB_PREFIX_ . "specific_action WHERE id_product=" . (int)$idproduct;
    $res = Db::getInstance()->ExecuteS($sql);
    $specific_action = (int)Tools::getValue('specific_action', 0);
    if (count($res)) {
        if ($specific_action) {
            $sql = "UPDATE " . _DB_PREFIX_ . "specific_action SET specific_action=" . (int)$specific_action . "  WHERE id_product=" . (int)$idproduct;
            Db::getInstance()->Execute($sql);
        } else {
            $sql = "DELETE FROM " . _DB_PREFIX_ . "specific_action WHERE id_product=" . (int)$idproduct;
            Db::getInstance()->Execute($sql);
        }
    } else {
        $sql = "INSERT INTO " . _DB_PREFIX_ . "specific_action (id_product,specific_action) VALUES (" . (int)$idproduct . "," . (int)$specific_action . ")";
        Db::getInstance()->Execute($sql);
    }
}

Exit the editing window and add the field to your grids.
​​​​​​​