Product Database

Discussion created by trxi on Dec 4, 2017
Latest reply on Dec 4, 2017 by techt



Reasonably new to FM.  I've used it for a couple years and have always made databases which help me perform tasks related to my various work output.


I've searched the site, youtube and downloaded a variety of FM files posted on this forum to look how they're put together but I'm still stuck.


I'm trying to build something on a larger scale now, which would be a parts & assemblies database logic linked to where the parts are supplied from.


I've broken down my structure in this way



-Vendor Type


-Product Category

-Product Type


The above tables have very few fields.  Just basically ID fields, and a Name field with the lists.  The Vendor field has more information related to contact info etc for that vendor.


Now here is where I'm confused.  I made new tables for each of my products.  Each product has about 50 sub-types




Vendor Type 1, Vendor 1, Product Category 1, Product Type 1, would be the lead in structure


Then my table X for the product X.  It would have 50-100 records.  There is a lot of individual performance data I need regarding each product, so I did not think it was appropriate to put all products in one table as I would have hundreds of columns.  Also, when new products are entered, I'd like the selection to be inputted where they came from (vendor type, vendor, product category, product type).


On the various product Tables, (Product 1, 2, etc) I have successfully figured out how to conditional value format lists, so I can select vendor, product type etc from the categories.  Looking at my relationships though, they seem overly complicated for the task.  I based the structure on youtuber Guy Stevens Filemaker Pro - Multiple Levels of dropdowns - YouTube


Im having trouble because I now want to build assemblies, with the various products.  I cant figure out how to read data from all the various products without specifically linking fields to named tables? 


I thought maybe I need to have a "master product list" table, where every time one record is made in any product, it adds that product name/price etc into this master list, then gives it a company product code?  I can then reference this in the builder.


Can anyone provide assistance?


My end goal is to have the assemblies table have a selection of 10 parts, where I could build an assembly from any product from the various product tables, and the assembly will spit out the price etc. 


Then also on the vendors page, have it show a list of all products they make/supply to me, which can be sorted by category and type to narrow the records.


Attached is my relationships.  The springs/product index relation does nothing, that's me playing around trying to figure out how to make this work.  Attached is also example assembly page started.  Id like to eventually have 10 components into the assembly builder.  Then have it give that assembly it's unique ID.


All products, including assemblies I think should also be assigned a unique companyID, which I was trying to figure out with the productindex table.