Can evaluate work with a related field?

[jwnacnud]

I have many products and each product can exist in multiple categories. I have a product table and a category table. I access the category table via a portal. I have many people adding and changing the categories so I need to find out which items have been modified to update the website.

I need to have a checkbox in the products table set anytime a category is added or updated? I have used evaluate in the past but cannot seem to figure it out with a related record.

If there is a better method, let me know.

[bcooney]

Don't you have a join table btw product and category, capturing the combo's since it's a many-to-many relationship?

As for which products have had any of their categories edited, you can set a flag in products by scripting all edits to the join table.

Evaluate does not work on related fields.

[jwnacnud]

What is a join table and how do I use it. I've been filemakering for 10 years and have never used a join table. It sounds like I about to be enlightened.

[bcooney]

If you search the forum for "join table," you're bound to find many examples.

A join table resolves a many-to-many relationship. In your case, each product can be assigned many categories, and each category can be assigned to many products. A join table records the combinations by storing the foreign keys of CategoryID and ProductID, each combo in its own record.

The portal on Products to see its assigned categories would be based on the join table. Likewise, a portal on Categories displaying the related Products would be based on the join table.