This is how I do the same thing:
Employees::employee_id = testform::employee_id
testform::evaluator_id = Evaluators::employee_id
The two "table occurrences" (TO) both point to the same baseTable "employees", but there are two relationships, so two names to the TOs. Then the two fields in testform are entered with appropriate "id". This is what allows me to bring the names and other information into the testform as lookups or merge fields or just the related fields (no entry allowed, but for display only).
NOTE: I also have a trap so that both fields are not the same (ie the Employee cannot Evaluate themselves)!
I'm not sure of your 'reports' and why they aren't working.
I think my problem is that because I have more than one area to report on I set up different tables for the different areas. i.e. Report1 is for Area Station 1, Report2 is for Area Station 2, etc.
In other words:
Employees::pk_employeeID = ReportForm1::_fk_employeeID
ReportForm1::_fk_evaluatorID = Evaluators::_pk_employeeID
Employees2::pk_employeeID = ReportForm2::_fk_employeeID
ReportForm2::_fk_evaluatorID = Evaluators2::_pk_employeeID
Report1 works great but as soon as I try to add a new record to Report 2 I get the above warning, "The operation could not be completed because the target is not part of the related table".
I guess I could amalgamate all the fields into one table and only use the ones required on each form. But I was trying not to have duplication in one table.
I hope this makes sense?
It may depend on the context. If it's built on the form table then both fields are there. If it's built on one of the relationships it won't see the other relationship directly.
Sent from miPhone
Beverly has you pointing down the right track on the base question. However, just to add a comment on another aspect of your post: "an employee number (EmpNo) which is given by the company and therefore cannot be a serial number assigned by Filemaker".
I suggest you still use a FM unique ID field as your primary key to drive all your relationships, and treat the company's employee number as just another piece of data, like a middle name.
true, but you can use the employee number as a secondary key as well. as long as it's validated to always be unique in the employees table, it can be a key field too. But I agree that every table has a unique value for every record/row that has no meaning. Serial or UUID - depending upon whether you merge/sync data from different sources.
Thanks everyone, I have it working now.
you may wish to mark your 'solution' as correct answer so others may know.
I changed my strategy slightly: Because I need a Student and an Evaluator ( which are both Employees) on the same form I copied the Employees TO so it would relate to each of the 12 forms we use. Granted having 12 copies of the Employee TO may not be the best way to do this but it now works perfectly.
As well (thanks keywords) I went back and set up the primary field using UUID.
Thanks for all your help and pointing me in the right direction.
You can RENAME the TO on the RG (relationship graph). Just as I suggested.
One is Employees and the other is Evaluators, but they both have the same BaseTable (which happens to be named Employees, too).