Issue with complex, dynamically filtering of portal

[Johstrom]

Hi,

I hope that someone on this community is able to assist me in a rather complex dynamical filtering of portals... I am working on a pretty complex CRM based on this demo for dynamical filtering of portals by Sara Severson: http://www.soliantconsulting.com/blog/2013/03/dynamically-filtering-filtered-portals

But I have an issue when trying to dynamically filter the portal based on several relationships, that someone here maybe can answer... This is my situation:

Filemaker file A (the CRM) contains a portal which I filter. The portal show posts from file B (Contact database) with the use of a relationship X. The relationship match fields in the two files are global fields containing just "1" - which matches all posts. 

The filtering of the portal here is working great. I can filter posts from the Contact database without any issues. But I would like a second dynamical filter field in relationship with a file C (an Order database) so I can narrow down the contacts in regards to what they ordered.

So I made, in file A, another relationship between file B (relationship X) and file C (Order database). Here I match a client ID in file B with a client ID in file C. And adjusted the portal filter criterias accordingly, so it took notice of this second filter field.

And yes, now it seemed that I could filter out specific orders, to find, for example, only clients in city X (from the file B, the contact database) which has bought item A (from the file C, the order database). But I noticed it didn't find every client with a particular order - after some debugging I found out that this procedure did only find the latest order a client made, in file C. 

In other words: 

If client A (from contact database) ordered item A and then later item B (posts in the order database), the filter did only find the client's order of item B. If I try to filter for item A, it found other clients that ordered this item, but not the client which ordered both A and B. 

It seems that the relationship between file B and C only matches the latest match, which seems a little odd. 

If I put a portal in file B (the contact database), with relationship of Client ID with file C (the order database), Filemaker found all orders, but only the first row in this unsorted portal here is found by the filter in file A.

Any ideas? How can I make the relationship between file B and C in file A to find all orders? 

Here an image of the tables in file A, with some complementary information: https://postorder-hstrom.tinytake.com/sf/MTUxODk5N181Mjg2NDAz

With kind regards,
 - Johan.

[Johstrom]

Hi again, 

I think I have solved this with a workaround - instead of trying to fetch data directly from file C (the order database) in File A, I created a calculation field in File B (the contact database) with the List function, fetching all necessary data from File C. Then I can filter the portal in File A on this new calculation field in File B.

Though, the portal refresh ain't quick and this makes an already very heavy database even more heavy, so any ideas on how to solve this without the need for such a "summarize" field, would be appreciated.

With kind regards,
Johan.