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

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

Author

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.

Author

Thank you Chris,

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

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

Best Answer

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):

Author

Great answer!

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.

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.

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

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

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

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

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

TEOS

Hi @Patresh,
thank you for the solution. Right know i'm working on function to filter and deactivate automatically customers with invoice date more than 365days. Can you help me please. Thank you.

Hi Imad: You can define a server action to do this. What is the specific help you are looking for?

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>

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.

Author

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?

Author

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.

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.

I updated my answer.