12 Replies Latest reply on May 16, 2017 9:23 AM by eric

    Table Occurrence / lookup question on a report layout

    theaudone

      Hi,

      I'm hoping someone can help me sort through this problem I'm having. I'm creating a report layout and I think I have a problem referencing the right Table Occurrence but I'm not sure...

       

      Set up: It's a performance evaluation tool. An evaluation is stored on the perf_eval table, the questions for the evaluation are stored in a lookup table (sslu) and the ratings are stored in a second lookup table (critrat_lu) There is a join table that stores the evaluation number, the question number and the rating in points the employee received on each question (ss_eval_dtls) The citrat_lu is related many times in the database to every join table therefore there is a TO of the ss_eval_dtls, ss_eval_dtls2. Here is an example:

       

      Perf_eval        ss_eval_dtls                                   sslu                         critratlu

      eval1              eval1  question1      6                   question1                6  above standard

      eval2              eval1  question2     4                    question2                4   standard

      eval3               eval1 question3      2                    question3               2   below standard

       

      My model:

       

      Screen Shot 2017-05-15 at 9.48.52 AM.png

       

      The problem I'm having is in creating a report layout, I want to show the employee's rating of Above Standard, Standard from the critratlu table not just their points of 6, 4. I have tried pointing my report at perf_eval and I have tried pointing my report at ss_eval_dtls for example, but only the points value will display correctly, not the description from the lookup table. You can see just above the red oval below that for the first section, Product/Procedural Knowledge, the points value is 2 and the rating is Below which is correct, points value is 4 rating is Standard. But when I get to the next section Selling Skills, points value show 6 but the rating is showing Standard all the way down, which is completely wrong. The data is correct in my critratlu table, so why is it pulling the wrong information? I'm not sure exactly but I believe it's something to do with the fact that there's a TO for ss_eval_dtls called ss_eval_dtls2, but for ppk_eval_dtls there isn't.

       

      Screen Shot 2017-05-15 at 9.53.02 AM.png

       

      I'm open to any discussion and dialog on this, thanks in advance!!! Audrey

        • 1. Re: Table Occurrence / lookup question on a report layout
          eric

          I'm not the most experienced at interpreting reports like this, but I see no one else has responded yet.

           

          "I have tried pointing my report at perf_eval and I have tried pointing my report at ss_eval_dtls"

          In the report image, which are you pointing to?

          Are those two different layout with two different table occurrences?

          To get the correct rating descriptions, I think critrat_lu would have to be directly connected to each table occurrence from which you are reporting.

           

          In every layout issue like this I have to follow the path one relationship at a time to see whether I'm getting more or less records than I want for the layout from those related tables.

           

          Sometimes I get stuck and can't figure out how to get all the related data to display from a single layout's perspective, and yet somehow I can get the data to show by using unstored calculations in the same table or closer tables to force that data to show how I want. For report performance we normally try to avoid unstored calculations, but if it gets you through today in a reasonable time, I'd do it.

           

          Sorry, if I haven't interpreted your table occurrences correctly yet.

          • 2. Re: Table Occurrence / lookup question on a report layout
            theaudone

            Hi,

             

            Thanks so much for taking a stab!

             

            In the report image I am pointing at perf_eval.....I pointed at it because it's like the ultimate parent table - it holds the evaluation ID that is then carried everywhere else.

             

            I can correctly display in a portal from each join table the child rows, and the stored code of the rating points, but I can't display the description of those points EXCEPT for the first section of my report, the Product/Procedural Knowledge section. That shows 'Above Standard' every time they earned 6 points. But in the next section Selling Skills, the points they earned come in correctly but if I try to bring in the description it doesn't display right so I took it out - you can see the difference from what I pasted above to what is below.My report looks like this currently (sorry it's so basic and not lined up well)

            Screen Shot 2017-05-15 at 1.49.07 PM.png

            • 3. Re: Table Occurrence / lookup question on a report layout
              eric

              Oh. I thought it might have been one big subsummary report.

              If each of those different sections are just portals based on each _eval_dtls table occurrence,

              then shouldn't you have a critratlu table occurrence directly related to each of those _eval_dtls table occurrences?

              • 4. Re: Table Occurrence / lookup question on a report layout
                theaudone

                Hi,

                 

                Wow I'm so happy you're following this. Thank you again!

                 

                So yes, each eval_dtls table does relate to critratlu but when I created that relationship for every eval_dtls join table FileMaker created a second occurrence of the eval_dtls table every time. Did I lose you??

                • 5. Re: Table Occurrence / lookup question on a report layout
                  eric

                  Hi,

                  I'm not sure how FM created the second occurrence without being "told".

                  If you have a file with safe data, I'd be willing to look at it. I'm sometimes bad at understanding verbally, and am more hands-on.

                   

                  It might also help if I knew what "dtls" stands for, and whether all dtls TO are the same table and all lu TO are the same table.

                  I'm also trying to figure out why ppk has the extra lu, but the others don't.

                   

                  The two portals you show are based on PPK and SS respectively?

                  Are your report rating descriptions then based on ppk_lu and sslu respectively?

                  • 6. Re: Table Occurrence / lookup question on a report layout
                    theaudone

                    Hi,

                     

                    dtls stands for Details. In an Evaluation there are details and questions relating to that topic. So Product and Procedural Knowledge questions and details versus Selling Skills questions and details. The lookup table sslu holds the questions pertaining to Selling Skills, therefore called Selling Skills Lookup or sslu for short, and so on.

                     

                    When I created my model FileMaker forced the dtls2 table occurrences everywhere. It wouldn't let the critratlu table have a foreign key sent to all the different _dtls tables. I get an error message that says FM won't allow more than two relationships between tables and I must have a table occurrence to represent the relationship. It must not like that the critratlu code table is shared across the database.

                    • 7. Re: Table Occurrence / lookup question on a report layout
                      eric

                      I don't know if it's possible with your data,

                      but if at all possible, to simplify reporting and keep it flexible, I would attempt to have only one Details table and all those top six TOs replaced by a single TO, and all their respective Lookup tables and TOs replaced by a single Lookup TO. To categorize the Details into different sections, I would have a Category field. When set up this way, it would be easier to report the data from multiple perspectives, and you could even have a single subsummary report showing all the sections and details at once.

                      1 of 1 people found this helpful
                      • 8. Re: Table Occurrence / lookup question on a report layout
                        theaudone

                        I did think long and hard about designing it that way, you are correct with that design. I broke the details tables out because I was convinced the customer would start tracking more data on each of the topic areas that had nothing to do with the questions and I wanted it more normalized, but that hasn't happened yet.

                         

                        I'm going to mark this thread as resolved, I really appreciate all your effort. And I'll probably end up going back and redoing my model like you suggested. It's just much simpler that way!

                         

                        Audrey

                        • 9. Re: Table Occurrence / lookup question on a report layout
                          theaudone

                          I fixed it! This response has the Correct Answer for anyone else who reads this thread:

                           

                          I removed all my relationships from my code lookup table - critratlu - to every join table in the database. I then used the green double plus sign button on the bottom left in the Relationships graph Manage Database window to duplicate my code lookup table into multiple occurrences. I then reestablished a relationship from each separate occurrence of the code lookup table to each of my join tables and voila! My report runs like a charm.

                           

                          I think I will create another post just explaining what I figured out. I feel that it's very confusing that FileMaker lets you build a second occurrence of your tables on your graph on the ONE side of a one-to-many relationship. I understand this probably allows for a ton of flexibility somewhere else but when it comes to code lookup tables the explanation on the Forum should be VERY clear, and it isn't.

                          1 of 1 people found this helpful
                          • 10. Re: Table Occurrence / lookup question on a report layout
                            eric

                            Good news.

                            Do you have a new graph to share with us?

                             

                            FileMaker lets you build a second occurrence of your tables on your graph on the ONE side of a one-to-many relationship, because a single table  might be able to relate to itself or to any other table in multiple ways. Though this might not relate to how you mentioned it, you might have a session table of choices where you might have an any-join, the another relationship to the session that was chosen, then another to the earliest, session, the latest session, the most chosen session, etc. Not a perfect example, but there are real cases like that.

                            1 of 1 people found this helpful
                            • 11. Re: Table Occurrence / lookup question on a report layout
                              theaudone

                              That's great Eric, thanks for pointing that out. I forgot about that type of application because I was wrapped up in my own issues. Hopefully all this will help others!

                               

                              Here is the new model:

                               

                              Screen Shot 2017-05-16 at 12.22.52 PM.png

                              Audrey

                              1 of 1 people found this helpful
                              • 12. Re: Table Occurrence / lookup question on a report layout
                                eric

                                Hey! Look at that! In the midst of this discussion, your ranking as a Community member went from "Solution Starter" to "Requirements Gatherer". Congratulations!

                                theaudone

                                112POINTS • REQUIREMENTS GATHERER