To create a support ticket, start a search

Products Grid: Adding a column 'nb of sales for 2021'

Target

We would like to add a column displaying the number of sales for a specific year, here 2021

We will then be able to:

- use filters in the grids to find the most sold products and eventually remove unsold products

- export quickly the products grid including this field to work on an Excel spreadsheet

 

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?: nb_year_2021

 

Sc creates the field, you now need to populate the grid with

Field name: Year_2021
Table: Another table
Type: just displayed
Refresh combinations: No

 

From the Advanced Properties panel on the right handside:

- select the menu SQLSelect and enter:

return ',COALESCE((SELECT SUM(od.product_quantity) AS nb
            FROM `'._DB_PREFIX_.'order_detail` od
            INNER JOIN `'._DB_PREFIX_.'orders` o ON (od.id_order = o.id_order)
            WHERE od.product_id = p.id_product
            AND o.valid=1
            AND o.current_state IN (4,5)
            AND o.date_add >= ("2021-01-01 00:00:00")
            AND o.date_add <= ("2021-12-31 00:00:00")
            LIMIT 1),0) AS nb_year_2021';

 

Here we look for valid orders with a status 4 or 5 (refer to PrestaShop > Orders > Status) for year 2019.

 

Exit the editing window.

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

 




Related articles