Two portals - relationships or filtering?

[Angus McKinnon]

First time on the forum in a good while. I'm trying to rejuvenate a hire bookings database which has had several people working on the development in the past and is a bit of a mess. 

 

The main "dashboard" layout has two portals on it, which list orders due to be dispatched, and orders which are out on hire and due to come in. Each of these is accessed via a relationship, with a "start date" field in the dashboard table relating to either the date out or date in field of the orders. 

 

The main irritation at the moment is that, because there are two relationships going on, there is a lot of duplication which seems needless. (Things like conditional formatting rely on fields through that relationship, so I can't copy and paste elements from one portal into another) Minor tweaks are becoming quite time consuming. 

 

What I'm tempted to do is change the dashboard layout so I have two portals based on an identical relationship, using the "x" operator so that all orders appear in both portals, then filtering them differently to get the orders displayed that I want. This seems simpler and more elegant, but I'm not sure if there will be any performance hit. Is there more processing involved in selecting records via a portal filter instead of via a relationship?

 

 

The other thing I'm thinking about is the amount of modifiers I will need, to make the portals do what I want. For example, I want the user to be able to hit a button and exclude one of our most prolific customers from the portal, so we can see what else is going on. Traditionally, my way of doing this has been to have a field called "exclude <company name>" with the values "yes" or "no", then use a button to Set Field, and reference it in the calculation that defines the portal filtering. 

 

I've recently become aware of the existence of variables and wonder if this would be a neater way of doing things? It would mean that single purpose fields won't be clogging up lists elsewhere. Again I'm unsure of any impact this change might have on the speed of the solution. 

[comment]

 Is there more processing involved in selecting records via a portal filter instead of via a relationship?

 

Yes, there is. A relationship relies on indexing the matchfield/s on the child side in order to select the records. Portal filtering, OTOH, is done "on the fly"; similar to an unstored calculation and is not suitable if you have a large amount of records to sift through. You'll have to experiment to see how serious the impact is in your particular scenario.

 

Re your second question: even if you decide to keep the relationships, you can eliminate the "exclude" field/s in the child table by having an exclude field on the parent side, with (for example) a return-separated list of the child IDs you want to exclude from the relationship.  You just need to add another predicate using the ≠ relational operator to the existing relationship (and make sure the exclude field is never completely empty, but contains a dummy value instead).

[Angus McKinnon]

Thanks, that's a very helpful start. 

 

At the moment we have around 10,000 order records on the system, I reckon the most we'd accumulate from here is about 3,000 per year. 

 

I suppose instead of an "x" relational operator, I could use ">" so the relationship excludes orders older than, say, six months, which will be the vast majority of them, then the portal filtering has only the remainder to sift through on the fly?

[comment]

I suppose instead of an "x" relational operator, I could use ">" so the relationship excludes orders older than, say, six months, which will be the vast majority of them, then the portal filtering has only the remainder to sift through on the fly?

 

Yes, that is correct.