Filter two date fields at once?

[phcaptjim]

Hi,

I have been looking through the community for an answer to my question but could not find the right answer so I decided to start this discussion.

Basically I want the user to be able to select a date (month, quarter, year) and get all the data for a set of patients were EITHER admitted or discharged during the year.  Right now I am only looking at discharge date. 

What I would like to do is this:

If you select a year, say 2013,  the filter should grab all patients who have an ADMISSION_DATE or DISCHARGE_DATE within the year 2013.  It seems fairly simple, how do I accomplish that?

Thanks!!!! 

[Christian_Lauritzen]

Hi!

You might need to tweek the data model a bit. Introduce the field Date that contains the date for an event, regardless if it is admission or discharge. Then add the field EventType that contains "Admissions" or "Discharges". If you add EventType as a selection list box, you could also choose to include/exclude them as you wish. Even though it is tricky to help you out without understanding your data mode, something along those lines should do it.

/Christian

[phcaptjim]

Thanks Christian.  I know exactly what you are talking about.  I thought about adding an event date table for this selection but the size of my app is already 600mb and I don't want to make it much bigger, however this is probably the most efficient way to do this.

I'll implement this and see how it goes.

[chiru_thota · Accepted solution]

I know it is already answered.Below screenshot may be useful.

[Christian_Lauritzen]

Another route then; Use only one of the date fields, say that you have called it YearMonthAdmissions, but label it Month in the user interface. Then create a trigger event on Select Field, which sets the corresponding values in YearMonthDischarges.

[rwunderlich]

This tutorial provides a pattern for handling multiple dates using a link table as well as optional fact calendars.

Linking to two or more dates

-Rob

[Christian_Lauritzen]

Nice Rob. Worldclass explanation!

[phcaptjim]

Lots of great information here!  Thanks!

I went with the CalendarLink table.  My table has Encounter ID, Date, and Date Type (either admission or discharge).  The Date field then links back to my main Calendar table.  It's working rather well!