Jump to content

Filtering via relationship


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

Recommended Posts

Hi guys

I have a couple portals that are filtering via the portal rather than the relationship and and wanted to see if someone could recommend a way of changing this to filter purely by the relationship.

For example:

I have a company layout that has a portal to display each of the following: related invoices, related purchase orders and related projects. In this post I'll use Invoices as an example. The initial relationship does filter down to the related company record (company::id = invoices::id_company) however, I have multiple other filters that need to be applied, which is where I'm having the portal filter the results at the interface level. I have a series of checkboxes to further filter the results shown (in terms of invoices: open, in process, void, completed, closed, etc) which references the Status field within the Invoices table. Currently, multiple options can be checked and the portal will filter the results respectively. This is a global field within a separate table (invoiceGlobals::gInvoiceFilter). Initially I don't expect there to be many related records, so filtering via the portal is relatively fast right now, but as more related records are added I'm anticipating the portal needing to filter many more, especially since there are two other portals on the same layout performing the same task.

I guess the point is, is there a way to filter all of this through the relationship graph? I'm a little stumped because there's multiple criteria.


Link to comment
Share on other sites

7 minutes ago, madman411 said:

is there a way to filter all of this through the relationship graph?

Of course there is. But the global field used for the filtering must be in the Companies table, so that you can add it as a matchfield to the relationship. In your example, the relationship would be defined as:

Companies::CompanyID = Invoices::CompanyID
Companies::gStatus = Invoices::Status

or perhaps you prefer to use another TO of Invoices for this, so something ;like:

Companies::CompanyID = Invoices 2::CompanyID
Companies::gStatus = Invoices 2::Status

Once you have this in place, the relationship will show the invoices of the current company whose status corresponds to one of the options checked in the gStatus field.


  • Like 1
Link to comment
Share on other sites

Thanks comment, I will test this out when I next have access to the database.

My only concern is if multiple check boxes are selected. For example, if "open" and "void" are both selected, then only open and void invoices will display in the portal. If I remember correctly I had to use a function in my filter calculation... I believe it was FilterValues - will the relationship graph be able to decipher two or more values in the same field at a time?

Link to comment
Share on other sites

I used to always filter relationships and someone just pointed out that filtering portals is better, because you can re-use one relationship and just filter the portals. It's a tad more difficult to filter the portals but more importantly I'd like to know if there is any speed degradation when using filtered portals compared to filtered relationships. Let's assume we do something simple, not crazy, just for the sake of argument.


Can someone enlighten me, please? Thank you.

Link to comment
Share on other sites


Filtered portals are inherently slower compared to filtered relationships, because a filtered portal must evaluate the filtering expression for each record on-the-fly, while a filtered relationship relies exclusively on indexed fields. This is somewhat similar to the difference between stored and unstored calculation fields, when performing a find.

That said, filtered portals are definitely usable with a reasonable amount of related records, and they do have the advantage of not cluttering the relationship graph.

Edited by comment
Link to comment
Share on other sites

Thank you, Comment, for lack of knowing your name after this many years.

That's what I assumed, but I always like to get confirmation for some reason. :-)

So theoretically virtual lists combined with PSOS would be better than both, right? I really don't want to build large (even if organized) graphs anymore.

Link to comment
Share on other sites

Regarding virtual lists: I can't recall if this was discussed here or on the Community site. 

But there are several ways to build virtual lists, and for large lists, there are definitely some ways of doing it that provide poor performance.

Where N is the line number field:

GetValue( $$myMassiveArray; N) is the worst.

$$data[N] is way better, assuming your collector script has put the data into individual "reps" of the variable.

There are some variations but that's the basic idea.

Link to comment
Share on other sites

This topic is 3031 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.