I am testing a simple base approach which has three tables: Clients, Contacts and Jobs.
The clients table has linked records to contacts to denote all the contacts that we work with at that client. And linked records to jobs to denote all the jobs we are working on with that client. A client can have multiple linked contacts and multiple jobs, but contacts and jobs can only be linked to one client.
When I'm creating a new job record, I'd like to select the specific contact at the client who represents the lead contact for that particular project.
I realise this can be achieved by a simple linked record field to the contacts table. However, when selecting from the contacts table, this shows all contacts regardless of which client they are at. Which theoretically means it would be possible to assign a contact from a different client than the job is for.
What I was hoping was that there was a way to filter or add conditions to the linked record options so that when I go to select the contact, it only shows contacts who are assigned to the same client as the job is for. I'm open to this being done through a very different approach if it needs some backgrounds formulas etc to achieve this - if it's possible at all.
I'm attaching screenshots of the three tables to demonstrate what I've described. Thanks in advance for any guidance.
