Contoso GbR sells bicycles from its own production through two distribution channels. On the one hand, 4 freelance sales representatives divided into 4 sales areas North, East, West and South look after the bicycle dealers located in these areas and receive a fixed sales commission for this, on the other hand, the bicycles are sold directly to the end consumer via the company’s own web store. For a medium-sized bicycle manufacturer, this should be a realistic scenario. The exciting question now is how do we solve this with JTL-Wawi, so that the sales representatives do not have to laboriously maintain all sales in an Excel table and at the end of the month sales statistics and commission statements can be created quasi at the push of a button. An exciting question, I think.

In JTL-Wawi there is no possibility to assign an employee to each customer who can explicitly process this customer. Here, the necessary linking of the corresponding database fields is simply missing. In quotations, orders and invoices, the user who is logged on in each case is stored as the processor. Well our freelance sales representatives should not necessarily have full access to the Wawi and all other information. Unfortunately, there is not yet the possibility to define custom fields like products and categories. In the ant, not all customer fields can be used in the invoice data export. So what remains?

Custom export with SQL query

One possibility is to bypass the ant for the export and to solve a direct export via SQL query. The advantages are obvious. I can store the sales employee as an attribute with the customer and thus do not have to create a new user. The user who is actually processing the operation always appears as the processor. The sales territories can be created as customer categories or customer groups, although I would prefer the customer category field here. The whole thing has to be done only once when creating a new customer. If the customer registers via the B2B store, the customer must be explicitly activated for the customer group Dealer (therefore the use of the Customer category field) and the necessary assignments, such as Sales employee and Sales area, can be made at the same time. When the customer orders later, regardless of the sales channel, the necessary information is already stored with each order.

For the export of the invoice data, a query is written with the SQL management studio about the necessary database fields, with which at the end of the month an export of the invoice data as *.csv file can take place. From my point of view the best solution.

A separate account for each sales employee

The second option is to create an account in Wawi for each sales employee and simply hide it from the employee. This way I can now use the JTL ant for the export, but now I face a new problem. For each new order, whether imported via webshop comparison or created via instant order, I have to manually change the agent to the stored sales employee after creating the order. This costs a lot of time in the long run and is also cumbersome and error-prone. The bottom line is that I find this solution very unsatisfactory.

Conclusion

Sometimes it’s the little things that make life easier. The reason why I brought up this topic in the first place is very simple. The data is then to be analyzed using a spreadsheet. For this purpose, the use of a pivot table is a natural choice. How to create such a pivot table with LibreOffice Calc afterwards, I will show in the next days. To get sample data in form of a csv file I wanted to create it with my sample installation of JTL-Wawi. When playing through the possibilities, solution path one clearly prevailed for me. Of course, a lot of SQL knowledge is required here, but the bottom line is that the time saved is worth it. A detailed documentation will follow secured.