newbie many-to-many relationship?

[dtruog]

Hello all,

This is my first posting. I'm in the middle of creating a new database for my work, and have had no prior experience with Filemaker Pro. I"m currently using FMP 8 on Mac OS 10.4. I would appreciate any help that you could give me. And please let me know if there are "keywords" for the types of problems I am mentioning, because that will help my future searches on this site.

I work for a commercial film editing house, which means we take footage from Advertising Agencies, and edit them into TV commercials. I am in charge of creating an INVENTORY DATABASE for my company. Someone else created the majority of the database (which includes files for billing, contacts, Work Orders, etc...), but I am in charge of the INVENTORY file. I have asked this other person for help, but they can't quite figure out my problem. I have already created most of my file, but have not been able to tackle this one problem, which I THINK is a many-to-many relationship problem.

Each element we receive into our inventory (tapes, discs, film stock) is given a unique barcode. This element is usually with us for a couple of weeks while we work on a project, then it is shipped back and forth between us and other companies while it is being finished. Finally, we either keep it or send it somewhere else for storage.

My database has to always reflect the Barcode's vital information (contents, type of stock, length, etc...) and also its whereabouts (location and date of movement). So, how do I make a relationship in which each barcode will have multiple movements (shipped back and forth, and we must keep a history of when and to whom it was moved) and each movement has multiple barcodes (usually we ship a couple elements at the same time to a location, and we need to know which elements were shipped with each other)?

Right now, I have an "INVENTORY" file which includes the barcode and all of its vital information. Then, I have a 'MOVEMENT" file which includes movements with the shipping date, method, tracking number, and contents. The match field in my relationship graph is "barcode", which is a unique field. The majority of the files seem to work great. I even have a portal in the "INVENTORY" file that shows the Movement History of a barcode (where and when it has been moved). But there's one big problem:

I can't figure out a way to include multiple barcodes into one movement record.

Should I combine these two fields into one? I originally split my information into two files because I would like to be able to search for elements by barcode, but also search through movements to see what I shipped on a certain day. So the main field in "INVENTORY" is my barcode, and the main field in "MOVEMENT" is my movement ID.

I'm sure that there is a way for me to accomplish this, but my FMP knowledge is a bit lacking. I wanted to resolve this matter on my own, using these forums as guides, but my work needs this database up and running ASAP, so I thought I'd ask my question directly. Please help me if you can. And please let me know if you require more information, because I'm pretty sure that I have forgotten some details.

Thanks again,

Daniel

[stanley]

Daniel:

Welcome to the Forums.

You seem to have a pretty good grasp of the relational model, so this shouldn't be too difficult for you. Your "MOVEMENT" table is a line-item table, and is viewable as such from your "INVENTORY" table. The trick you're after is to make it a line-item in another table, so that you can view multiple "movements" together.

Create a new table, let's call it "SHIPMENTS" (or something like that.) In "SHIPMENTS" you'll have a serial number field (you should always have an auto-enter serial number field in every table), and you could move your date, tracking number, etc. into this table as well, as that data will be common to every "MOVEMENT" record related to an individual SHIPMENT record.

Now, in your "MOVEMENT" table, create a field called nShipmentNumber (or however you do these things), and that is where you will store the s/n of the SHIPMENT record.

This way, your individual MOVEMENT records are releated to their INVENTORY records for their actual metadata, and to a SHIPMENT record, from which you can view every item which shipped together.

As far as terminology is concerned, you could look up "join table" to see the theory behind this type of thing.

-Stanley

[dtruog]

Stanley,

Thank you very much.

I think I'm on the right track now. My MOVEMENT file is the join-table between SHIPPING and INVENTORY, enabling me to have portals within both of those entailing the important info. My only hurdle now is to create a script of some sort that enables an employee to quickly add MOVEMENT records. I'll let you know if I run into any problems with that.

Also, could you please give me the "official" definition for a line-item? I don't quite have a grasp on that yet.

Thanks again

Daniel