Need Suggestions...Relationships, summaries, etc...

[Jeff M]

I've been trying to post this question for a while, but I can't figure out how to explain it...so I'll just give it a try...I hope it makes sense.

I'm in advertising. I build pages for ads & catalogs. To do this I'm given a SKU list, which contains all the possible SKU's for the ad/book. Every SKU has category and brand managers assigned to them, which is also listed in the SKU list. Once a page is built by my team, I'm given a page report, which is a simple list of the SKU's used on that page.

Here's where it gets complicated...

I have to provide PDF proofs via email to those category and brand managers for pages on which they have SKU's. My client wants all category and brand managers for the page included in a single communication. They also want pages which share the same category and brand managers to be sent out in groups. So, essentially, I need a way to know which pages share the same groups of category and brand managers.

Example:

Page 5: category managers are John, Mary & Bob. brand managers are Harold & Sydney.

Page 6: cat managers are John, Mary & Bob. brand managers are Harold & Jack.

Page 7: cat managers are John, Mary & Bob. brand managers are Harold & Sydney.

Page 8: cat managers are John & Mary. brand managers are Harold & Jack.

Pages 5 & 7 can be grouped and sent out together because they share the exact same group of category and brand managers. 8 can't go with them because it doesn't contain Bob, and because it has Jack in place of Sydney. 6 can't go with them because it has Jack in place of Sydney. You get the idea?

So, here's what I've done thus far...

I have a table for SKU data. Essentially it has fields for SKU, category manager, brand manager.

I have a table for page report data. It contains the page number and the SKU.

Finally, I have a page table, where I keep a list of all pages in the ad / book. Each record represents a single page. I use this record to log when the proof was sent, when it came back, etc.

I have the page report table related by SKU to the SKU data table, that way I can see what SKU ended up on what table and which category and brand manager own that SKU.

When I'm looking at my page report table, I search and find records for page 7. The first 2 SKUS belong to CM: John and BM: Harold. The next SKU belongs to CM: Mary and BM: Harold. The next 2 SKUs belong to CM: Bob and BM: Sydney. The last SKU belongs to CM: Mary and BM: Sydney. So, my final list of Category Managers is John, Mary & Bob. My final list of Brand Managers is Harold & Sydney.

So, once I get the page report data in place and the SKU data in place...how do I quickly identify pages set to go to the exact same list of people?

Perhaps there is a fairly straight forward solutions I'm not thinking of? All the ways I can think to get there feel sloppy and overly complicated. I could use some advice or suggestions. Thank you so much!

[comment]

IIUC, you have:

Pages -< PageReport >- SKUs

If so, you could define a calculation field in Pages =

List ( List ( SKUs::CM ) ; ¶ ; List ( SKUs::PM ) )

and use this field as the top-level breakfield in your report.

[Jeff M]

Didn't know about the List function, very nice. Is there a way to limit the list to unique values only?

[Fenton]

Since you have FileMaker Pro Advanced, you can use a Custom Function. Go to

http://www.briandunning.com/filemaker-custom-functions/results.php

Search for "unique". There are a few there; I think "UniqueValues" would do.

Within the calculation, get your list first, then put that into UniqueValues().

[Jeff M]

It's interesting you point that out. I have FileMaker Advanced 9 at home, but on my work computer where this file will be used, I have FileMaker 10, non advanced. Will custom functions added in FileMaker Advanced 9 be honored when moved to a non-advanced version of FileMaker?

[comment]

You could also add a People table, and point the List() function to get the list/s from there. That would be unique - unless someone is both a CM and a PM. Or define a value list and use ValueListItems() instead.

A custom function must be installed in Advanced, but it will work in the Pro version too.