AnsweredAssumed Answered

Table Occurrence / lookup question on a report layout

Question asked by theaudone on May 15, 2017
Latest reply on May 16, 2017 by eric


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