I'm new to Filemaker, but I've transferred data over from an old MS Access database and now I'm trying to set up some useful relationships.
The database is mostly about capturing information on external grants applied for and won, and so I have a project table set up which captures project title, funder, total value, which stage of the bid we're at, the status of the contract, the start and end dates of the project etc etc. The tricky bit is around the staff members. Each bid application has a PI (principal investigator) who is responsible for the bid from our organisation's point of view.
I've set up a second table for staff, which shows their name, their email address, phone number, monthly salary, full time equivalent (FTE) and their job title.
By making the staff table the parent, and the project the child, I have created a layout based on the staff table which shows all the grant bid applications (in a portal) a staff member has made, which is great.
Now I also want to list a project team for each project, as a portal within a project table-based layout. I thought I'd achieve this by creating a join table which shows the FTE and project team role for each named staff member for that particular project, and linking it via one-to-many relationships with the project table and staff table. But firstly, that has thrown up a message in the relationship window that I can't have two sorts of relationship between the tables and that I need a new Table Occurence. Fair enough: as long as I don't have to input the same info into each occurence.
For each project, the database populates the staff team portal with the PI. I can't tell whether this is a good or bad thing considering what I want. If I add a new staff member to the project team portal, will the database think that they are also the PI? Will this ruin everything? It's true that the PI is a member of the team, but I'm really not sure whether I'm doing the right thing here.
One of the reports I want to run would show staff members, and all the projects they are committed to working on in a portal.
Please can someone help me figure out the logic to what I need to do with the tables and relationships?