How to get non-matching data through a many to many relationship

[WonkoTheSane]

Here's a relationship / data retrieval question that I need to ask by way of example, so please bear with me.

 

I have three tables in a system that works like an online blog. They are Posts, Tags and a join table that links the two in a many-to-many relationship - Post_Tags. Naturally a Post can have many Tags.  Question: how to I get all the Tags that have NOT been applied to a particular Post?  Ultimately, I'd like the data to show up in a portal, but I'm not even sure how to structure a relationship (or even a raw query) to get it.

 

Thanks much for any suggestions.

 

 

[comment]

Define a calculation field in the Posts table =

List ( Post_Tags::TagID )

Use this field as the matchfield in a new relationship to (another occurrence of) the Tags, table, using the ≠ relational operator.

 

 

Note:

This will not work for a post that has no tags at all; IOW, the matchfield cannot be empty. The solution to this is to make the calculation field contain a "dummy" value in such case, for example =

Case ( Post_Tags::PostID ; List ( Post_Tags::TagID ) ; -1 )

See also:

http://fmforums.com/forum/topic/48666-matching-values-in-separate-repeating-fields/#entry227334

[WonkoTheSane]

Wow, you are the master. Many thanks. This does indeed work.  I had no idea that when building relationships you could use an integer on one side of the operator, and a list on the other.

 

Some details for anyone else who wants to try this solution (at least in v13):

 

1) When setting up the calculation field in the "Specify Calculation" window, you must uncheck the option "do not evaluate if all referenced fields are empty"

2) incredibly, this works if the calculation result is set to number, OR to text.

[comment]

Uhm .. . the result of the calculation field should be Text. Sorry for not making it clear from the start.

 

 

I had no idea that when building relationships you could use an integer on one side of the operator, and a list on the other.

 

That's not exactly how it works (that is, the "integer" part is non-essential) - see the note about a multi-key field here:

http://www.filemaker.com/help/13/fmp/en/html/relational.11.4.html#1027684