Trying to update filtered portal to filter in the relationship itself

[Courtney]

I have a Dashboard layout that is based on my Personnel table.

I have a portal on this layout that is based on my Project table.

Personnel is related to Project by Personnel::Employee UID = Project::PM - so that the person who is designated PM (Project Manager) on the project sees his projects in this portal.

The portal is filtered by whether field Project::PM_Count is greater than 0, where PM_Count is a calculation (number) with this definition:  ValueCount ( FilterValues ( List (ProjectTimesheet::eReview Status ) ; "PM Review" ) ) --- (This counts timesheets that the PM needs to review.)

Currently, with about 160 projects with PM_Count greater than 0, the portal is taking about 2 minutes to refresh after each review.

I am trying to get rid of the filtering by making a relationship that would accomplish the same result, because I understand that should be faster. However, since PM_Count is a calculation field, I'm having a hard time creating a relationship that will work.

How can I create a relationship for this portal that won't require filtering of this nature?

 

[comment]

If I understand your description correctly (a big if!), you want to:

1. Define a calculation field in the Project table cCondProjectID=

If ( ValueCount ( FilterValues ( List ( ProjectTimesheet::eReview Status ) ; "PM Review" ) ) ; ProjectID )

 

2. Add a new occurrence of the Project table to the relationships graph, and link it to Project as:

Project::cCondProjectID = Project 2::ProjectID

 

3. Set your portal to show records from Project 2 (and don't forget to also change the fields inside the portal to fields from Project 2).

 

Caveat: untested. And there's no guarantee that this will be significantly faster than filtering the portal. Eventually, to have performance, you must eliminate unstored calculations and use scripts to update values in stored fields.

Edited October 4, 2016 by comment

[Courtney]

Thank you. I will put this into my test file and see how it goes.

 

Can you expand at all on eliminating unstored calculations and using scripts to update values in stored fields? Is there a 'best practice' type paper on that kind of thing? Ultimately, my goal is to improve performance, especially when people are connecting through our VPN vs sitting in the office.

[bcooney]

Courtney, this might be a good start : 

 

[comment]

On 10/04/2016 at 11:39 PM, Courtney said:

Can you expand at all on eliminating unstored calculations and using scripts to update values in stored fields?

This is a very wide subject and I don't think it's suitable to discuss it as footnote to your question. I suggest you do some reading on denormalization.