Portal showing records across table occurences

[Dangerous Lee]

Hello,

I'm the classic "guy at company who was asked to make a database and the expectations have gotten out of control.." etc etc.

I've developed and populated a shot tracking database for VFX on a film. My principle entities being:

Shots

Elements

Assignments

Artists

A shot is comprised of several elements. I do not have a join-table and...unfortunately, instead of using a portal I, in my inexperience, used an interface with 6 foreign keys connected to 6 occurrences of the "ELEMENTS" table to allow a user to import elements into the shot tracking.

That much of it works just great. What I haven't been able to do is form a portal in an Elements layout which will display all the shots which contain that element in any of the foreign keys, or any of the table occurrences.

I've been back and forth in my head about ways around it, parsing the names into a list and having a calculation which searches that list, but those all seem to contain retroactive work, as well as the simple fact that I don't know how to implement.

Any ideas?

-Lee

[bcooney]

If there is a many-to-many relationship btw Shots and Elements, then you need the join table.

View the join table from Shots, and see the Elements that each Shot has.

View the join table from Elements, and see the Shots that use that Element.

The 6 foreign keys "whatever" has to go. It'll haunt you forever that the structure is wrong. Rework it.

[Dangerous Lee]

Thank you for the reply.

I know this is annoying, but sadly, the production schedule doesn't allot me enough time to make significant retroactive steps. The next db will be made with adequate forethought, but I didn't know enough heading in for that to be possible this time.

I'm trying to work out a script I will run to populate a join table based off entries in the multiple IDs in the current table. Not sure if that's going to work, and it may well be a bad idea, but the alternative is simply limiting expectations for the variety of reports I can run.

Thanks for the answer.

-Lee