Subsummery List Calculation

[littleb3]

I am completely new to both FileMaker and Help Forums so please excuse me if this is a terrible question.

I have been put to the task of making a complicated tracking system for the animation company I just started working for. I might not have set it up the proper way to begin with, but where I am stuck now, is creating a list in a sub-summery field.

Basically, every entry in this table is a item that needs to be designed for an episode (a prop, a background, a character etc). Each item is assigned to an artist to design. I have a sub-summery by episode within which I want a field to be a list of all the artists assigned to an item within that episode.

Since many artists are assigned many items, I want the list of unique artists for that episode (each is only listed once).

I am very new to all this and I have no clue how to make custom functions. Is there an easy way to solve this using the standard Filemaker functions to do a calculation?

Thanks for helping the newbie :

[comment]

I'll make some assumptions here - correct me if these are not true:

1. Each item is designed for a single episode;

2. Each item is designed by a single artist.

If so, you should have the following relationships:

Episodes -< Items >- Artists

(read as: one episode has many items; one artist has many items)

A portal to the Artists table, placed on a layout of Episodes, will list all the artists working on an item of the current episode.

I have been put to the task of making a complicated tracking system for the animation company I just started working for.

Perhaps you should read this:

http://www.fmforums.com/forum/showtopic.php?tid/193440/

[littleb3]

The first assumption is not exactly correct. Items can be used over and over in different episodes. For every episode I add an entry to each item used in it. That record indicates if it is a reused item or if it is new. If it is new then a single artist is assigned to it.

My relationship was set up wrong though. You are correct there. I understand now, I would add the artist via a portal to the artist table. Just like adding products as the lineitems to an invoice.

After it is set up that way though I am still a bit confused. I want to have the list of artists in my sub-summery, am I using some sort of summery or calculation or filter to to list the unique artist to the episode from the artists table?

thanks!

[comment]

Items can be used over and over in different episodes. For every episode I add an entry to each item used in it.

OK, then you would have something like:

Episodes -< Usage >- Items >- Artists

However, this may need improvements, because looking at Artists from the point-of-view of an episode, you'd see all artists that worked on items used in the episode - not only artists that worked on items prepared FOR this episode (i.e. new items). Unless that's what you want.

Another approach would be to separate usage from preparation, and have another set of relationships for the latter. I am hesitating here, because I am not fully familiar with the business requirements.

I want to have the list of artists in my sub-summery.

I am not sure what you mean by that (and it's not because of the misspelling ...). What exactly is the product you are after? Do you want to see the list on-screen, or do you want to produce some kind of a report?

[littleb3]

The relationship you set up with usage seems to make sense in this case for me. It is almost how I have it already but will need to tweak it slightly.

The end product I am looking for is a report. A few of us use the database to track all these things, but most people in the company just want a status report.

In that report, among other things, they would like a list of artists working on the items in the episode and when or if all items have been delivered. My current report is set up with a bunch of calculations to indicate number assets complete. To accompany the amounts of completed items they requested a field that would list all of the artist assigned to something in that episode. Does that make sense?

I think that using a portal to assign to items to an artist will work. I will still need some sort of calculation (I assume within the artists table) to list the artists associated with an individual episode and to have it list only the unique ones. I would then add that to my report.

Thanks for your continued assistance.

[comment]

To accompany the amounts of completed items they requested a field that would list all of the artist assigned to something in that episode. Does that make sense?

I believe it would be better to append another page to the report, produced as a list directly from the Artists table - after finding the artists associated with the episode (this is assuming you report on a single episode at a time).

[littleb3]

I think I understand the mistake in my logic. I didn't realize that since each artist only appears once in the artist table, a portal to that table within the episode table will list them each only once as well.

I thought it would list them multiple time based on however many time they were assigned something, but that would only happen with a portal to the item usage.