FHIR API: appointment date range search gives wrong results

[bravegag]

Hello,

I'd like to get the list of appointments using three criteria:
1. location
2. dates e.g. range
3. practitioner (id)

I had a query working well using the first two criteria only e.g.

Code: Select all

https://api.opendental.com/fhir/appointment?location=1,2,3,4,5,6&date=ge2019-06-07&date=le2019-06-08&status=booked

However, when I attempt filtering by practitioner (id) then it comes back with strange results with dates way beyond the range:

Code: Select all

https://api.opendental.com/fhir/appointment?location=1,2,3,4,5,6&date=ge2019-06-07&date=le2019-06-08&status=booked&practitioner=2

I tested this other search variant and it appears to work correctly:

Code: Select all

https://api.opendental.com/fhir/appointment?location=1,2,3,4,5,6&date=2019-06-07&status=booked&practitioner=1

I saw that `practitioner` was a correct search criteria as part of the `appointment` capability statement. Though, It wasn't completely clear how to pass the parameter.

TIA,
Best regards,
Giovanni

[bravegag]

Hello again,

It's actually filtering by practitioner id correctly, the issue is the date range, when I search for the locations and date range only it returns wrong results:

Code: Select all

https://api.opendental.com/fhir/appointment?location=1,2,3,4,5,6&date=ge2019-06-07&date=le2019-06-10&status=booked 

This is an excerpt from the results, see the start and end of the appointment it's completely off the date search range:

Code: Select all

