Jump to content

Filter to exclude matching records


sal88
 Share

This topic is 2288 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi all

I have a personnel portal (looks up values from the 'personnel table'). When I click a row then the associated personnel_ID is put in to a personnel_IDF field in a new record of a different table (Case_personnel). This second table is also displayed in a portal.

No changes have been made to the 'personnel' table.

How can I get the personnel portal to exclude records where the personnel_ID matches the personnel_IDF field in the Cases_personnel table?

 

Link to comment
Share on other sites

This is difficult to follow. If I am guessing correctly, you want to omit personnel who already have a related record in Case_personnel from showing in the portal to Personnel? That could be done either by modifying the relationship to Personnel, or by filtering the portal only.

In any case, it would help knowing what the current table is, and how exactly is it related to the other two tables.

Link to comment
Share on other sites

Hi Comment

Yes sorry I was wondering myself if it was badly phrased.

The current table is Case and I want to assign multiple personnel to the current record.
These assignments are recorded in the Cases_Personnel table (Personnel_IDF, Case_IDF) which is visible via a portal (via Case_ID/Case_IDF relationship).
I want the portal (Cases_Personnel) to be populated by clicking on a row in the other 'personnel' portal - this portal just lists all records in the personnel table via a simple relationship.

So when I assign a personnel to the case, I want that personnel(s) to be hidden from the personnel portal.

Edited by sal88
Link to comment
Share on other sites

Thanks, that's exactly the effect I'm after, it would be simpler for me to do it via a portal filter though. I've tried this but no luck:

PatternCount ( List ( Cases_Personnel::Personnel_IDF) ; Cases_Personnel_VL::Personnel_ID ) = 0

 

Also, I'm confused as to how the Objects/UnassignedAttributes relationship works in the example you sent. You have a NOT join which somehow treats a single multiline field (cAssignedAttributeIDs) as though it was multiple records (the number being the number of lines in the field). Have I been missing something all these years?

Edited by sal88
Link to comment
Share on other sites

8 minutes ago, comment said:

Why?

It would save me creating a new join, as I already use the personnel portal twice in the same layout (I have all three filtered 'dynamically' as per a search field (one for each). The other two don't need this exclusion rule so just remain showing all personnel records.

 

Multi-key fields - that's quite a revelation. I dread to think of the hassle it might have saved me all this time.

Edited by sal88
Link to comment
Share on other sites

1 hour ago, sal88 said:

I already use the personnel portal twice in the same layout

I don't quite see the need for that - but then I don't see the entire picture.

Anyway, you could filter the portal by:

IsEmpty ( FilterValues ( Attributes 2::AttributeID ; List ( Assignments::AttributeID ) ) )

where Attributes 2 is the occurrence shown in the portal.

 

Link to comment
Share on other sites

I can't work out why it's still not working. I've attached screenshots. As you can see it is not removing from the right hand portal values that are in the left hand portal.

The filter applied to the portal on the right is:
IsEmpty ( FilterValues ( Cases_Personnel_VL::Personnel_ID ; List ( Cases_Meeting_Attendees::Personnel_IDF ) ) )

browse.png

design.png

Link to comment
Share on other sites

Got it sorted using IsEmpty ( FilterValues ( List ( Cases_Meeting_Attendees::Personnel_IDF ) ; Cases_Personnel_VL::Personnel_ID ))

I also needed to refresh the portals.
 

Thanks Comment, helpful as ever!

Link to comment
Share on other sites

This topic is 2288 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.