Forum Discussion

HunterCollins's avatar
HunterCollins
Qrew Member
4 years ago

Multiple Condition If/Then

Good Morning!
I have an If/Then Statement that I need to have multiple conditions. I want the find the date between two appointments. Both Appointment dates are listed in the field [appointment date] however there are different appointment types attached to those dates. I am trying to find the date range between new patient appointments and ultrasounds. These are labeled as "NEW PATIENT" and "US IN MAP." Here is my current formula that is not working.

If([REASON FOR VISIT]="NEW PATIENT",
[REASON FOR VISIT]="US IN MAP",
abs(todays([APPOINTMENT DATE]-[APPOINTMENT DATE])),
null, null)

All help is appreciated, thank you!

------------------------------
Hunter Collins
------------------------------
Formulas and functions
Relationships
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 years ago
    I think you might need to provide more context for your current structure. A single date field, [Appointment Date] cannot hold two dates. I suspect you have a child table of appointments, with one field called [Reason for Visit], and one field called [Appointment Date]?

    If so, what is the parent table, and how many appointments can be children to that parent?

    In general, if you have a formula date field, the syntax would be:
    If(
    [Reason for Visit]="New Patient", [Date that will Display Goes Here],
    [Reason for Visit]="Us in map", [Date that will Display Goes Here],
    [Else this date will display]
    )

    ------------------------------
    Michael Tamoush
    ------------------------------
    • HunterCollins's avatar
      HunterCollins
      Qrew Member
      4 years ago
      I have a parent table of patients with a child table of appointments which is on a one to many relationship. So one patient may have several appointments, with no current limit on number of appointments. Within appointments i do have a field that is reason for appointment as well as one that has the appointment date. From there i am using a numeric formula field to calculate.  I hope this helps!​

      ------------------------------
      Hunter Collins
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Hunter

        I need to make a summary field on he Parent record for the maximum date of the appointment where the [Reason for Visit] equals NEW PATIENT

        Then repeat for US IN MAP

         Now you have Two clean fields on the Patient record and you can subtract.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------