Slow Layout

[crazybake]

This may not be the right forum...

I have a layout that is VERY slow to load. After reading through several threads, I know why. My approach in this layout is not good.

 

My solution contains tables: Students, Transactions, TransactionLineItems ...

 

A layout showing records from the Students table contains a portal showing records from TransactionLineItems (assets currently checked out.) Now the "bad" part. This portal has several unstored calculation fields - example:

 

The due date for checking out an asset is based on (4) other calc fields:

1- checks the allotted time for an asset (2 days, 1 week etc) and assigns a due date

2- based on above date it checks to make sure that it is not a scheduled holiday or a weekend (sometimes allowed, based on asset settings), keeps or reassign due date

3- based on above date, it checks for school breaks, and based on a particilar asset, this may be allowed.

4- the final due date checks for terms and if the checkout or due dates fall inside this timeframe (Fall, summer winter...) and if access to certain assets are allowed.

 

The other unstored calc field in the portal determines whether an item is late or not based on current date and time and compared to the due date and time. The calc field shows: "late", "on-time", "due today" based on the result

Last, I have another portal with a summary field that counts the "late" records in the other portal. Even writing this - I feel the distant cringes.

 

That said, I need advice: Would it be best to combine all of the date calcs above into one calculation? would that help? Or should I approach it differently?

For counting the late records in the portal, I tried to create  self-join relationship, but it could not get it to work. What would be the best way to count unique portal records based on a field (besides what I have done)?

Or - start over... ugh

 

 

[Nuos]

Ok I'll stab at this. One calc won't help I don't think. The simplest would be to load the student then go to related to display assets checked out. Further I would go to a related layout without any of the calcs on it - actually nothing displayed then a go to layout of the actual records to display. That way the engine only runs on the selected records not all records. Experiment with this to see if it helps.

If you need to display assets, can you gather the info using scripting (try SQL) and format it into a global field that in the displayed on the main layout - a virtual portal if you will.

Finally, you could display in a portal just the asset, with no other fields besides its name. Then try conditional formatting (if due, make font red) or have gif that only displays if related records due now field is set to true? It'll take some experimenting. The bottom line is this:

1) try to get rid of all sum or unstored calc fields on the first layout. OR

2) limit exposure to them as much as possible OR

3) creat splash page that warns user that loading their request may take some time

Good luck

M