multiple fields in one table that fetch the same data from a related table
First off, I am learning FMP (version 11 for Mac) on the fly with no formal training, so any answers would have to be a little basic. I can accept, "this is too complicated for your level" as an answer and I'll move onto something else for now.
I am using the 'FM Starting Point' database for managing my sole proprietorship. I've been able to do a lot of modifications to adapt this database to my needs, learning a lot about how FMP works. I've thus far been able to figure out all my own problems, but now one has me thoroughly stumped.
This database has a timesheet table that didn't work for my needs. I created what I wanted in Excel in order to figure out what fields and calculations I'd need, then I recreated it in FMP.
As a freelancer, I have three types of timesheet entries, per audio minute, per hour, and piecemeal. So my timesheet has those three sections (see the attached image with a dummy record). For all three sections, I want to be able to relate an entry to a project, account, and contact, which are in turn related to invoices. So for each section, I created a field related to the project, account, and contact tables that pointed to the field from which I want to get the data.
The problem is that even though I created three fields, they all act as one. If I put in a project name under the transcription section, the same project name will autopopulate to the other two sections.
Each project, contact, and account record has a category that matches my timesheet sections, so I'm thinking there might be a way to say that for each field I only want the account, contact, and project names that match a certain category, but I can't find a way to select two fields when creating the relations.
The only way I can think of to make this timesheet work for me is to put the sections side by side and only have of each of the contact, account, and project fields, but then I wind up with a table that is much too wide. So I'm hoping that keeping it in three sections is doable.