Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 08, 2020 11:29 AM
I have a base of events where I am tracking event updates. I am trying to write a conditional formula that will display up to 3 different possible date fields based on 11 outcomes using data from fields already existing in each record.
I have tried nested IF formulas using IF( AND( as well as IF( OR( and have not been able to correctly solve the problem.
I am either getting a formula that produces a date that is not 100% reflective of all criteria or an error message.
I looked into SWITCH formulas but got lost…
Fields within this base include:
Field Type | Field Content
Date Field | Original Event Date
Single Select Field | Update to the event (cancel, postpone, reschedule)
Date Field | Announcement of Update
Date Field | Rescheduled event date
Single Select Field | 2nd Update to the event (cancel, postpone, reschedule)
Date Field | Announcement of 2nd Update
Date Field | 2nd Rescheduled Date
I want to display the appropriate date for each record based on the following criteria:
Return {Original Event Date} if any of the following are true:
Return {Rescheduled Event Date} if any of the following are true:
Return {2nd Rescheduled Event Date} if any of the following are true:
If the single select field {Update} is "reschedule AND the the single select field {2nd Update} is “reschedule” AND the date field {Announcement of 2nd Update} is on/before today.
Any help would be greatly appreciated!
Thanks,
Sarah
Solved! Go to Solution.
Jun 18, 2020 03:32 PM
Try this one. I moved some of the lines around at the end.
SWITCH({2nd UPDATE},
"CANCEL", SWITCH(UPDATE,
"CANCEL", {Show Date (original)},
"POSTPONE", {Show Date (original)},
"RESCHEDULE", {Resched Date}
),
"POSTPONE", SWITCH(UPDATE,
"RESCHEDULE", {Resched Date}
),
"RESCHEDULE", SWITCH(UPDATE,
"POSTPONE", IF({2nd Announce},
{Resched Date},
{Show Date (original)}
),
"RESCHEDULE", IF({2nd Announce},
{2nd Resched Date},
{Resched Date}
)
),
SWITCH(UPDATE,
"CANCEL", {Show Date (original)},
"POSTPONE", {Show Date (original)},
"RESCHEDULE", {Resched Date},
{Show Date (original)}
)
)
Jun 08, 2020 04:39 PM
This certainly is a complex situation.
At first glance, you will need a combination of IF
, AND
and OR
statements nested together.
You might be able to use the SWITCH
function for parts of the formula, but only when evaluating the single select fields. You cannot use SWITCH
when comparing dates.
Do you have a matrix or grid showing all the different possible situations? It would be easier to build a formula based on a grid showing all the different possible combinations.
It seems like some of your combinations overlap. For example in the list below, situations 4 and 5 are subsets of situation 2. Either situation 2 isn’t fully defined, or situations 4 & 5 aren’t necessary.
Jun 10, 2020 08:25 AM
Thank you for your feedback! I do think that the question you posed may be answered by adding an AND OR to situation 2.
Here is what I’ve put together as a matrix to show all the different combinations and outcomes I’d like to display. There is a chance some events may have 3rd reschedules, so I want to be aware of scaling that when building out the formula.
Thank you!
s
Jun 11, 2020 11:37 AM
Thank you for the screen capture. However, it isn’t clear what the values are for the different input fields. Could you do a different screen capture showing all the relevant fields {Update}, {2nd Update}, {Announcement of Update}, {Announcement of 2nd Update}, etc?
Jun 16, 2020 10:37 AM
Hi Kuovonne,
Here is a screen capture of the different fields within the base - show formula is the field I am trying to correct.
In addition to the lookup fields {Link to Show Date}, {Link to Reschedule}, {Link to 2nd Reschedule} I do have the original date fields {Show Date}, {Reschedule Date}, {2nd Reschedule Date} in the table as well…if that makes any difference in constructing the formula.
In this example:
Jun 16, 2020 11:33 AM
Thanks for the screen capture.
In general, if you have access to the original date fields, it is far better to use the original date field than the lookup fields. Lookup fields and date fields cause more headaches in formulas than any other field types. A lookup of a date field is the most difficult to troubleshoot.
Would you be able to create a sharable link to a simplified version of the table with the data so that I could make a copy to work with?
Jun 17, 2020 08:53 AM
Here you go
Jun 17, 2020 10:01 AM
Thanks for the the shared link.
Here is a first stab at the formula. I wasn’t able to test it because the shareable link you created did not let me make a copy of your base. There may be some errors in field names, extra/missing commas, that I can’t test unless you make your table copyable.
I also had some trouble identifying the logic based on announcement dates. But hopefully this formula will give you enough of a starting point.
SWITCH({2nd UPDATE},
"CANCEL", SWITCH(UPDATE,
"CANCEL", {Show Date (original)},
"POSTPONE", {Show Date (original)},
"RESCHEDULE", {Resched Date}
),
"POSTPONE", SWITCH(UPDATE,
"RESCHEDULE", {Resched Date}
),
"RESCHEDULE", SWITCH(UPDATE,
"POSTPONE", IF({2nd Announce},
{Resched Date},
{Show Date (original)}
),
"RESCHEDULE", IF({2nd Announce},
{2nd Resched Date},
{Resched Date}
)
),
SWITCH(UPDATE,
"CANCEL", {Show Date (original)},
"POSTPONE", {Show Date (original)},
"RESCHEDULE", {Resched Date}
),
{Show Date (original)}
)
Jun 18, 2020 02:37 PM
Thank you for this! The formula is giving me an error message - invalid formula. I don’t know much about switch formulas, but I’m wondering if every use of UPDATE should be written as {UPDATE} - that is trying to reference that field, correct? I will give that a try and will go thru looking for missing/extra commas.
I do have the toggle “on” to allow viewers to copy data out of this view. Is that what you meant by making the table copyable?
Thanks again for your time on this!
Jun 18, 2020 03:32 PM
Try this one. I moved some of the lines around at the end.
SWITCH({2nd UPDATE},
"CANCEL", SWITCH(UPDATE,
"CANCEL", {Show Date (original)},
"POSTPONE", {Show Date (original)},
"RESCHEDULE", {Resched Date}
),
"POSTPONE", SWITCH(UPDATE,
"RESCHEDULE", {Resched Date}
),
"RESCHEDULE", SWITCH(UPDATE,
"POSTPONE", IF({2nd Announce},
{Resched Date},
{Show Date (original)}
),
"RESCHEDULE", IF({2nd Announce},
{2nd Resched Date},
{Resched Date}
)
),
SWITCH(UPDATE,
"CANCEL", {Show Date (original)},
"POSTPONE", {Show Date (original)},
"RESCHEDULE", {Resched Date},
{Show Date (original)}
)
)