Animation Art Database - help needed

[tenaciousg3]

Hi All,

I'm fairly new to Filemaker, and this forum, so please go easy on me! I searched the forums for over an hour before posting this, and some things seemed to fit, but it's such a specialized application it's tough to get the full answer I need. Hopefully you out there in Filemaker land can help. It's a bit tough to explain so I hope I make sense:

I'm creating a database to track art created for an animated TV show. For every piece of art There is one line version and then multiple color versions of the same piece of line art that need to be tracked. Each piece of art will be used multiple times throughout the course of a season and we need to not only track the art created but also which episodes, act and scene numbers they appear in, the artist that drew the art, the colorists that colored the art, what color versions exist for each piece of line art and the dates these pieces were assigned, completed, shipped, etc. Lots to cover, I know.

I've already gotten pretty far in creating my tables, fields, and relationships. I've had some help setting this up already and I "think" I've got it set up correctly, but there have been some problems as my users start to actually enter data. Hopefully you can offer some insight into how I might be able to fix some of these problems. I've attached a screen grab of my relationship graph so you can see all the fields and tables.

The relationship between Line art and Color art allows creation of records in both directions, but deletion only on the Color Art side. The relationship between Color art and usage allows creation of records in both directions, but only allows deletion on the usage side.The relationship between Episodes and Usage only allows creation on the usage side, and allows no deletion on either side.

I have some specific issues that are coming up, but this message is already so long I figured I would start by getting your opinion on the relationship graph and if you see any problems there first.

ANY help you can give would be greatly appreciated.

Thanks!

-George

[David Jondreau]

Don't be shy, ask your questions!

[tenaciousg3]

OK you asked for it!

1. When determining what models we need for a show, we start with the script, so I have a "script breakdown" layout based on the Usage table. On that form are episode number, script page, Design name, Category, VariationName and notes fields. That seems to be working just fine for the most part, it's creating line art records which we fill in the blanks for later. The problem occurs when doing a find in the breakdown layout using Episode Number as the find field. The layout will correctly list all the models we've entered, but when listing a re-used model from a previously entered show it always puts the incorrect episode number in that field - it puts in the number used when the record was first created, NOT necessarily the number for the show it's being used for. For example, if the record for a piece of art was first created for episode 101, and then we use it in 106, if I do a find for all models in 106 it shows up on the list, but with a 101 in the episode number field. The same thing is true when it shows up on other layouts, like an art paste-up page. I do a search for all models in 106 and the record shows up but with 101 in the episode number field.

One thing of note, I needed to create an "Episode Number Lookup" field under usage. I do NOT want it to create a new record for episodes every time I create a usage record, so I needed to turn that option off in the relationship options, otherwise it wouldn't let me enter anything into that field.

2. My episode titles are not appearing on my layouts. In the Episode table I have 3 fields, Episode Number, Episode Name and Studio. I already explained how I had to use the Episode Number lookup so I could enter the episode number in the Script Breakdown layout without it creating multiple episode records. I need the Episode Name to be on several layouts, like the art past-up page and various reports, whenever a new usage is indicated. I tried simply putting the Episode Name field in the layout but that didn't work, tried creating a lookup field like I did for Episode Number, and nothing - it's still blank. Do I need to do some kind of calculation linking the Episode number with the title and the usage record somehow?

I'm having all kind of little glitchy things like this. I'm sure these are simple fixes (at least I hope they are) but I can't seem to figure it out. I suspect there might be something wrong with how my tables are linked, but I'm at a loss. Any suggestions?