one record related to multiple records

[Benet]

i have a database that relates stock items to jobs. mostly the stock items are individual and have as specific "serial number" hence are individual records. In this case if a stock item is related to a job i simply fill in a jobID field in the stock item record and the relationship is made. However some stock items are multiple, like "10mm washers" for example. in this case i reduce the stock quantity to match how many have been used on a particular job. the problem comes when i go to write the relationship. As i only have one jobID field in the stock item record, when i go spread the washers across several jobs the jobID is overwritten and the relationship with all but the most recent job fails.

Is there a way of using a repeating field or merge field in some way so that a stock record can be related to multiple jobs.

Or, maybe i need to look at this another way ?

I find myself wishing relationships had an "or" function as well as "and"

Currently the relationship that binds stock to jobs is stock:jobID = jobs:JobID

Thanks

Ben

[comment]

Relationships do have an AND function: if you write multiple JobID's, separated by a carriage return, into the foreign key field in StockItems, that item will be related to all of the listed jobs.

However, this method is very limited, because you cannot assign individual quantities to specific jobs. The correct way is to create a join table with fields for JobID, ItemID and Quantity.

[Benet]

Thanks, the carriage returns works perfect, im not sure how i got this far into the filemaker without it.

However of course you a quite right i cant seperate out the quantity.

Could you expand a little on "join table" would it only contain those fields ?

ben

[comment]

A join table (or 'associative entity') is the standard way to facilitate a many-to-many relationship in a relational database (not just in Filemaker). At minimum, a join table needs the two foreign keys to its "parent" tables - but it can have other fields to describe the attributes of a specific join, such as Quantity in your example.

See a basic demo here:

http://www.fmforums.com/forum/showpost.php?post/246136/