Relational information based on other relational information
Ok, so I'm teaching myself FileMaker Pro and doing so with a real application because I find it easier to do so rather than basic tutorials bit by bit.
Anyway the application I'm developing is call centre application. The main aspect of the system is of course the Job Details page as this is what everyone will see and to a certain extent edit. So the page will create a job number and of course there will be a User and that user will be part of a company. However there will be many companies and as a result many users.
I'm not sure if I'm doing this the right way so I'll explain what I've got.
I have the Job Details table.
I have the Company table.
I have the User table.
On the Job Details table there is a Company field which I have linked to the company table.
There are also First Name and Last Name fields that are linked to the Users table.
So I can create a user and assign them to a company but I don't know how to prevent duplication of users against a company. Is there a way to check the users assigned to a company in order to prevent a new user with the same details being added to that company?
Second, on the Job Details table I would like to be able to select a company from a drop down which in turn only makes the user field only display users listed against that company also from a drop down box. Is this able to be done and if so how? Would it require a script or can it simply be done by options when creating the fields?
Thanks for your help