DB Design Issue and Importing
FYI: I'm usong FMP Advanced. I will likely upgrade to the Server version once I have this design properly done and running.
I'm in the door business. We sell doors to contractors for specific projects. Each project needs submittal information, orders, etc. There are several users, several tables and many forms and reports. Any one user will only work on one project at a time (session) but more than one user could be working on the same project at the same time. I understand about record locking, etc.
I've been displaying and manipulating data by specific project info by using found sets. Is this the best way to handle this issue of working within "projects" from the beginning? Does the DB Manager offer a better way? Should I be importing projects for each session? It seems like this must be a common issue, but I haven't been able to figure out a simple solution. Most of our data is best displayed in lists. Portals are OK but don't always work for what I need.
My DB design now has several tables that have all the records for all the projects. When users begin, they select the project from a list and that projects number becomes a global variable. For each layout they open, a script creates a found set to display just the records for that project. This is becoming cumbersome as I need to create a new script for each different table or TO. A bigger issue is importing records (other FM records from the same DB). Some tables are populated by importing data from other tables. Some of that data is then changed for vendor order forms. (again using import techniques) These are sometimes temporary and also involve deleting records - usually found sets. Having to import all records in my DB just to display the ones I need for one project seems inelegant. Also, deleting found sets makes me nervous. There's probably a way to import just a found set, and I'll want to know how to do that, (or import with criteria eg "only project number 1234") but it feels like I'm taking the wrong approach from the start. The DB with eventually have tens or hundreds of thousands of records in some of the tables.