1 2 Previous Next 23 Replies Latest reply on Apr 1, 2015 6:31 AM by mark_baum

    How do I display all drop down values or popup values on a layout?




      I'm hoping someone who has more experience with layout design can help me with this question.


      I'm designing a performance review system for a sales company. I have tables that contain values of performance rating criteria by topic, for example, selling skills. (sample values below)


      Each time a sales rep has a performance review, the reviewer needs to rate them as 'above std', 'standard' or 'below std' etc (yet another table of course with these values) on each and every criteria.




      selling skills performance criteria:

      - sales rep effectively demonstrates product knowledge

      - sales rep demonstrates ability to successfully use closing techniques to achieve sales objectives

      - sales rep demonstrates ability to differentiate products in competetive situations

      - effectively presents the products and appropriate procedural presentations to satisfy the customer's needs


      rating code:

      - above standard

      - standard

      - progress toward standard

      - below standard


      I would like the layout to show each of the available selling skills criteria to the reviewer when they create a new performance review, instead of them having to go through and choose each of these criteria from the popup, then rate it. They will always be rating the sales reps on ALL the criteria, every time they do a review. So if they have to go through and choose each criteria before applying their rating, it will be a lot of clicking time that isn't necessary.


      What is the best way to do this on a layout?


      Right now I have a layout for the high-level performance review, then a tab control showing each rating topic, and a portal on each tab that I am showing a popup with all the rating criteria, and a separate rating code for each choice.


      If you need more detail about my database design etc I'm happy to show that too.


      Thanks in advance,


        • 1. Re: How do I display all drop down values or popup values on a layout?

          Audrey -


          I'm not 100% certain I understand your setup. Does each criterion for each person live on its own record? Can you show us how your data model is arranged?



          • 2. Re: How do I display all drop down values or popup values on a layout?



            I'm attaching a PDF of my design. To answer your question in words though, a row will be stored on a details table (ss_eval_dtls) for an employee's current review (perf_eval) for each criteria explained above (kept in sslu - selling skills lookup) with the rating they receive (from critrat_lu or, criteria rating lookup - above std etc.) The performance evaluation table itself holds their higher level final scores and review date information. I hope this is making sense...


            Screen Shot 2014-12-02 at 7.20.51 PM.png

            • 3. Re: How do I display all drop down values or popup values on a layout?

              I think what you need to do is this:


              • Create a library table that holds one record for each possible criterion on a performance review. (You may already have that if you're drawing your pop-up list from there.)


              • When a new review is created, instead of allowing individuals to create individual line items one at a time for each criterion, use a script to create all the criteria at once by importing them from the library table (or by using a script and looping over the available criteria).


              This way, as a user is completing a review, they don't have to select which criterion they're completing from a list of available options. The options will already be there as individual line items, and all they have to do is choose the rating.





              1 of 1 people found this helpful
              • 4. Re: How do I display all drop down values or popup values on a layout?

                OK thank you so much for looking at this.


                Yes, the library table you're referring to is my sslu table - selling skills lookup - it holds all the criteria. I want it to be table driven so it's easy to change the choices in the future if they need to...


                I was pretty sure I would need a script since there were no solutions in the forum about this at all. I can't imagine I'm the first person who wants a layout to display all dropdown choices available upon create though?


                Does you have any recommendations on where I could look for scripting assistance?


                Thanks in advance,

                • 5. Re: How do I display all drop down values or popup values on a layout?

                  Does you have any recommendations on where I could look for scripting assistance?


                  Sure. Right here.  


                  Yes, you would need a script to create the new record. Such a script might look something like this:


                  Go to Layout [ {layout for new evaluation parent record} ]

                  New Record / Request

                  Set Variable [ $evalID ; evaluation::recordID ]   // Or whatever the record ID field is

                  Go to Layout [ {layout for library table} ]

                  Show All Records

                  Go to Layout [ {layout for criteria associated with evaluation} ]

                  Import Records [ ]  // Set this up to import from the same file, with a source table of the library, and a destination of the criteria table

                  Replace Field Contents [ criteria::recordID ; $evalID ]  // Use the foreign key field that ties the criteria table back to the evaluation table

                  Go to Layout [ {layout for new evaluation parent record} ]


                  That would be method 1, and is simpler than method 2. That would involve setting up a relationship between the criteria table and the library table, saving the IDs of the library records in a variable and looping over the values, creating the records as you go. That's more bulletproof (because Import can sometimes get munged up), but it's a little slower to execute and can be harder to understand.


                  Anyway, give this a try and let me know if you have trouble.



                  • 6. Re: How do I display all drop down values or popup values on a layout?

                    Wow thank you so much! I'm new to Filemaker, can you tell? But not new to database design...I'm following your logic and I think I can make this work.


                    I'll let you know results!



                    • 7. Re: How do I display all drop down values or popup values on a layout?

                      Hi Audrey, we were recently in touch about this project. You wrote the following (I'll respond in a separate reply):


                      I am building a Performance Review application for a company that deals in sales of equipment and tools for surgeons in the operating room. The sales reps need quarterly reviews on their performance.


                      I’m trying to automate a process that involves a “main” table and a “code” table.


                      The process will create a series of new records in the main table, one for each of a predefined set of unique codes stored in the “code” table.


                      Once the records are created, they are presented to the user, who manually enters more information into each of the records.


                      Basically, I’d like to get the user started by pre-filling the choices that they need so that they don’t have to click so many drop down lists manually.


                      The user will create a new performance evaluation, which on the back end is a new set of rows in a main table, with related employee ID, date etc.


                      The user needs all of the records each time, they will never do a partial employee review in any form (they won't fill in the review for only certain categories, nor will they only fill in certain questions on all categories, etc.)


                      I have a main performance evaluation table, related to a typical type of employee table with the standard information you'd expect to see there. Then there are code tables that hold a set number of questions for each category the sales rep will be evaluated on, for example:


                      Category: Selling Skills

                      Expectation 1: Has successfully profiled the hospitals and surgical centers in his/her territory?


                      The rep then receives a rating on this question: above standard, standard, below standard, needs improvement. So for each question, in each category, there is a rating. (And yes, the rating is just a code table as well.)


                      This continues on like this, multiple questions in multiple categories. So for each new performance evaluation, the form should pre-create for the user doing the review, loading all questions in each category. The user then provides the ratings, saves and exits.

                      • 8. Re: How do I display all drop down values or popup values on a layout?

                        The advice you've received so far makes good sense to me. Here's how I'd describe the important tables involved (I realize you have an ERD in the thread above, but I'd prefer to talk about the tables as I'd envision them):


                        * Question Template table -- contains one record per question to be asked. Includes a category code field identifying the category of the question, and a SortOrder field identifying the sequence of the questions as they should be presented to the user.


                        * Category table -- contains one record per category of question along with the category text presented to the user.


                        * Perf Evaluation table -- contains actual performance evaluations. Records are imported (or otherwise added) from the Question Template table, then an EmpID field is populated for each imported record to associate it with the rep in question, and a YearQuarter (or date) is set as well.


                        * Employee table -- contains one record per employee, identified by EmpID.


                        It sounds like you are still working on the best way to bring the Question Template data into the Perf Evaluation table. I agree that the Import Records script command is the simplest way and will probably work just fine for you. The alternative is a looping script that loops through the Question Template records one at a time, copying them to the Perf Evaluation table.


                        Can you identify where you've gotten stuck at this point?





                        • 9. Re: How do I display all drop down values or popup values on a layout?

                          Hi Mark,


                          To answer your last question first, I think I got stuck for two reasons. One, I was so new to FIleMaker I was still exploring, learning and building my app while I was researching the script and two, I was not sure if the import method was the first method I should try. I'm convinced now, that's two recommendations and I will get started on it.


                          Your vision of my tables is pretty close, it is different from how I built them but it doesn't matter. I have a separate Question Template table for each category out there, instead of a category code. I then have a relationship from each to the main Performance Evaluation table. Either way works - I follow you no problem. The result is the same; if the users need to edit their questions I only have to do the edit in one place in the database and I'm done. They aren't 'hard-coded' onto a form somewhere.


                          Thank you so much for all your interest in my project, I really do appreciate it! I'll be sure to let you know how this script turns out.


                          • 10. Re: How do I display all drop down values or popup values on a layout?

                            Great - I look forward to hearing how it goes!


                            It will simplify your import process if you put all your template questions into one table, rather than having a separate table for each category. Is there a compelling reason for the separate tables -- other than the fact that you already have them in place? :-)

                            • 11. Re: How do I display all drop down values or popup values on a layout?



                              Yes, that is the most compelling - that they're already in place. :-)) I guess I was thinking along the lines of 'what if they did some day want each category as a separate review'  or if they wanted to build layouts with more details just on that aspect of the review. I thought I would try to make it extremely flexible, instead of relying on a category code… I'm not opposed to changing it at all really.


                              I made a stab at the script today, I'll keep you posted. I'm not sure these users are going to like the import dialogue boxes. That remains to be seen. First I have to get the script working though, thanks.



                              • 12. Re: How do I display all drop down values or popup values on a layout?

                                Hi Audrey,


                                For the “Import Records” script step, you can specify the data source and import order.


                                The data source is the same file as you are working with (or wherever your data is housed). Click the “Add File…” button in the resulting “Specify File” dialog, and browse to your database file.


                                The import order is set up in the same manner as when you do it manually. Remember that you need to be in the proper layout context for the data you are “importing”.


                                Once that’s set up,  if you select the “Perform without dialog” option, the import process will take place invisibly.


                                Cheers —



                                • 13. Re: How do I display all drop down values or popup values on a layout?

                                  Hi Mark,


                                  Thanks for your help! I feel as though I'm making great progress now. I also feel like I cheated on a test when you reminded me about the Perform Without Dialog checkbox…I KNEW that too….  :-)


                                  I think now I need your insight on a different aspect of this.  I adjusted the script to hold the primary key of the new evaluation ($evalID) because that needs to be written as well, and it works, EXCEPT, even though the rows are being written and the import is successful, I can't see the description back on the main layout. I pasted a screen shot of this below the script. When I create a row manually the 'question' appears in the drop down long rectangle box, and the black rectangles to the right are waiting user input (above standard, standard, etc.) I'm guessing that the script needs to move through the rows now, tabbing out of fields etc in order to view the text?


                                  Please see script:

                                  Screen Shot 2015-03-31 at 11.16.58 AM.png

                                  Also, a screen shot of rows as they appear after the script runs:


                                  Screen Shot 2015-03-31 at 11.31.10 AM.png

                                  • 14. Re: How do I display all drop down values or popup values on a layout?

                                    Hi Audrey,


                                    I'm sure there are other people in the community whose interpretive and/or psychic powers are stronger than mine, but I sure could use some comments in your script telling me what's going on.


                                    It looks like you have a performance evaluation record related to a series of performance evaluation details records. That's clear.


                                    The evalID field is the primary key for a performance evaluation and joins the details to the evaluation. You are correctly performing a replace on it after the import takes place.


                                    But what is the ppkCd field used for? It seems to be associated with a details record that you create before the import, and then is populated to all the imported details records. Note that when the import takes place, the details record that you created before the import might no longer be in the found set (depending on how the import is set up). So it would not have its evalID foreign key value get set.


                                    However, I'm not sure what that first details record is used for in the first place...


                                    Regarding whether the question text appears, that depends on two things -- at least if it's being displayed relationally:


                                    1) How your ERD is set up (is there the proper connection from perf_eval to ppk_eval_dtls to question_template_table)


                                    2) How your import is set up (is your question_template_table's primary key being imported into a corresponding foreign key used to establish the relationship between ppk_eval_dtls and question_template_table)


                                    If the question text is being imported into a field in your performance evaluation details table, double-check that this is really happening as intended, and that your join between perf_eval and ppk_eval_dtls is really working. You can test that by viewing the foreign key in the portal (i.e. ppk_eval_dtls::evalID). If it displays, the relationship is working properly and the problem is probably with the import (or possibly security settings, but I doubt that).


                                    Hope this helps -- a little --



                                    1 2 Previous Next