Merlin Backoffice ® and All-in-one Rewards communicate..

Posted on6 Years ago by

Level: advanced

Modules involved : Merlin Backoffice ®

Is it really worth introducing you to it, the all-in-one-Rewards module from prestaplugins is a staple of the PrestaShop world, having been one of the top 10 best-selling modules for several years on the official marketplace, and arguably the best program for building customer loyalty.

It lets you manage all loyalty programs from a single interface. And for customers, it becomes simple to earn points or remuneration via affiliation, advanced loyalty and sponsorship programs, or even via a Facebook "Like" button...

We're going to start with the most important aspects of our program.

In fact, we'll be rolling this out on Sitolog in the near future, so you can benefit even more from all these advantages.

A Merlin Backoffice user recently asked us if it was possible to display and edit the number of specific points that can be earned by purchasing each product, in order to have a global view. This tutorial answers that question.

Personalization page by product of the all-in-one Rewards module:

Product-specific rewards

We immediately thought of the 5 custom columns that allow non-standard headings to be displayed.

We had just written a tuto on how to use them to display an additional HTML block on your product pages.

However, a little something was missing to be able to apply this technique to all-in-one Rewards data. Indeed, in this module, each product can have several different values for the same property. Like several numbers of points awarded, according to dates, or according to programs.

So we need to be able to display several lines for each product. As many lines as there are possible rewards on that product.

To do this, we need to be able to display several lines for each product.

To achieve this, we had to enhance the customizable column editor with a new parameter, GROUP BY.

Ouch, yes, it's technical. By the way, this article is classified in the "advanced" category. Let's say it right away, GROUP BY is a MySQL language command and we're not going to do a programming course here.

So two cases.

There are two possible scenarios: either you already know how to write SQL queries, including the GROUP BY command, and you'll be able to figure out the whys and wherefores on your own without us needing to explain it, or it's all Chinese to you and you'll be content to apply the settings below to the letter to achieve the same result.

If necessary, review the tutorial above to 1/open the column configurator and 2/add the 5 custom columns to the right-hand list, by dragging them from the left-hand table:

Adding the custom columns

Then right-click to open the custom column settings window:

Sperso column settings

There are three columns to fill in, for each of the headings you wish to display.

In 1/ the table name, without its prefix. It's called rewards_product. If the requested table does not exist in the database, its name will be displayed in red, as above.


In 2/ the name of the item to be displayed, as defined in the base table. This information can be found using PHPMyAdmin, for example. Here's the structure of this module's rewards_product table:

Table viewed in database

The data we're interested in are plugin, type, value and dates.

Finally in 3/, a new column from version 1.0.2 of Merlin Backoffice, we add an additional condition to the GROUP BY string of the query, allowing us to display as many rows per product, as they have associated rewards. This condition is based on the table's unique auto-incremented key (or primary index), in this case id_reward_product.


Please note that in this column, you must respect the syntax: you must include the name of the complete table, with its prefix (it's blurred on the screenshot above), before the name of the key. This gives, if your prefix is the default value ps_: ps_rewards_product.id_reward_product

We validate these settings, which closes the window and takes us back to the table configuration window... Let's take this opportunity to give a more meaningful name to each of our 5 customizable columns:

Edit column titles

We validate, and the product table refreshes, with data from our five columns. We can see here that, for product 65 for example , we have 4 rows corresponding to different rewards created with the all-in-one Rewards module. Multi-line display made possible by group by:

The result in Merlin's product table

So you now have in a single view the ability to see all rewards for all products.

You can also, directly modify the values in these columns. But only on a cell-by-cell basis, there is no (yet) mass modification function on the custom columns.

You can also, directly modify the values in these columns.

You can also use these columns to sort products (right-click "Filter on this column").

You can't, however, create or delete rewards here; you have to do so in the all-in-one Rewards module. You can only modify them.

Attention, if you modify the validity dates of a reward, always make sure that there is no overlapping period between two rewards on the same program for the same product.

If this corresponds to a real need, let us know, it will be possible in the future to carry out mass modifications on this type of custom columns.

1 Comment
  • Cl**** O

    Cl**** O 12/20/2018 Reply

    Merlin Back Office est à l'image de son concepteur professionnel, Intelligent, très efficace, respectueux, disponible, altruiste avec un sens aigu de l'échange (plus vous vous investissez plus vous recevez). Merci Franck pour être ce que vous êtes.
Leave a Comment
Leave a Reply

  • Login
    • £ GBP
    • $ USD