merging external fm databases

[AlesD]

Hi,

I have few FM DBs which has same table and structure inside. is it possible to create Master db where I will merge all external tables together to one master table? Solution shouldn't one time solution but dynamic.

[comment]

Solution shouldn't one time solution but dynamic.

What exactly do you mean by that?

[AlesD]

I meant that i don't want to merge data only once. I need live connection because those slave DBs will be changing time to time and then I need update Master DB/table as well.

[comment]

You can show "live" data from other file/s very easily, but showing records from several tables together - as if they were all in the same table - is much more complicated. Why not simply maintain one table for everyone?

[AlesD]

It's little bit complicated I'll try to be more specific. We have databases for every project we are working on. Projects consists of shots and shots consists of tasks(Task carry information to which shot and project belongs). So in each project db is table SHOTS where are specific informations for every shot(image,length,comments,description etc) Now we are planning/shedulling tasks for every active project/shot in MS Project. So in MS Project we have table TASKS of all tasks. This TASK table we connected as external odbc table into FM. Our goal is every artist can find out in one specific FM layout list of tasks when what he must do with some shots info. I would like to connect MS Project TASK table via Shot_Name (which exists in SHOTS table and TASK table) but I don't know how to do it because task can point to different projects that's mean different fm databases. So I was thinking of joinning all projects DBs into one, there make connection with MS Project. Unify our project dbs is not possible.

What is your thoughts?

[comment]

We have databases for every project we are working on.

...

in MS Project we have table TASKS of all tasks.

...

Unify our project dbs is not possible.

I think you have a major contradiction here - because it seems to me a union of all Tasks table is exactly what you have in the external table.

Our goal is every artist can find out in one specific FM layout list of tasks when what he must do with some shots info.

Perhaps they could do this in a layout of the external table. But I suppose you'd want this to also show data from the individual Tasks tables. Ostensibly, this could be done by establishing a relationship between the external master table and every one of the individual Tasks tables - but what will happen when you open a new project?

IMHO, you should make every effort to keep all your projects in the same file, with one table for projects, one for all shots and one for al tasks.

[AlesD]

I think you have a major contradiction here - because it seems to me a union of all Tasks table is exactly what you have in the external table.

You are right here. That's because the purpose of MS Project is schedule all tasks of all projects during some time period, that is why all tasks are present here in one table. In opposite to fm projects db which are project oriented. Scheduling process is made by one person but each project has own production manager

Perhaps they could do this in a layout of the external table. But I suppose you'd want this to also show data from the individual Tasks tables. Ostensibly, this could be done by establishing a relationship between the external master table and every one of the individual Tasks tables - but what will happen when you open a new project?

Yes it is partly possible but I also need to show assigned tasks for each shot in shot table.

IMHO, you should make every effort to keep all your projects in the same file, with one table for projects, one for all shots and one for all tasks.

I'm afraid of it because we run 3-4 projects simultaneously per year. Average number of shots is 300-500. It give us ~2000 shots per year. Each shot could have 10 tasks. We have 50 employees what can access fm db via IWP this could get really slow very soon.

I really appreciate your help here, I'm not so familiar with FM and MS Project so every advice is very helpful for me.

[comment]

we run 3-4 projects simultaneously per year. Average number of shots is 300-500. It give us ~2000 shots per year. Each shot could have 10 tasks.

That's practically nothing.

We have 50 employees what can access fm db via IWP this could get really slow very soon.

That's another thing, but I am not sure the separation makes any difference, because you want them to access a single unified file anyway, don't you? That's how you started this thread. So this file could contain all the necessary data - which would be very simple - or you could try to pipe the data from the individual files into it - which would be complicated at best.