{
  "entry": [
    {
      "fullUrl": "appointment/14",
      "resource": {
        "description": "Ext, Ext",
        "end": "2009-04-30T08:40:00",
        "id": "14",
        "identifier": [
          {
            "type": {
              "text": "Open Dental FK to appointment.AptNum"
            },
            "use": "usual",
            "value": "14"
          }
        ],
        "meta": {
          "lastUpdated": "2009-04-30T20:50:05"
        },
        "minutesDuration": 40,
        "participant": [
          {
            "actor": {
              "display": "John Smith",
              "reference": "Patient/15"
            },
            "required": "required",
            "status": "needsaction",
            "type": [
              {
                "coding": [
                  {
                    "code": "PART",
                    "display": "Participation",
                    "system": "http://hl7.org/fhir/participant-type",
                    "userSelected": false
                  }
                ]
              }
            ]
          },
          {
            "actor": {
              "display": "Brian Albert",
              "reference": "Practitioner/1"
            },
            "required": "required",
            "status": "accepted",
            "type": [
              {
                "coding": [
                  {
                    "code": "PPRF",
                    "display": "primary performer",
                    "system": "http://hl7.org/fhir/participant-type",
                    "userSelected": false
                  }
                ]
              }
            ]
          },
          {
            "actor": {
              "display": "OP-1",
              "reference": "Location/1"
            },
            "required": "required",
            "status": "accepted",
            "type": [
              {
                "coding": [
                  {
                    "code": "PART",
                    "display": "Participation",
                    "system": "http://hl7.org/fhir/participant-type",
                    "userSelected": false
                  }
                ]
              }
            ]
          }
        ],
        "priority": 5,
        "start": "2009-04-30T08:00:00",
        "status": "booked"
      },
      "search": {
        "mode": "match",
        "score": 1
      }
    },

Any ideas what the issue could be?

TIA,
Best regards,
Giovanni

[lukemiletta]

Hello,

I've looked into this issue. In our current implementation for searching for dates, if multiple dates are included, any appointment is selected that fits any of the criteria. For example, with your example date=ge2019-06-07&date=le2019-06-10, it is selecting any appointment with a date greater than 2019-06-07 OR any appointment with a date less than 2019-06-10. That is the same as selecting an appointment for any date. I will discuss with a coworker on Monday on how we can remedy this, as searching for a specific date range should be possible. Thanks for letting us know.

- Luke Miletta

[bravegag]

Hello Luke,

Thank you for looking into this!

I've looked into this issue. In our current implementation for searching for dates, if multiple dates are included, any appointment is selected that fits any of the criteria. For example, with your example date=ge2019-06-07&date=le2019-06-10, it is selecting any appointment with a date greater than 2019-06-07 OR any appointment with a date less than 2019-06-10. That is the same as selecting an appointment for any date. I will discuss with a coworker on Monday on how we can remedy this, as searching for a specific date range should be possible. Thanks for letting us know.

IMHO having OR semantics within a single API RESTful request here is not very helpful but rather confusing e.g. what would the op priorities be? (x AND y OR z) could be ((x AND Y) OR z), (x AND (y OR z)). It should be all conjunctions i.e. AND instead of disjunctions OR. The OR semantics can be emulated in most cases by making multiple separate API requests without added complexity on the client side just stitching the results together. However, that's not the case for the conjunction AND, emulating AND with multiple request calls would involve doing things that are a DB job like set operations, sorting, indexing, etc.

Sadly the FHIR spec leaves this point open to the imagination:
https://www.hl7.org/fhir/search.html#date

Please keep me posted where this will be fixed: 18.x, 19.x or both?

Best regards,
Giovanni

[lukemiletta]

Hello Giovanni,

This issue was fixed in 19.1.32 which we just released. The instance of Open Dental you are testing against should be updated to that. If you would like to stay on stable (18.4.x), then be prepared to wait a little bit. Hopefully we will be releasing 19.1 to stable sometime this month. Let me know if you have any further issues. Thanks!

-Luke Miletta

[bravegag]

Hello Luke,

I updated to the latest version 19.1.32.0 and it works great, thank you! we'll trigger an update at the practice once the 19.x version goes into a stable release and we have our integration work ready.

However, there still is a nuisance I tested the API for dates ("yyyy-MM-dd") and realized that you support timestamps as well ("yyyy-MM-dd'T'HH:mm:ss").

I tested the following border-case and the results are IMO still not 100% correct:

Code: Select all

https://api.opendental.com/fhir/appointment?location=1,2,3,4,5,6&date=ge2019-06-07T00:00:00&date=le2019-06-11T14:00:00&status=booked&practitioner=1

Note the less or equal value is 2019-06-11T14:00:00 and I'm getting (and shouldn't) an appointment entry whose:
start: 2019-06-11T14:00:00
end: 2019-06-11T15:00:00

and should not be the case because the "le" timestamp should correspond to the "end" and not the "start" of the appointment. Likewise the "ge" should correspond to the "start" of the appointment.

Since this request also takes into account dates (without the timestamp part) then it should be consistent and consider a given date le2019-06-11 to be equivalent to le2019-06-11T00:00:00 which means it should not return the appointments for that date as it currently does.

Possible clean solutions:
* Accept dates only ("yyyy-MM-dd") and not date times, in which case the current impl is correct.
* Accept date-times too ("yyyy-MM-dd'T'HH:mm:ss") but then implement it correctly => ge filters by the start of the appointment, le filters by the end of the appointment and finally consider the date truncation issue as well.

These nuisances or border-case inconsistencies may result in a Web widget presenting the user with blocked times per day and showing blocks that are before or beyond the given range. Then the front end would have to trim these broken border cases.

Best regards,
Giovanni

[cmcgehee]

The FHIR standard stipulates that for the appointment date search parameter, the date should only apply to the "start" field.
https://www.hl7.org/fhir/appointment.html

My interpretation of the FHIR standard leads me to believe that le2019-06-11 is equivalent to le2019-06-11T23:59:59. From https://www.hl7.org/fhir/search.html#date, "For example, the date 2013-01-10 specifies all the time from 00:00 on 10-Jan 2013 to immediately before 00:00 on 11-Jan 2013."

[bravegag]

Hello Chris,

Thank you for your support. I read the linked FHIR docs and found what you mentioned and yes you are right, what you have implemented adheres to the Spec written there.

Best regards,
Giovanni