9 Replies Latest reply on Jun 25, 2014 2:13 PM by philmodjunk

    Ranking Employees



      Ranking Employees


           Hi There,

           Our factory rates employees by using 5 ranks:

           rank1 - Uncertified

           rank2 - Certified

           rank3 - Instructor

           rank4 - Master Instructor

           rank5 - Supervisor

           I want to have a layout where 3 fields need to be shown:

           1) one can choose students by only listing (Drop-down list?) employees with rank1

           2) one can choose instructors by listing either rank3 or rank4

           3) one can choose a supervisor by listing only those employees with rank5

      I don't know how to make each field conditional on the above conditions

           I have set up 3 tables:

      rank: _pkRankID, RankName

      employee: _pkEmpID, EmpFname, EmpLname, _fkRankID

      trackingform: _pkFormID, FormDate FormComments, FormRating, _fkEmpID 

      Is this the best way or am I way off track?


           How do I accomplish this?


        • 1. Re: Ranking Employees

               You might not need a table just for the 5 rank names--it depends on what else that you need.

               The purpose to your tracking form table is not clear to me.

               To get a list of all employes of rank 1 (students) you need only perform a find for all employee records with that rank.

               The same is true for your other requirements.

               Finds can be performed manually or via a script. For examples of finds performed by a script, see this forum thread: Scripted Find Examples

               You can also set up relationships that match only to employees of a certain rank or range of ranks.

               And filtered portals can omit all employees not of a given rank.

               So you have a number of options for working with this employee rank.

          • 2. Re: Ranking Employees

                 Sorry I wasn't clear,

                 What I am trying to do is set up a tracking form that can be used by an instructor each day with a student which will replace our current system which is a paper form that an instructor has to fill out showing student name, instructor name, supervisor, date, performance comments, etc.

                 On the form I am trying to make it simple for the instructor.

                 In the first field on the form he would be able to pick from a list of rank1 (students) ( I was trying to make this a drop down).

                 In the second field he would be able to pick either rank3 or rank4 (instructor or master instructor). Again I was thinking this could be a drop down.

                 In the third field the drop down would only show rank5 (supervisor).

                 I'm not sure how to set up the drop-down to only show the records based on the rank or ranks.


                 Hopefully I am making this a little more clear?

            • 3. Re: Ranking Employees

                   These are called conditional value lists. Here are some links on the subject:

                   There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                   The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                   Forum Tutorial: Custom Value List?

                   Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                   Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                   Hierarchical Conditional Value lists: Conditional Value List Question

                   Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                   Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

              • 4. Re: Ranking Employees

                     Thanks PhilModJunk,

                     But what I am looking for is slightly different and maybe Filemaker isn't able to do this. From the examples shown it looks like you first have to select the type and then get the specific item.

                     What I was hoping for was a drop-down list that didn't require you to first select a type, in my case, for example: rank1.

                     Is it possible to have a field with a pre-selected type? As in:

                     Student: (drop-down list pre-determined to only show all employees that have rank1)

                     Instructor: (drop-down list pre-determined to only show all employees that have rank3 or rank4)

                     Hopefully I am explaining this better than last time.

                • 5. Re: Ranking Employees

                       Take a closer look at Option 1 in the forum tutorial. No value need be selected to specify a type. You do not even need a relationship.

                       And if you prefer to use Option 2 in the forum tutorial, the user need not be the one to chose the type or category. Either a script can set that value for them by setting the value in the field or you can use a calculation field as the match field to the table of values such that your value list only lists values for a given rank or list of ranks.

                  • 6. Re: Ranking Employees

                         Thanks PhilModJunk,

                         I guess my problem is I am used to other programming languages that allow me to use a 'where' clause.  Using my example above I can't figure out how to write the conditional statement.  All I get is either a ? or a 0 (zero) or the drop-down list won't let me select it at all.

                         I realize you are probably very busy, as over the years I have seen your name in hundreds of answers and are probably unable to walk an old guy like me through  my simple and crude example.

                         Thanks for all your help over the years but I think I will try another forum to see if someone can walk me through, step-by-step, how to get a drop-down box without first selecting a type and then in another field selecting the specific item.

                    • 7. Re: Ranking Employees

                           There are many posts by me because I've invested a lot of time in this forum.

                           Option 1 has all the step by step details that you should need.

                           If you prefer option 2:

                           You can use a calculation field to get a relationship that matches to only one rank or a set of ranks in your employee table:

                           TrackingForm::constStudent = Employees|Students::RankField

                           where you define constStudent  as a calculation field that equals the value (rank name or rank id) for your first rank employees. "Employee|Student" is just the name I've given to an additional table occurrence of your Employees table.

                           And if you are more comfortable with SQL queries, you should have looked at the newest and last link in the list provided. It describes how to use ExecuteSQL--which uses an SQL query, in a conditional value list.

                      • 8. Re: Ranking Employees

                             My apologies PhilModJunk,

                             I wasn't trying to insult but rather give you a compliment. As I said in my previous post, I have seen your name in hundreds of post. What I meant to say was how grateful I, and those in this forum have, with someone like you who can and has answered all these questions.

                             If my last post came off wrong I think it was because I am under enormous pressure and time constraints to get this project done by next week. I was hoping there was a simple answer to my question using my example.  

                             Thanks again for all your help over the years and, again, my apologies.

                        • 9. Re: Ranking Employees

                               Yes but these are simple examples and it doesn't seem like you have actually read all the info in the links provided or downloaded and examined the demo file. You are welcome to ask specific follow up questions about how to implement any one of these methods for getting a conditional value list to work for you.