Skip to main content
Question

I need to Sort/Group By Two Different Date Fields

  • July 30, 2019
  • 0 replies
  • 31 views
I have two date fields in a form for potential/admitted patients ? ?Inquiry Date? and ?Admission Date.? Every form has an Inquiry Date but there might not be an Admission Date. I would like to create a bar chart showing how many inquiries and admissions I have had per month. I have already created formulas to count the number of each date (EX: COUNT([Inquiry Date])). These Count Formulas work well as they show the information I want but, my problem is that a patient can inquiry in one month and be admitted the next. How do I sort/group by a date when there are two dates to consider? Any suggestions would be great. Thank you!

 EX: Patient?s Inquiry Date is July 30, but Admission Date is Aug 1. When I sort by either date field and group by Month. The patient is either assigned to July as an inquiry or to August as an admission. How can I count the patient as an July inquiry AND an August admission?
    This topic has been closed for replies.

    The simplest way is to have two separate reports.

    But if you really want to have 1 report then you need to have two records to chart.  To do that you would need to have a child table on the Patient table and use automations to maintain the children.

    Basically you would have an Automation that would trigger whenever a Patient is created or modified and either of those dates change, to delete the existing child records  and then add them back.
    Terms & ConditionsAccessibility statement