I have three tables:

STORES --< PRODUCTS >-- CATEGORIES

Each store can hold many products (one-to-many relationship). And each product belongs to one of several categories. Several products can share the same category.

So, for example, the "Main Street" store might have the products "Frog Fountain", "Copper Birdbath", and "Lion Fountain" in stock. The "Frog Fountain" and the "Lion Fountain" are each assigned "Fountain" category. The "Copper Birdbath" is assigned the "Birdbath" category.

When I'm on a STORE layout, I'd like to see a list of products in stock. That's easy: I just create a portal between STORES and PRODUCTS.

However, I would like to create a second portal on the STORE page that shows a count of the number of each products in that store for each category.

So, for example, on the page for my Main Street Store, I'd have one portal that showed the items in stock:

Frog Fountain

Copper Birdbath

Lion Fountain

That's easy, but I'd like a second portal that shows the number of products in each category I have in stock. So, for this store, it would show (for example)

Arbors - 0

Birdbaths - 1

Fountains - 2

Gazebos - 0

Seems easy enough, but I'm wracking my brain! The STORES and PRODUCTS tables are linked by a STORE ID, and the PRODUCTS and CATEGORIES tables are linked with a CATEGORY ID. However, I don't know how to isolate and count ONLY the number of products in each category that are in that store from a store page.

I realize I can do something similar to this in a subsummary report, but I want to be able to interact with the data (click it to run scripts, edit fields, etc) and I understand you can't do that in reports.

Anyone able to guide me in the right direction?

Thank you!

Edited February 10, 200817 yr by Guest

but I want to be able to interact with the data (click it to run scripts, edit fields, etc) and I understand you can't do that in reports.

This kind of interaction still comes in stages, you can't really make a trial and error ... the thingy doens't refresh like a spreadsheet. It could look like you're pulling the tool out of its realm.

But this kind of do it, when the users are cautioned about the refreshing issues:

http://www.fmforums.com/forum/showpost.php?post/266487/

--sd

Thank you, Søren!

I actually thought this would be easier!

At any rate, I spent some time scrutinizing this this morning to see if I can make it work for my case. I think I can, but I am worried about refreshing issues, as you mentioned.

I'll go down this path, but if there are any other ideas out there, let me know!

Tony

It's not particular hard to track changes, although a plugin is required:

www.productiveit.co.uk/files/Script_Trigger_on_Change_Records.zip

...utilizing:

http://www.zipptools.com/

--sd