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.
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?
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
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.
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.
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.
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.
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.
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.
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.