I have one table of people, and another with records of their visits. In the people table, I am trying to add a field that will tell me the date of the earliest visit… after 9/30/2016.
I tried adding a formula field in the visits table to return the date of the visit only if it is after 9/30/16, but I have no idea how to build a specific date into the formula. I really don’t want to add another field to the table that will force me to add “9/30/16” to every single visit record just so I can do an IS_AFTER formula with two date fields. (Which reminds me, why don’t default values you set for certain fields show up when you add new records in the mobile app? Real bummer. But I digress.)
IF(IS_AFTER(DATE,9/30/16),DATETIME_FORMAT(DATE,‘MM/DD/YYYY’),"") returns #ERROR everywhere. I am really hoping my format “9/30/16” is the problem, but I have tried other variations with no success.
Meanwhile, if it is possible to get that formula to work, will a roll-up field in the people table let me pull the MIN from that calculated field? Is there a better way to do this? I’d do it manually, but I have a couple hundred people records.