Howdy, all:

I have this filter for a self-join portal:

Let ( [

indexValues = List ( TEACHER::_kplt_TeacherID ) ;

searchValues = List ( TEACHER::Name_LF__lct ) ;

index = ValueCount ( Left ( indexValues ; Position ( ¶ &  indexValues  & ¶ ; ¶ & TEACHER_X::_kplt_TeacherID & ¶ ; 1 ; 1 ) ) ) ;

prevValues = LeftValues ( searchValues ; index -1 ) ] ;

IsEmpty ( FilterValues ( TEACHER_X::Name_LF__lct ; prevValues ) ) )

 

The filter works fine as-is but I also want to include in it another constraint where names will only appear if the Status__lxt field (for each name/record) is set to "ACTIVE".  I tried different permutations of adding that bit but I just can't get the portal to work as wanted. 

TIA for your help!

Well you didn't say what you tried but usually you can just add it with an 'and', and maybe some parenthesis:

(First filtering parameters)

and

second filtering parameters.

I'd agree with Steve. you probably want

Let ( [

indexValues = List ( TEACHER::_kplt_TeacherID ) ;

searchValues = List ( TEACHER::Name_LF__lct ) ;

index = ValueCount ( Left ( indexValues ; Position ( ¶ &  indexValues  & ¶ ; ¶ & TEACHER_X::_kplt_TeacherID & ¶ ; 1 ; 1 ) ) ) ;

prevValues = LeftValues ( searchValues ; index -1 ) ] ;

IsEmpty ( FilterValues ( TEACHER_X::Name_LF__lct ; prevValues ) ) )

and

( Status__lxt = "ACTIVE" )

Does that work OK for you?

Edited June 21, 20178 yr by rwoods

You have titled this as "quick portal filter question", but I don't see anything "quick" about it. The method you use (taken from here) relies on a list of all related values in order to remove the duplicates. If the relationship does not exclude non-active records, they too will be included in the list. You cannot use such list to filter only active records. The idea here is it pass a value only if it's the first time it appears in the list - but the first occurrence may have been a non-active record, so this becomes utterly unreliable.

The simplest solution, IMHO, is to modify the relationship so that it only includes active records. Otherwise you would have to find an alternative way to produce the list - e.g. by a recursive custom function. Or devise a completely different method. None of these will be "quick".

 

Thank you all! Steve and RWoods: I tried what you suggested but it didn't work, originally; however, when I changed the T.O. to the self-join table--instead of the "parent" table--it worked.

...but...

In a case of not seeing the forest through the trees, Comment's wisdom shines through and lights the way again: that making what appears to be a quick fix to solve the problem really doesn't solve the problem (or could make things worse when you expect data to appear where it should but it doesn't or it's wrong.) Thanks too, Comment, for inserting the hyperlink to the filter--my apologies to everyone for not including it in my original post.

Edited June 21, 20178 yr by WF7A