9 Replies Latest reply on Sep 7, 2016 9:19 AM by beverly

    Having 2 Employees show up in Multiple Forms

    pademo57

      I couldn't find an answer to this in the forum so I thought I would ask here.

       

      The company has a policy where an employee must be evaluated once a year by another employee and have a written evaluation form.

       

      I have an Employee file which has the employee name and an employee number (EmpNo) which is given by the company and therefore cannot be a serial number assigned by Filemaker. The table also has a checkbox where you can assign either "Employee" or "Evaluator".

       

      I used a calculated field to find the Employee and another calculated field to find the Evaluator. The idea being an Employee can be the Employee on one form and on the next form can be the Evaluator. I know it's weird but it has to do with government rules.

       

      On the current form it shows the Employee Name and his Employee Number and another box shows the Evaluator Name and the evaluator's number.

       

      In the relationship window I have the Employee TO::EmployeeNo related to the fk_EmpNo of Report1. I also have Employee::EvalNo related to EvalNo of Report1. This is working great.

       

      However when I try to connect the same fields to Report2 it tells me I must create a new relationship. I copied Employee TO and called it Employee_Report2 and related the same fields as in Report1.

       

      But when I try to create a new record in Report2 I get an error message which repeats 3 times saying the same thing "The operation could not be completed because the target is not part of the related table".

       

      So here are my questions:

      1) How do I relate one Employee and an Evaluator onto different forms (Report1, Report2, etc)?

      2) How do I stop getting these error messages?

        • 1. Re: Having 2 Employees show up in Multiple Forms
          beverly

          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.

          beverly

          • 2. Re: Having 2 Employees show up in Multiple Forms
            pademo57

            Thanks Beverly,

            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:

            Report1 form

            Employees::pk_employeeID = ReportForm1::_fk_employeeID

                                                              ReportForm1::_fk_evaluatorID = Evaluators::_pk_employeeID

                                                              ReportForm1::Station1Check

                                                               ReportForm1::Station2Check, (etc)

            Then on

            Report2 form

            Employees2::pk_employeeID = ReportForm2::_fk_employeeID

                                                              ReportForm2::_fk_evaluatorID = Evaluators2::_pk_employeeID

                                                              ReportForm2::Station2Check

                                                               ReportForm2::Station2Check, (etc)

             

            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?

            • 3. Re: Having 2 Employees show up in Multiple Forms
              beverly

              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

              • 4. Re: Having 2 Employees show up in Multiple Forms
                keywords

                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.

                • 5. Re: Having 2 Employees show up in Multiple Forms
                  beverly

                  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.

                   

                  beverly

                  • 6. Re: Having 2 Employees show up in Multiple Forms
                    pademo57

                    Thanks everyone, I have it working now.

                    • 7. Re: Having 2 Employees show up in Multiple Forms
                      beverly

                      you may wish to mark your 'solution' as correct answer so others may know.

                      beverly

                      • 8. Re: Having 2 Employees show up in Multiple Forms
                        pademo57

                        Hi Beverly,

                        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.

                        • 9. Re: Having 2 Employees show up in Multiple Forms
                          beverly

                          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).

                           

                          beverly