Using the table relationship to restrict data going from Table A to Table B
I am trying to solve what I thought was going to be a simple problem.
I have a table A which includes an entire psychiatric practice's patient demographics. On a layout for that table, I am able to check a box that indicates which therapists have access to a patient's file. Using permissions, I was able to solve that pretty easily. When a therapist logs in, he or she only sees records she has been given permission to see.
Where I am stuck is that because her layouts reflect back to the entire patient table A, despite being restricted on which patient's she can see, the whole patient table comes up when she goes to try to add a new document. In other words, if she creates a new patient and then goes to create a note for that patient, IDs of all available patients come up as choices to assign the new document to.
I first tried to solve this by having a second table occurance of that Patient table A, thinking I could somehow make that the therapist's table of patients and then use that as the reference of available patient's to make a new document for, but I have no way of figuring out how to have that table occurance only have those patients in it she has been given permission to see.
I then tried to make a whole new table, but couldn't figure out how to have it automatically populated with patients from Table A. I could use look ups for each and every field I guess, but I don't know how it would restrict which records go into that table.
Any ideas to get me started?