Thanks in advance for your help on this dilemma! --

MY GOAL:

I am designing what should be a simple contacts database and we need to be able to assign contacts to multiple categories, such that:

1- users can continually add categories

2- users can change the name of categories (e.g. from "donor" to "giver") and have filemaker automatically update this name for all entries that have this category

3- Each contact can have many categories, and each category can have many contacts (obviously)

4- It is easy to get together a found set of records and quickly add them all to the same category.

THE PROBLEM:

I thought I would do this using 3 files - contacts.fp5, categories.fp5, and a bridge file, categoryassignments.fp5. Then I'd display and add the category assignments through a portal in the contacts file. BUT - with a large database, and if it is in a multi-user environment, wouldn't this take too long to search/sort etc. because it has to make the cross-file index... Also, to add large groups of records to a single category would take minutes because I'd have to create a record in the bridge file for each new assignment.

I know there is a solution out there, this can't be a one-of-a-kind dilemma. What tricks can I use?

Thank you so much!

Chris

First of all, if you wish to allow the continual changing of names (something I don't recommend) categories should be referred to by number and have the names just related data. Then changing the name of a category doesn't require and significant action. Changing names on a continual basis will still cause confusion amoung the people using the file.

You can do this without a join file. Just create a text field for categories and add category numbers separated by carriage returns. The actual list of possible category numbers and categories are stored in a separate file. Relate the category text field to the number of the category in the category file. The category names for a contact can be displayed in a portal based upon this relationship.

Finds can be performed by setting a global text field to one or more category numbers (separated by carriage returns), self-relating the global field to the text field storing contact category numbers, creating a relationship between the global and category multi-key text field, and using the "Go to Related Record (show only related records)" script step.

-bd

Dear BD - Thank you so much for your help! Please let me know what I can do to support this service you provide...

I tried what you suggested, and here are my questions:

1 - How can I restrict the search (make it an AND search, not an OR search)? When I perform the "go to related" command, it pulls up any record that has any one of the numbers I put in the global field. What if I want just those records that have category numbers 1, 4, and 5 (all of them), for example?

2 - are there implications with this approach for sorting and reporting that you can think of?

Thanks

You can still do traditional finds on the names or numbers in the portal. It will be slower than "Go to Related Record". As an alternative, you can get REALLY clever and build a compound, mulit-key. The limitation is that you have to know the maximum number of categories you will have. Say you have a max of 99 categories and each item can belong to a maximum of 10 of them. As and example, one contact is in categories 1, 5, 13, 42. You can build a compound, multi-key which looks like:

1

5

13

42

01051342

For the OR search, your global (left side) key is:

1

5

13

42

For the AND search, your global key is:

01051342

There is also the basic limitation of 64k characters in a text field. This comes into play when using relationships for such things as data range searches (you need every data between two dates in the left side global).

As to reporting, the basic logical consistencies apply. If you sort by category, you can't sort a contact into 5 different categories using this method. For this you need the join file in which each record is a contact/category (multiple records per contact).

Contact Steve at Ocean West for ways you can support the form. These can range from helping answer questions to sponsorship. Thanks for asking.

-bd (Brent Dussia)