I am very new to file maker but have a background with MS SQL so I am building out a database using the trial version of 17 to see if this will fit my company's needs since it has an easy to use & customize front-end.
I am attempting to build a pseudo-inventory tracking system where equipment can be assigned to locations.
The intended workflow is that the end user assigns equipment to a location in a hierarchical drop down workflow that flows Category > Manufacture> Model.
The Category drop down drives what Manufacturers show up in it's dropdown list based upon if any item from that manufacturer has an equipment piece under that category, and models show up based upon the previous two selections.
The Category, Manufacturer and Model data are all housed on a master Equipment table (the Category and Manufacturers are house in their own independent Check tables and referenced to from the Equipment table).
I understand how to get the lists to drive each other (although it's irritating that you can't use a query for this), but can't get the table side figured out within the system.
So my problem is that I am trying to build what would function SQL View or a relationship table that is rebuilt for any new piece of equipment is added to the master record list - ordinarily for the table approach I would add a trigger to the table to run a stored procedure to truncate a temp table and insert distinct results for the category and manufacturer, and have all the related tables reference that relationship, but because of how those above me want the system to work, it needs to be a self-contained file and can't reference an external data source.
How would the filemaker pros on here go about solving this?
Attached is a screenshot of my existing table relationships.