database for construction reports and remote users
I started to post a long explanation and several questions and just got myself even more confused so I am starting over. New to Filemaker Pro 12 as you will see. I am creating a database for construction reports. There are five construction projects (subprojects) in the main project and four construction reports. I saw this as a many to many relationship as each subproject will have its own reports from the four available. Each subproject will have a unique name, number, city and state.
There will be five inspectors and each will complete a daily report on their subproject (or possibly one of the other subprojects) and complete the other reports such as non-compliance if needed.
All the reports will have some fields in common, subproject name, number, city, state and date of report as well as time of report at the top.
After that, the information in each construction report will vary depending on the type of report.
Filemaker server will be installed and host the date. Four computers in the office will have Filemaker Pro 12 for entering additional data and printing reports. The Inspectors will have iPads, Filemaker Go and GoZync and connect remotely to upload reports.
I created a table named InspectionReports and crated fields for the common subproject data (name, number, city and state) and tables for the other common data such as inspector name, date of inspection and created a primary key for it. I envision the data being entered from this form locally and remotely but am confused. I think this is a join table but I may be looking at the database relationships wrong.
I created one table for the subprojects and crated fields for the common subproject data (name, number, city and state) but wonder if I should have created five tables, as each subproject is unique?
I created five tables for the reports that will contain the data that is unique to each report type.
I would like the data to be automatically entered for number, city and state once a subproject name is entered to reduce the chance of errors. The inspectors will be new to databases and iPads (even new to computers for the most part).
I am totally confused as to what types of keys I need where? A primary or foreign for each report table? A primary for InspectionReports? Can I use one Subprojects table and lists for the subprojects or do I need five tables and does each get a primary or a foreign key?
Sorry to be so long but wanted to explain as best I could. Thanks to all that have the patience to read all of this and a special thanks to anyone that can help me out. One of my concerns is that the iPads will not be connected directly to the database more than once a day and sometimes perhaps not for several days so they need to have all the common information stored locally and protals are not an option.
i feel like I am making this a lot more complicated than it has to be and have been struggling to find the answers for several days. I have created at least a half dozen databases and not been able to get any of them to work that way I have described.