I believe there are other topics related to this question as well. However, after a search in the community I couldn't find an answer to my situation.
My goal is to count the number of leads described as rotten. For that purpose I have an unstored calculation field that sets a specific field (rotten_flag) with value 1 if a lead is in a specific pipeline stage more than a certain desired number of days. Otherwise, the field is populated with 0.
This is set up as a unstored calculation as it needs evaluation every single day to be updated.
The picture below described the relationship:
- it is a cartesian relationship with the second evaluation being the one checking the rotten flag.
Initially I was getting the number of rotten leads using the ExecuteSQL function. It worked fine, however it is costing a lost of CPU usage, and the application takes some time to calculate this.
Here is the SQL query:
"SELECT count (*)
WHERE \"rotten_flag\"=1" ;
"|" ; "¶"
I am using FM Cloud and I believe it doesn't support cron jobs, so a night job is out of question.
Thank you in advance.