HunterCollins
Qrew Member
3 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
------------------------------

4 Replies

• 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
------------------------------
• 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
------------------------------
• 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
------------------------------