Identifying Product Qualifying for Discounts and Tracking Last Used Date

Dear All,

One of our customers has a discount integration from NAV to DW based on user group and product group. Each customer has different discount percentages based on the product group.

Example:

Discount 1: C-0130 - Maison VII-XII Collection Group - Buy Qty1 get 30%
Discount 2: C-0130 - Maison VII-XII Collection Group - Buy Qty60 get 40%
Discount 3: C-0130 - Fairy Sheers Collection Group - Buy Qty1 get 30%
Discount 4: C-0130 - Fairy Sheers Collection Group - Buy Qty60 get 40%

As per their requirement, when a user buys one of the products from Discount 1 for the first time, the user will get 30%. Then NAV has customization to update the discount percentage for the second order afterward for that user. When the user buys a second order with the same discount, the user will get another promotion. Based on their requirement, they can't combine the discount configuration and must separate these discount ways.

Regarding the condition in the product discount list, one customer could have more than 30 rows of discounts, and they can only identify each discount by their customer number, which might have similar discount percentages. They have difficulty identifying the discount, thus they inquire: "How can we determine which product qualifies for the discount?" Additionally, they would like to know: "Does the product discount list have any field to show what is their last used date?" As I researched, the product group and last used date information can't be displayed on the discount list page.

Could you please suggest any methods for how we can find a way?

Best Regards,

Thuzar

Hi Thuzar,

It would seem that DW's discount engine does not support this logic very well. The sheer fact that you need about 30 discount configurations per customer is a sign that you'll run into issues.

Have you considered using Live Carts with Discounts handled in NAV instead? It would simplify things a lot, as I would assume they alread have that logic in NAV, so they should have some reporting associated with it as well.

That said, I can see from your screenshot that they are all Product Discounts. In this case it's relatively simple, because each discount orderline has a ParentOrderlineId which will tell you what product (orderline) triggered it. It's also available in Tags (for the templates) if you need to represent them visually. In fact, Swift template should already show you the proper discounted information accurately. It may not display the discount name, id or the percentage, but display the calculated amount.

Regarding the question "Does the product list discount list have any field to show what is their last used date?", there isn't that information readily available, but should be simple to query it if you need it. You need to join the EcomDiscounts with the EcomOrderlines where the discount id is being used, but filter by orders that have been completed and only get the most recent one per order id.

Something like this should be able to get you that (untested)

SELECTeo.OrderlineDiscountId AS DiscountId, MAX(eo.OrderDate) AS LastOrderDate
FROM EcomOrders eo
JOIN EcomOrderlines eol ON eo.OrderId = eol.OrderlineOrderId
WHERE eo.OrderCompleted = 1
GROUP BY eol.OrderlineDiscountId

Best Regards,

Nuno Aguiar

Developer forum

Identifying Product Qualifying for Discounts and Tracking Last Used Date

Replies