Linking one record to many

[RowN]

Hi all,

I'm very new to this and trying to build a database for film and video editorial management. At the moment I have built a database (based of a starter solution for the moment) to track every shot and where the assets for that shot have come from.

What I want to do is have a separate table that has the project information in it that I can enter in once and it will appear on multiple tables or databases.

I am having trouble with the relationships between the two.

I am attaching the file for reference in case it is easier to take a look.

Cheers,

Ro

EditorialManagementv1.zip

[comment]

track every shot and where the assets for that shot have come from.

I am not sure what you mean by "assets for that shot". Isn't a shot a type of asset?

It's also not clear how you associate assets with projects. Does every asset belong to a single project only?

[RowN]

Hi,

Thanks for replying.

Each shot has at least two assets. An audio file and a video file which then need to be synced up to create the shot. On the asset management database I have I have an entry point for both those pieces of information on the one form.

There is some overall information that only needs to be entered in once, such as the project name that I would want to appear on all records and on different tables (such as a scene list management table). I should note to be clear I would have a complete new database based off this template for every new project, that's why I only need to enter it once in this current database.

Does that help?

[comment]

Each shot has at least two assets.

Can it have more than two?

I should note to be clear I would have a complete new database based off this template for every new project

That doesn't sound like a good idea. For example, you would not be able to search for a shot in other projects. But at least you have answered the question about how a shot is related to a project. Can you also explain the purpose of the Scene List table?

[RowN]

Can it have more than two?

Yes, it can have as many as one particular shot needs from video, sound, VFX, sound effects, etc. To begin with I would primarily be using it for raw footage coming in from set.

That doesn't sound like a good idea. For example, you would not be able to search for a shot in other projects. But at least you have answered the question about how a shot is related to a project. Can you also explain the purpose of the Scene List table?

The scene list would then be a combination of various different elements again such as: scene description, characters, elements needed for the scene (which could possibly even link back to the asset management) etc. It's a step up in the progression. The scenes are made up of the assets.

But getting back to the original question is there a way that the Project info can be entered in once and appear on multiple entries in both lists?

Thanks for your help.

By the way, the reply to your first question your last post seems to be caught up in the quote link above for some reason.

[comment]

Yes, it can have as many

Well, then you should have separate tables for Shots and Assets, arranged in a one-to many relationship. IIUC, it's a actually a parent - child - grandchild block:

Projects -< Shots -< Assets

Have a look at the demo file here for a suggested way to handle this part:

http://fmforums.com/...678#entry318678

The scenes are made up of the assets.

Is it correct to say that one scene can have many assets, and one asset can be used in many scenes? If so, you will need a join table between the two.

[RowN]

Hi,

Thanks for that link. I've been working through a version of that and I think it's clearing up some stuff for me.

Is it correct to say that one scene can have many assets, and one asset can be used in many scenes? If so, you will need a join table between the two.

No, the assets will be only used in one scene. I'm working on a version of the link you posted above that might help put it into perspective for you too. Explain what I am trying to do in a more simple detail. I think I just bit off more than I could chew and needed to get back to the basics of FMP before I get too ahead of myself.

Thanks.