Many to Many question

[JImbob]

I want to set up a many to many database and I have followed the example in the help to set one up.

My situation is slightly more complicated and I wanted to find out if it is possible.

I have a number of animation SHOTS that I am working on from a particular project. Each shot will be sent to the client several times during its refinement.

Approximately once a week a tape is prepared containing the shots that have been updated since the last tape.

These shots are labeled by the version number that increases each time it is compiled on a tape.

So each SHOT can appear on multiple TAPES (with a different version number) and each TAPE can contain multiple SHOTS.

I think I need to make a SHOT.file a TAPE.file and a JOIN.file

For functionality I would like to be able to enter information in the TAPE.file where I can select records from the SHOTS.file in a drop down list. But in the example I followed in the help files I was only able to edit the JOIN.file

Am I going the right way for this type of system?

[CobaltSky]

Hello Jimbob,

What you are working towards is certainly possible.

In fact if you wish you can do what you require without the 'join' file. This is done by the use of 'multi-key' fields in your SHOT and/or TAPE files.

A multi-key field is a key field for a relationship. It is a text field which has more than one value in it (separated by carriage returns). The relationship then simulteneously locates records in the related file which match any of the entries in the key field.

Thus in your SHOT file, you could have a multi-key field for Tapes, in which you store a list of all the tapes that include that shot (you'd then be able to create a portal in the SHOTS file where all the related records from the TAPES file would be viewed.

Then from within the TAPES file, a relationship back to the SHOTS file should pick up all the shots that have a reference to a given tape (using the same key fields to match on, but with a relationship working in the opposite 'direction').

To make it easy to manage, you might wish to create a 'view all' portal (based on a constant relationship eg a calculating field in both files with a formula set to "1" and used as the basis for a second relationship) on a special layout in the SHOTS file. You could then locate the tapes for a given shot and set the portal up so you can click on a given shot to add it to the list (in the multi-key field) for that tape.

This approach should achieve what it seems that you want, but with slightly simpler and more direct methods and file relationships?!

[Kurt Knippel]

Multi-key fields are really just little join files. The benefit of them is that they are easy to setup, especially for one-to-many relationships, but more difficult to manipulate for many-to-one relationships.

In this case a join file really is needed, otherwise you will end up in scripting hell trying to deal with the multiple multi-key fields needed to keep things straight.

At its simplest the join file needs two fields: TapeID and ShotID. However you may need some additional related calculation fields for reporting purposes.

[CobaltSky]

No fundamental disagreement here.

Just depends on one's definition of 'scripting hell', I guess.