Relationship problems

[alder2009]

Hi There. I have been trying to figure out a solution for this for a day now and thought it would be best to ask.

My database contains a table of plant machinery models e.g. JCB JS130 Excavator

I have a separate table containing all of the "machine makes" e.g. JCB, Caterpillar, Takeuchi

I also have a separate table containing all of the "machine types" e.g. Excavator, Dump Truck, Telehandler

Every record in my "models" table has a reference to a "machine make" ID and a "machine type" ID. These tables are related and its all simple enough.

I now have a table with one record in it, an item. This 'one' record needs to be related to 'many' models.

I have 3 fields in my "item table", a machine make ID, a machine type ID and a model ID.

I have created a second occurrence of the makes table and related my "items" the "makes" through an ID. I have also created a second occurrence of the models table. The models 2 table is related to the machine makes 2 table.

In a nutshell, 'one' item in the items table is related to many models in the models 2 table via a relationship with the machine makes 2 table.

How to I now create this relationship based on the machine type as well?

[Lee Smith]

Automatic message

This topic has been moved from "Community Resources → FileMaker Pro 12" to "Database Schema & Business Logic → Relationships".

[comment]

'one' item in the items table is related to many models in the models 2 table

Are you sure about this? How can an item (presumably a piece a machinery) be of more than one model?

[alder2009]

Hi There,

We sell glass panels for many machines.

I have a table of models, all related to a manufacturer and a machine type. Every model as a field called CabID.

The Cab ID of every model is related to a table that contains CabID's and ItemID's (Glass panels). The ItemID is related to a table of unique glass items. One Item can be related to many models.

I am now setting up a layout where the user can create a new item and then link that item to multiple models. I have set up a separate table for a new item and wish to link this one item to multiple models. Once the user decides to save this item, I was going to use a script to commit the "temporary new item" to the main items table.

There's probably a far easier way to accomplish this.

[eos]

At the moment I'm not sure what it is you want to accomplish. Is it defining a new item and at the same time connect it to a number of models?

[comment]

We sell glass panels for many machines.

...

One Item can be related to many models.

OK, I see. Depending on what you want to do in the next steps, you can either:

• use a checkbox field to select the models compatible with an item;

or:

• use a join table to record each item-model compatibility.

The second option is the more orthodox one, and you will need it if you ever want to produce a report of all compatibilities, e.g. in the form of:

Item ABC

• Model 123

• Model 456

• Model 789

Item DEF

• Model 234

• Model 456

• Model 567

...