3

In a SearchKit query I'm looking to add a column that tells me the number of days from today to/from a date field. Basically datediff() from today.

  • If the date is in the future, say tomorrow, the column would show 1 (positive).
  • If the date is in the past, say yesterday, the column would show -1 (negative).

I tried the following options in Field Transformation...

  • "Days to Anniversary": doesn't work if the date is more than a year away, and also doesn't work for comparing today to a date in the past (to give the negative number of days).
  • "Days between two dates": there doesn't seem to be a way to set today's date as a comparison option;

I'm able to add a column "Current Date" from the Extras section, but I don't see a way to use it in the Field Transformation.

Appreciate any ideas.

1
  • Clarification: I'm on CiviCRM 5.65.0
    – Justin657
    Oct 4, 2023 at 20:46

2 Answers 2

3

You can use the field transformation and choose days between two dates.

enter image description here

Code:

[
  [
    "SavedSearch",
    "save",
    {
      "records": [
        {
          "name": "testdays",
          "label": "testdays",
          "form_values": null,
          "mapping_id": null,
          "search_custom_id": null,
          "api_entity": "Contact",
          "api_params": {
            "version": 4,
            "select": [
              "id",
              "DATEDIFF(CURDATE(), birth_date) AS DATEDIFF_birth_date_created_date",
              "birth_date"
            ],
            "orderBy": [],
            "where": [
              [
                "contact_type:name",
                "=",
                "Individual"
              ],
              [
                "birth_date",
                "IS NOT EMPTY"
              ]
            ],
            "groupBy": [],
            "join": [],
            "having": []
          },
          "expires_date": null,
          "description": null
        }
      ],
      "match": [
        "name"
      ]
    }
  ]
]
3
  • Interesting. That would seem to do it, except that on my Civi 5.65.0 the "Current Date" column doesn't show up under "Field Transformations" the way it's available in your screenshot. The current date displays in the results, but "Current Date" not selectable as a field under Field Transformations.
    – Justin657
    Oct 4, 2023 at 20:48
  • Nope, it doesn't but i have added it manually. Created a search with dummy fields for date diff, exported the search. While importing changed the params for DATEDIFF() Oct 4, 2023 at 21:16
  • Yes, perfect! Thank you. It's a hack but it works. I manually edited the api_params field directly in the database since I'm on a dev env. Using the SK import tool is much safer.
    – Justin657
    Oct 4, 2023 at 21:26
1

I think it would be a good to raise as an issue on https://lab.civicrm.org/dev/core/-/issues/new to discuss making the current date (and other Extra fields) available for use in Field Transformations.

For now, I think you could do this as a re-write using Smarty in a SearchDisplay with your date field and the current date field. See some details here.

2
  • Thank you. I'd prefer to do the calculation on the backend, which I have manually done with the hack provided by Pradeep. I'll open an issue because this should be more user-friendly.
    – Justin657
    Oct 4, 2023 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.