Jump to content

Trying to update filtered portal to filter in the relationship itself


Courtney

This topic is 2732 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

 

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2732 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.