Yardım

0

how to set a filter for customers who have at least 1 sales order with order date in this year or last 365 days with a value greater than £0

Avatar
Lampfix

I would like to set a scenario in Marketing Automation and would like to know what fields to use for the following criteria:


- Target: Partner

-Filter - has a sales order confirmed this year (2020) or in the last 365 days, with untaxed total with a value greater than £0

3 Yorumlar
Avatar
Vazgeç
Avatar
Lampfix
-

Thanks for this Chris. Can you help me also find sales orders confirmed in the last 365 days? This kind of filter would then allow a rolling year in automation.

Avatar
Lampfix
-

Thank you Chris,

So you are saying I need to create a new field and a new extension view with that code?


Avatar
Chris TRINGHAM
-

Yes, that's the idea. I have added more details (see below).

14 Yanıtlar
2
Avatar
Paresh Wagh
En iyi cevap

You will need to add a custom related field (sale_order_ids.date_order) to the res.partner model. This field will then be available in the filter and you will be able to select a date range.

EDIT:

Added screenshot as requested.

Custom Field definition:



What the Filter will look like on the Contacts screen:



What the results will look like (sample using demo data):


8 Yorumlar
Avatar
Vazgeç
Avatar
Lampfix
-

Great answer!

Avatar
Chris TRINGHAM
-

Hi Paresh,

It is a good solution, but to help anyone who wants to use it I think this is going to select both quotations and confirmed sales orders in the date range. It should be easy to add selection of confirmed sales orders only to the user-defined filter.

Avatar
Paresh Wagh
-

Nice catch Chris. I didn't see the "confirmed sales order" part of the requirement. You're right, this will select both quotations and sales orders.

Since a "related field" was used for this, the domain field is not available in the configuration screen of the field definition. Also tried adding a second filter field for the order state but using both criteria to filter the partners does not work as expected and seems to use an OR instead of an AND condition. Looks like a custom filter will need to be developed for this requirement.

Avatar
Paresh Wagh
-

Looked into this a little more. It seems like a customization is required to handle the "confirmed orders only" part of the requirement.

Avatar
Chris TRINGHAM
-

Yes, I think it can be done by adding a filter to the search view..

Avatar
Chris TRINGHAM
-

Here's how to setup a custom filter for this: https://odootricks.tips/odoo-date-filters/#select

Avatar
Peat Biby
-

hi! This is absolutely wonderful, thanks!! How would I be able to sort by this related field? That's the only thing I can't figure out. Thanks again! Peat

Avatar
Paresh Wagh
-

Hi Peat: If you make the related field a stored field you should be able to sort on it.

1
Avatar
Chris TRINGHAM
En iyi cevap

Building on what Paresh has suggested, you can use the field he described and create an Extension View like this:

<?xml version="1.0"?>
<xpath expr="//filter[@name='inactive']" position="before">
      <filter string="Orders this year" name="orders"
        domain="[('x_sales_order_dates','&lt;=', time.strftime('%%Y-12-31')),
                 ('x_sales_order_dates','&gt;=',time.strftime('%%Y-01-01')),
                 ('sale_order_ids.state', 'not in', ('draft', 'sent', 'cancel'))]"/>
    <separator/>
</xpath>

This will display a filter like this:

To select orders in the last 12 months, you can have another filter with a different domain:

 <filter string="Orders last 12 months" name="orders_12m" 
domain="[('x_sales_order_dates','&gt;=',(context_today() + relativedelta(months=-12, weekday=0)).strftime('%Y-%m-%d')),
('sale_order_ids.state', 'not in', ('draft', 'sent', 'cancel'))]"/>

======

If you want to filter this in Marketing Automation you can

1. Create a new boolean field x_recent_sales_orders

2. Create an automated action


Enable Developer Mode and navigate to Settings / Technical / (Automation) / Automated Actions:


for rec in records:
  rec['x_recent_sales_orders'] = False
  if rec.x_sales_order_dates:
    if rec.x_sales_order_dates > (datetime.datetime.today() - datetime.timedelta(days=5)):
      rec['x_recent_sales_orders'] = True
What's missing here is the automatic update to reset this flag.

To do it properly, I think you'd need a developer to setup a Scheduled Action to run this code daily (or weekly).  

As a workaround, it is also possible to create a Contextual Action and then you could manually run this (by selecting all orders).

After creating this field and adding the Automated Action you can use it as a filter in Marketing Automation.
1 Yorum
Avatar
Vazgeç
Avatar
Lampfix
-

Thank you Chris, I really appreciate your help on this.

Could you update the code to exclude 'zero' value sales orders, and also could you show me a screen shot of the Scheduled Action to set up for this?

0
Avatar
Lampfix
En iyi cevap

Thank you Chris that works incredibly well in the Customers view.

Could you give some guidance as to when setting and finding this filter in an Automation?  Whilst I can find the filter in Customers, I cannot find it here:




I would like to edit this post once it is done to make it clear for all users - thanks for your help.

3 Yorumlar
Avatar
Vazgeç
Avatar
Chris TRINGHAM
-

Odoo has a "simplified" version of the filter in various places and that only allows specific dates to be entered. There is a workaround for this as described here: https://odootricks.tips/domain-in-odoo/#problem but it seems that doesn't work for Marketing Automation.

Another solution is to create a boolean field that will identify customers who have placed orders recently (e.g. last 12 months) and update this every day using a Scheduled Action (then you can easily add a filter).

I have some general information here: https://odootricks.tips/server-actions and I'll try to add a more specific example later.

Avatar
Chris TRINGHAM
-

I updated my answer.

Avatar
Lampfix
-

From what you say I realised I don't need to use Marketing Automation to sort my database contacts and their opportunities. I can use scheduled actions instead.