Relationships, Portals and Lookups
Apologies if this has been answered in a previous post. Im relatively new to Filemaker and have searched but I think a lack of knowledge is preventing me from understanding whether the scenarios described should also apply in my specific situation.
I have a single database with two tables: The tables are "Projects" and "Timesheets". The Projects table lists out the current projects being undertaken and all supplementary information relating to each project. The Timesheets table is a form in which staff will input their activities and time against specific projects.
Each timesheet has a discrete "Call ID" field. Likewise each project has a specific "Project ID" which is why I've stored them in separate tables. When recording time against a given Project, the Timesheet form has a field called "Project Name" which is used by the staff to decide which Project they should be recording their time against. I need the "Project Name" field on the Timesheet form to Lookup the list of projects from the Projects table and return those records as a drop down list. Ideally, I would like that drop down list to filter against an additional field in the Projects table and only return those records that have a "Project Status" of "Running".
Whilst initially I thought a Lookup might be the right way to do this, I need to ensure that if the name of the project changes for any reason that the Timesheet forms associated with that Project are also modified with the new project name.
Im not really sure how to achieve this as initially I related the tables so that each table had a "Project Name" and a "Project Status" field which were matched (=). But I found that wasn't returning the data I needed. I've tried global storage and that doesn't achieve what I wanted and also I gave Portals a try but that only seemed to work if I was also doing a Lookup and the form wasn't working the way I wanted. Really Im not sure where to go from here as I seem to be going round in circles and unfortunately whilst my knowlege of relational databases is probably insufficient, at the end of the day I need to get this working.
Really appreciate any help that could be provided.