AnsweredAssumed Answered

Relationship with unstored calculation

Question asked by luis.ecohaus on Jul 13, 2017
Latest reply on Jul 13, 2017 by LabsRock

Hi all,

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.

 

Screen Shot 2017-07-13 at 16.03.51.png

This is set up as a unstored calculation as it needs evaluation every single day to be updated.

The picture below described the relationship:

 

Screen Shot 2017-07-13 at 16.05.51.png

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

ExecuteSQL (

"SELECT count (*)

FROM LEAD

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.

Outcomes