Filtering via relationship

[madman411]

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.

Cheers!

[comment]

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
AND
Companies::gStatus = Invoices::Status

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

Companies::CompanyID = Invoices 2::CompanyID
AND
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.

 

[madman411]

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?

[madman411]

Scratch that, comment. I created a test file and it works perfectly.

Many thanks.

[comment]

For reference:
http://www.filemaker.com/help/14/fmp/en/html/glossary.html#1027937

Keep in mind that a field formatted as checkboxes contains a return-separated list of checked items.

Edited November 11, 2015 by comment

[Agnes Riley]

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.

[comment]

Agnes,

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 November 12, 2015 by comment

[Agnes Riley]

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.

[comment]

25 minutes ago, Agnes Riley said:

So theoretically virtual lists combined with PSOS would be better than both, right?

I don't think any one thing is better than the other, overall. It depends on the purpose.

[bruceR]

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.

[webko]

Dear Bruce,

At what point would an array be considered massive? 1k rows, 10k, 100k?

Is anyone able to point me at the original discussion on this?

Cheers

Webko

[bruceR]

Low hundreds of rows. Also see:

https://community.filemaker.com/message/520205#520205