9 Replies Latest reply on Jan 13, 2015 12:14 PM by Mike_Mitchell

    What am I doing wrong here? (Structural/relationships question)

    truckbuddy

      Hey all,

       

      I've lurked for awhile and have picked up a lot of great help. But now I'm stuck.

       

      I'm not a developer -- just a small business owner. I've been trying to build a basic personnel database for a year. For awhile I had it, but when problems started creeping in, it became obvious it was time to scrap it and start over.

       

      Here's the deal: The stock personnel database that comes with FMP 13 is great for basic stuff, but I've been trying to customize it with some additional features. Namely, I want to keep track of customer reviews from places like Angie's List and Yelp (which generate more than half of our business). These reviews are valuable and a great way of measuring employee performance. I want to relate these reviews to the employees who helped the customers who wrote them. I also want to add a table for "incidents," mostly the lapses or otherwise negative stuff.

       

      The details:

       

      Customers write reviews. Each review is related to two or more employees (they work in crews of 2-4 people). However, sometimes customers write more than one review (some customers have written four or so reviews over the years).

       

      I get how a join table works, for the most part, but I'm not sure what to put into the join table between Employees and Reviews (I can live without a separate customers table, but if I could get this damn thing to work right, it would be nice to have customers linked in.) Do I put the employees in the join table almost as if they're inventory line items?

       

      This is a lot of words for what I'm sure is a simple problem, but I can't get it to work right. Match ID fields seem right, etc. Each review can have multiple employees, and each employee can have multiple reviews.

       

      Help!

       

      Thank you,

      Chris

        • 1. Re: What am I doing wrong here? (Structural/relationships question)
          Mike_Mitchell

          Hello, Chris. Welcome!

           

          You're on the right track. A join table is the "right" way to implement a many-to-many relationship. Here are some pointers:

           

          1) At its most basic level, a join table will contain a field for the key of each table it's joining together. In your example, it would contain EmployeeID and ReviewID. Each record in the join table, therefore, is a unique combination of the review and an employee to which it relates. You shouldn't ever have a duplicate of the combination of the two (although obviously an employee can appear more than once, and a review can appear more than once - that's the point).

           

          2) Many folks will also include a unique, separate key field for the join table itself. This can be useful if you want to identify a particular join record for scripting purposes, but it's not absolutely necessary. (The combination of the other keys is, as I said, unique.)

           

          3) You can implement the join table through a portal on each of the parent tables. For example, if I'm looking at a review record, I can have a pull-down of available employee ID values (along with, say, a name) to insert into the join table record (enabling record creation on your Relationships Graph) so that you can just add employees to a review. Conversely, you can have a portal on your employees screen with a pull-down of available reviews. This gives you the ability to see all reviews related to a given employee, or all employees related to a given review, simply by looking at the appropriate record.

           

          4) Another way to implement the join is to use a list selection view, where you click on the correct record and script the insertion of the correct key fields. This is a little more sophisticated, but takes control away from the user (to help prevent errors).

           

          To your other question, should you want to associate a customer with a particular review, you can do so directly on the review record, I should think. A single review would not be associated with more than one customer, so that could simply be a data element of the review record in a one-to-many relationship, and you can pass the information through to the join table if you wanted to via the related field.

           

          Hope that makes sense. Feel free to ask further questions.

           

          Mike

          • 2. Re: What am I doing wrong here? (Structural/relationships question)
            truckbuddy

            Mike,

             

            Thanks for the clear response. Let me see if I understand you correctly, you suggest putting a portal on the Reviews table (as well as the Employees table), that is directly linked to the join table? I've played with numerous portals and scenarios, but I'm not sure if I tried that one yet -- I think my main attempt has been to put, say, a Reviews table portal on the Employees Table...

             

            So, when I'm logging a new review, I can create lookups to add new employees, and these would go into each line of the portal (which is linked to the join table)? Then this review SHOULD be linked to each of the employees added?

             

            Thanks again!

            Chris

            • 3. Re: What am I doing wrong here? (Structural/relationships question)
              Mike_Mitchell

              Yes. The join table sits between ("joins") the two parent tables. This is what converts the many-to-many relationship into (essentially) a pair of one-to-many relationships.

               

              Now, to facilitate a more friendly user experience, you can add fields from the parent tables to the portals. For example, you can put the employee name in the portal on the reviews layout so the user can see which employees have been added. Just set the fields so they cannot be entered in Browse mode (to prevent people from editing the employees table from there) and it works a treat.

              • 4. Re: What am I doing wrong here? (Structural/relationships question)
                truckbuddy

                Cool. Thank you. I'll see if I can get this thing working.

                • 5. Re: What am I doing wrong here? (Structural/relationships question)
                  truckbuddy

                  Yeah, it doesn't take long to go off the rails here. For what it's worth, I did great with MS Access, but since we switched to Macs, well, not so much...

                   

                  Here's a pic of my relationships graph. Screen Shot 2015-01-13 at 1.35.06 PM.pngQuestions:

                   

                  1. Should I put pop-up employee fields in the join table? As of right now, when I create a portal on the Reviews table using the Join table, the only thing I can put on there is the Personnel ID Match Field.

                   

                  2. I accidentally created a portal on the Reviews table using the Personnel (Employees) table as it's "foundation." When I saved, the portal was just a blank box. That's been a recurring problem. Why is that -- is it because I'm using the wrong table for the portal?

                  • 6. Re: What am I doing wrong here? (Structural/relationships question)
                    Mike_Mitchell

                    1) The only fields you absolutely need in your join table are the two foreign keys. You're right; that means all you can put in there is the foreign key value. But that's okay, because you can reference fields in either parent table from there.

                     

                    2) A portal will not have any objects in it if you don't tell FileMaker to put them there during the creation of the portal. That's also okay; you can always add them later.

                     

                    See if the attached file (VERY quick and dirty) doesn't help.

                     

                    Mike

                    • 7. Re: What am I doing wrong here? (Structural/relationships question)
                      truckbuddy

                      Mike, you rock. Thank you so much. I'm going to analyze this to death until I figure out what I'm doing wrong.

                       

                      One last quick question -- you say I have to tell Filemaker to put objects into the portal. Maybe something changed in the version I'm using (FMP 13), because in the original version of this, I never had empty portals. Maybe I've just forgotten that simple step, but it's driving me up a wall. Is it a box you have to check when you create it?

                       

                      Thanks again!

                      • 8. Re: What am I doing wrong here? (Structural/relationships question)
                        truckbuddy

                        Thanks again. This helped immensely to spotlight where the real error was -- my employees didn't have any FRAKKING ID'S! Somehow I neglected to add those when I imported the employee list. Information in the portals wasn't "sticking," (among other problems) because there was no ID.

                         

                        It's fixed.

                         

                        Thanks so much!.

                        • 9. Re: What am I doing wrong here? (Structural/relationships question)
                          Mike_Mitchell

                          When you create the portal, you'll get a dialog that shows you the fields in the table occurrence to which the portal points by default. You can select fields from that. If you don't, you'll get an empty portal.

                           

                          And no, nothing changed between versions.