Regarding m2m queries, sorting and categories..

[Heronmarked]

Goodday all.

This is my first post to this forum, but it's helped me through some problems I've had developing in Filemaker for quite some time now, so let me start of by thanking you all for your contributions that have made live easier on me so far.

Now however, I have come across a situation I haven't found described in this forum anywhere, and I am at a bit of a loss.

The situation is as follows:

I am creating a contact database, my goal is to categorise contacts into various dynamic categories.

This would mean, as one contact can have many categories ( ) and one category obviously has many contacts and the categories also need to be editable, a many-to-many relationship is needed.

So I set up a database named categories. I create fields CatID and CatName (and whatever else, like CatDescription or something).. My contacts DB of course has a field named ConID, so I create a Cat-Con joinfile. I mirror whatever fields I want to show on a portal in the joinfile, and there I have my relationship.

I would like to use a drop-down list in the edit-contact layout (on the contact DB). Seeing how the categories DB is 2 steps away relationally, I will not be able to populate a valuelist with the created categories. This is problem number one.

The way I create relationships between one-to-many databases (in this example "company" and "contact") is to relate the company_name field as well as the ID field so I can fill the company ID with an automatic lookup on the company name. This requires the name to be unique, but this is not a problem.

As far as I can see, this technique will not work in case of a many-to-many relationship, as Filemaker cannot relate deeper than one database. So problem number two is: how to make the valuelist in problem one relate the entries as required?

I hope some of you can shed some light on this.

Thanks in advance for your help.

[BobWeaver]

In this case there is probably a simpler solution without the join file. You can use a multi-key in the contacts file. In the contact file create a field called Categories. In it you can place the Category ID for all categories to which the contacts belongs. Separate each ID with a carriage return, and then set up a relationship between the category ID field in the Categories file and the Categories field in the contacts file. This will create a many to many relationship using just the two files.