Filter to exclude matching records

[sal88]

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?

 

[comment]

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.

[sal88]

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 March 22, 2016 by sal88

[comment]

See if this helps:

PortalToPortalDwindle.fp7

[sal88]

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 March 22, 2016 by sal88

[comment]

5 minutes ago, sal88 said:

it would be simpler for me to do it via a portal filter though

Why?

6 minutes ago, sal88 said:

Have I been missing something all these years?

Perhaps: http://www.filemaker.com/help/14/fmp/en/html/glossary.html#1027937

[sal88]

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 March 22, 2016 by sal88

[comment]

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.

 

[sal88]

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 ) ) )

[sal88]

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!