Aug 26, 2022 03:40 AM
Hi all,
I’m building a CRM and have a field for ‘Contact Frequency’ set as ‘Single select’ with 7, 14, 30, 90 as options.
I have a roll up field with ‘Most Recent Interaction’ which pulls from an ‘Interactions’ table.
I would like to create a field to automatically mark as ‘Overdue’ anyone who’s ‘Contact Frequency’ has been exceeded.
Some searching uncovered a formula which I have tried to adapt no doubt with a glaring error!
I suspect that my ‘Contact Frequency’ options are perhaps not in the correct format or need a separate field to convert them into the correct format before a formula can use them?
IF({Contact Frequency}=””,””,IF(DATETIME_DIFF(TODAY(),{Most Recent Interaction},’days’)>{Contact Frequency},“Overdue”))
Any help would be greatly appreciated!
Solved! Go to Solution.
Aug 26, 2022 05:27 AM
Hi @MishQ,
The formula seemed to work for me. Using a single select field shouldn’t be a problem.
IF({Contact Frequency}="","",IF(DATETIME_DIFF(TODAY(),{Most Recent Interaction}, 'days')>{Contact Frequency},"Overdue"))
If you copy and paste that directly and it doesn’t work, you could try creating a copy of your base, removing the sensitive data and creating a share link.
Maybe someone else has an idea of the issue top of mind.
Aug 26, 2022 05:27 AM
Hi @MishQ,
The formula seemed to work for me. Using a single select field shouldn’t be a problem.
IF({Contact Frequency}="","",IF(DATETIME_DIFF(TODAY(),{Most Recent Interaction}, 'days')>{Contact Frequency},"Overdue"))
If you copy and paste that directly and it doesn’t work, you could try creating a copy of your base, removing the sensitive data and creating a share link.
Maybe someone else has an idea of the issue top of mind.
Aug 26, 2022 05:40 AM
@Zack_S, it works!
Looks like I was missing a space before ‘days’ in mine?
Thank you, very much appreciated!
Aug 26, 2022 06:25 AM
The space shouldn’t be an issue.
The style of quotes in your formula looks off. I’m wondering if that is the issue. Just a guess though.
Glad it worked!