Solved

FM - Problem with Portal Response Time Using Summary Numbers

Posted on 2014-04-03
5
431 Views
Last Modified: 2014-04-04
I have Year, YearMonth, and YearWeek tables that I use to total the count of Leads by agent.  Then I bring these back in via an X relationship and a portal.  However, when I click on the tab for the portal (each of the 3 above have their own separate tab), it takes about 3-5 seconds to show, thus it seems it is calculating.  

The Year tab/portal has the slowest reponse time and even shows a "not responding" in FM.  I even tried limiting it to only go back to 2009 and it shows little improvement.  The LEADDATA table is 26,000 records, but I thought FM could handle that easily.  Is there a better method than the one I'm using?  See screen prints.

I've thought of making the calc fields stored, but that would work for the past numbers, but not the current year, month, or week.  As I was doing the screen prints, I noticed that the flag fields are unstored.  Orginally, those could change, but now we have a separate reassign field, so possibly I could store these.  Any other suggestions?
-LEADDATAtable.jpg
-Yeartable.JPG
-Portal.jpg
0
Comment
Question by:rvfowler2
  • 2
  • 2
5 Comments
 
LVL 25

Accepted Solution

by:
Will Loving earned 333 total points
ID: 39975592
You do seem to be pulling a lot of summarized data across multiple relationships and that will definitely slow down portal display because each summary is being calculated on the fly. In general, the more "unstored" fields involved, the slower the response will be so eliminating those can sometimes make a significant difference.Summaries are by definition unstored, so if you summary fields are summarizing data that is also unstored that will also slow things down. Your "flag" fields are probably an excellent place to start.

Depending on your needs and structure, you can create ways to prevent an unstored calculation. The main way to do this is to a parallel, number or text field that is set to Auto-Enter every time another field changes or is updated, that is updated whenever a particular script runs, or that is updated via a script run on a schedule basis such a on Startup or via a "Refresh" button. The script or Auto-Enter calculation updates the parallel field based on the unstored calculations current value. All of this is intended to give you a number or text field that is current but also indexed (stored).
0
 
LVL 6

Assisted Solution

by:slinkygn
slinkygn earned 167 total points
ID: 39975636
I think you're on the right track.  Storing the flag fields (and then letting them get indexed after the next access) will help -- in fact, that may help more than anything else.  Storing the totals will help as well; if you have issues with the recent ones not being updated, you can force update them via a script trigger on the tab object.  OnObjectModify fires when you switch tabs; you can then GetLayoutObjectAttribute to see which tab is in front and force an update to the most recent year/month/week accordingly.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 39976229
How would you force an update?  Do you use the RefreshWinFlushCachedJoin or is that only the join and not the actual calc field.  I've always wondered how you would trigger or refresh a stored calc.
0
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 333 total points
ID: 39976626
Well, unless all fields are local, a calculation can't be stored, especially if it's summarizing related records. However, you can store the result of an unstored Calc in the previously described parallel field.

You could, via a script trigger force an update of the parallel field each time and edit is made to an individual record. You can also experiment with using a calculated auto-enter on the parallel field (with the second checkbox unchecked in the Auto-Entery options) be referencing in a calculation a field that is changing and causing the total to change. Or, you can run it more globally by periodically doing a Replace on all the parallel fields.
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 39978407
Thanks.  All suggestions really hepful.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question