When using custom fields for smart group criteria with relative dates the group does not respect the relative date over time

If you create a smart group using the relative (dynamic, rolling, "this week", etc...) dates for a custom field, once you save the smart group the dates are no longer relative. It creates/updates the group with static dates that match your relative dates at the time you save it.

https://issues.civicrm.org/jira/browse/CRM-20499e

changed the description

added needs:concept approval triaged type:request labels

I've created a fresh pull request here: https://github.com/civicrm/civicrm-core/pull/12824

Tested the pull out on local dev and it didn't fix it. Didn't see any change at all.

Here is a screen cast of the patch working:

relative-custom-date-fields

Got it working now. Thanks!

changed milestone to %5.8

closed

@eileen I don't think this solved the issue. https://github.com/civicrm/civicrm-core/pull/12824#issuecomment-443788453 May we please reopen this?

added prioritised label

reopened

removed prioritised label

added sig:bug label

I know I kinda touched this as part of the changes merged into 5.13 - for a non-custom field. I have no idea if that will have any impact on custom fields though

added Concept approved label

removed needs:concept approval label

changed title from When using custom fields for smart group criteria, relative dates create static dates instead to When using custom fields for smart group criteria with relative dates the group does not respect the relative date over time

changed the description

Just got this problem on 5.10.3 - as seen on the screenshot, the relative dates seems to be updated somehow but are added to the query instead of replaced.

The dates from the previous period are kept so it always gives 0 contact :

Expiry Date is duplicated but it is working as expected for Membership End date (core field) :

I have the same problem for "Previous Calendar Month", "This Calendar Month" and "Next Calendar Month"

We're still having the same issue in 5.11 as well.

EDITED (added some clarifications)

Just did another test and the date seems to be already duplicated upon creation (but obviously the same values at first).

Here are the civicrm_saved_search -> form_values (unserialized) for a custom field (double criteria for the same field) :

Array
(
   
    ... 
   
    [4] => Array
        (
            [0] => custom_25
            [1] => =
            [2] => Array
                (
                    [BETWEEN] => Array
                        (
                            [0] => 20190417000000
                            [1] => 20190516235959
                        )

                )

            [3] => 0
            [4] => 0
        )

    ...

    [relative_dates] => Array
        (
            [custom_25] => ending.month
        )

    ...

)

vs a core field :

Array
(
    ...

    
    [4] => Array
        (
            [0] => member_end_date_low
            [1] => =
            [2] => 20190417000000
            [3] => 0
            [4] => 0
        )

    [5] => Array
        (
            [0] => member_end_date_high
            [1] => =
            [2] => 20190516235959
            [3] => 0
            [4] => 0
        )

    ...    

    [relative_dates] => Array
        (
            [member_end] => ending.month
        )

    ...

)

It might be related to the way CRM_Contact_BAO_Query::dateQueryBuilder seems to deal with {fieldname}_low and {fieldName}_high

mentioned in commit 1f61a7b1

mentioned in commit bc88cec1

mentioned in commit 8392a043

mentioned in commit c0de5da7

I tackled this - see https://github.com/civicrm/civicrm-core/pull/14625

Note I would not expect that to apply to older versions of Civi as I did quite a bit of preparatory leg work to get it working

We've merged a fix for this into 5.16 - unfortunately I don't think it will fix previously saved groups :-( - ie we don't have an upgrade script in the fix

mentioned in commit 7f175707

mentioned in commit af89853e

@eileen @jasonobrown I think this has been better fixed now following the datepicker conversion work in 5.20 should we close this now?

It's working correctly for us now, so I'm good with closing it. Thanks!

closed