Enable Filtering in Relationship Definitions

Idea created by richardsrussell on Jul 31, 2016
    • PeterGort
    • hschlossberg
    • SteveNoble
    • richardsrussell
    • deanster2u

    This proposal deals with how to filter related records. But let me approach it obliquely by beginning with the observation that we often want to sort related records — alphabetically by name, chronologically by date, numerically by amount, etc. And there are 2 ways in which we can do so. We could use a portal-setup dialog ...


    Portal Setup Sort.jpeg


    ... OR ...


    ... we can do it right in the definition of the relationship itself ...


    Edit Relationship Sort.jpeg


    ... and if we choose the latter approach, the sort order as we define it is implemented not only in all portals based on that relationship but also (importantly) on all stand-alone single fields and calculations that take their value from the topmost record generated by that relationship.


    For example, if we've got a relationship starting in the Parent table that shows us records from the Child table "Colors" ...


    Color Samples.jpeg


    ... and the relationship is defined to be sorted numerically by "Wavelength", the topmost "Colors" data as seen by the Parent table would be from the "ultra-violet" record. But if we defined the relationship to be sorted alphabetically by "Color Name", the topmost values would come from the "blue" record.


    It's worth noting that the sort order defined in the portal trumps the one defined in the relationship definition. For example, even if the relationship were set up to have the colors sorted by "Color Name", that specification would be overridden by any portal which required instead a sort by "Frequency".


    So, with that introduction out of the way, let me get to the meat of my proposal. Just as we often want to sort related records, so too do we often want to filter them. This involves excluding certain records from the Child table that otherwise would be considered to be related to the Parent record. For example, we might want to exclude from the "Colors" table all the non-visible ones. Or a relationship from an "Organizations" table to a "People" table might filter out non-members of the organization. Or one from a "Customers" table to an "Invoices" table might want to filter out negative values, so as to show only sales, not refunds. And so on.


    We can create such filters using a portal-setup dialog ...


    Portal Setup Filter.jpeg


    ... OR ...


    Or what? That's the lacuna I'm proposing to address. There is no other "what"! We can't filter records using the relationship definition. I think we should be able to. Then we could display the topmost related value as a stand-alone field all by itself in the Parent table, without needing to enshroud it in a portal. (This is what I was trying to do to display the "Most Recent Sale" date and amount from an "Invoices" table when I encountered this limitation.) Or we could use that topmost value in a Calculation field within the Parent table. Right now those options are foreclosed to us without a whole lot of complicated fiddling and brain-twisting.