There's gotta be a better way to filter this portal...

[Rich S]

Howdy, howdy:

 

I have a self-join portal (by recordID) with five global fields above it that I want to use as filters, with the "default" filter being EVENT_PARENT_X_ID::EventDate__lxd ≥ Get ( CurrentDate ) so that if no values are chosen as filters, the portal will display the records with the current date, forward.

The problem is how to set up the filter calculation so that if one or more filters have values chosen, the portal will filter records based on those values. The problem is--at least how I'm looking at it currently--is that I'd need to create many permutations for the filter calc to cover all conditions. Surely, there has to be a better way of coding this!

Also, here's an odd thing: I attached a script trigger to each of the five filter fields so that when the value within is changed/modified, it fires the script to flush cached join and external data and refresh the window. The weird thing is, that none of the script trigger options will fire the script, but when I manually fire the script, it works as it should.

[comment]

I was stumped by your very first sentence:

40 minutes ago, WF7A said:

I have a self-join portal (by recordID) with five global fields above it that I want to use as filters

If the self-join is by recordID, then surely there will ever be only one related record, which is the same record?

Perhaps your relationship uses the x relational operator (in which case it's irrelevant which fields are the match fields)?

 

43 minutes ago, WF7A said:

how to set up the filter calculation so that if one or more filters have values chosen, the portal will filter records based on those values.

Here is a simplified example using 2 global fields:

( IsEmpty ( Parent::gColor ) or Parent::gColor = Child::Color )
and
( IsEmpty ( Parent::gSize ) or Parent::gSize = Child::Size )

This filtering expression will apply filtering by color if - and only if - the global field gColor has a value. Otherwise it will pass all colors. As you can see, it takes exactly one line per filter to cover all possible combinations.

I am afraid I did not understand the "default" filter thing. If you have a predicate that you want to apply always, regardless of other conditions, why don't you  implement it as part of the relationship?

I suggest you post a separate question regarding the script triggers.

--
P.S. Next time please post code, not pictures of code.

 

[Rich S]

Sorry about that--I should've written "Cartesian Product Relationship"; I do have the "X" selected between tables but used the ID as the connecting fields...which is moot because if I recall correctly, one can use any two (connecting) fields in such a relationship; it doesn't have to be the ID field.

"I am afraid I did not understand the "default" filter thing. If you have a predicate that you want to apply always, regardless of other conditions, why don't you  implement it as part of the relationship?" Simple answer: because I didn't think of it.  : |

Thanks for the suggested fix (it works well), and I'll post separately about the script triggers...and sorry about posting picture instead of code--I won't make that mistake again.

 

 

Edited July 27, 2021 by WF7A

[comment]

6 minutes ago, WF7A said:

if I recall correctly, one can use any two (connecting) fields in such a relationship;

True. Once you have defined the relationship, you can even delete the match fields and it will keep working.

P.S. Kudos for using the term "Cartesian Product Relationship" instead of the so often misused "Cartesian Relationship".