7 Replies Latest reply on Jul 13, 2017 3:26 PM by LabsRock

    Relationship with unstored calculation

    luis.ecohaus

      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.

        • 1. Re: Relationship with unstored calculation
          philmodjunk

          If you want this to control what you see on your dashboard, the relationship won't work. Relationships require indexes on the "many" or "fetch" side of the relationship in order to work and thus you cannot use an unstored calculation as a match field on the LEADS side of the relationship and get it to work from the context of Dashboard.

           

          What you could do is use a portal filter to omit related records with this flag value.

          1 of 1 people found this helpful
          • 2. Re: Relationship with unstored calculation
            luis.ecohaus

            Thank you Phil.

            I thought about a portal. But have a look at my design below. Would a portal be suitable in such layout design?

            Screen Shot 2017-07-13 at 16.18.48.png

            Another thing, I don't understand is why the SQL query is running so slow. I have more SQL queries using the count (*) function and they don't take so long. At the moment there are no rotten leads as well, so I don't quite understand why is it consuming so much system performance?

             

            Here are my server specs as well:

            • T2.medium
            • 2 CPUs
            • Up to 3.3 GHZ
            • 4 GB Memory

             

            I don't think it will make a difference if I upgrade it to T2.Large (4 GB more of memory).

            • 3. Re: Relationship with unstored calculation
              philmodjunk

              I don't see any issues with your layout that would preclude a one row filtered portal. But no matter how you slice it, an unstored calculation field will be a challenge here because, whether via a filtered portal, a "find and count them" script or an SQL query, you have to evaluate this field on every record in your table in order to determine which to count.

               

              You may want to set up a scheduled script that updates a simple number field that can be indexed to use as your Flag. This scheduled script might run between 12:30 and 1 am for example to "age" all non rotten leads into rotten status if the criteria is met. Note that you can use the value of this stored field to reduce the number of records that need this update by finding only those not currently "flagged".

              1 of 1 people found this helpful
              • 4. Re: Relationship with unstored calculation
                luis.ecohaus

                The schedule script is out of question as it is not available within FM Cloud.

                 

                                                                  FM SERVER                                                                 FM CLOUD

                AutomationSchedule server scripts as needed.Not able to schedule server scripts.

                Compare FileMaker Cloud with FileMaker Server

                 

                As for the portal, I will have a go but I am not very confident that my current skills will help me with that.

                Thank you.

                • 5. Re: Relationship with unstored calculation
                  easyaspi

                  As a workaround;

                   

                  Create a Number field RottenLeadsStored in your Leads table.

                  Assuming you have a one-record utility table, create a Date field called DateStoredValuesUpdated.

                   

                  Create a script that finds all Leads, and sets the RottenLeadsStored field to 1 or 0 as appropriate for each record, then sets DateStoredValuesUpdated in the Utility table to the current date. The first line of this script should be an Exit Script step that exits the script if DateStoredValuesUpdated = today's date.

                  Set this script to run when the file opens.

                   

                  This script will run when the first person logs in each day.

                  Of course, this may be too slow if you have a ton of records to update.

                  But it only affects the first person the first time they log in for the day, so it may be an acceptable delay.

                   

                  Mark

                  1 of 1 people found this helpful
                  • 6. Re: Relationship with unstored calculation
                    philmodjunk

                    Or one of you can kick off a similar script that performs this task and then closes the app for you as your last task of the day. You can run the script and go home without waiting for it to complete. You'd need to modify your calculation by one day to get the same results, but would be less of a nuisance to your users.

                     

                    Hmmmm, can't run ANY scheduled scripts in Cloud? I missed that one. We've been considering moving to the cloud, but that limitation would totally kill the idea for us, maybe cloud 16, which I understand had not been released yet, will offer support for that.

                    1 of 1 people found this helpful
                    • 7. Re: Relationship with unstored calculation
                      LabsRock

                      Can't you run script on server in cloud?  That would speed the update process whether you run it when the first person opens the file each day or when the last person closes it.