I have all employees in a contacts table. All sales reps are in the employees table.
The problem is I can't figure out how to do this.
Hmm and I can't really figure out exactly what you want to do. I can read your description a number of different ways and come up with a number of very different things.
A conditional value list for selecting contacts can limit the list to just employees, just sales reps or it can be selectable where a user selects Sales Reps and the value list lists Sales Reps and if they select Employees, they get a list of just employees.
A layout can be set up with some scripted support to only list employees or to only list Sales reps. (Or you can give the user something to click to switch between them.)
A portal could display only one group or the other or the group shown can be selectable.
Sorry I wasn't clear--I'll try again. My database has tables to keep track of rentals, sales, purchases, contacts, documents, etc. The rental, sales, and purchase tables each have related tables which contain either sales reps or employees. The employees (and therefore reps) are also in the contacts table. If a new sales rep is added, there are three tables into which the change needs to be made. I wanted to make all of this information flow out of the contacts table, so changes in employees and/or sales reps only need to be made in one place. The main problem is that I cannot figure out how to make a value list that includes only the sales reps from the contact table. That is the reason I set up the redundant tables.
I thought I could make up a contacts table occurrence and relate it to the main contacts table occurrence via this type of relationship: _[ContactsID_pk = ContactsID_pk AND Rep = gRep] where Rep is tagged with "1" when the contact is a sales rep for our company, and gRep is a calculation that equals "1" and is global. However, when I use this table from which to pull values for the value list, all of the contacts show up in the value list, rather than being limited to just the sales reps.
I guess I still don't completely understand how to do table occurrences for the purpose of queries.
You'd need a relationship to an occurrence of Contacts that is set up like this:
MainTable::constSalesRep = Contacts::ContactType
If "SalesRep" is the text in ContactType for every Contacts record of a SalesRep, then constSalesRep can be defined as a calculation field with:
as its expression and with Text selected as the Result type.
You could also use Option 1 of the following tutorial to get a value list of SalesReps:
Forum Tutorial: Custom Value List?
It works! Thanks so much. I used option 2 of the tutorial. When you see how it works, it looks so simple--hard to see why I couldn't figure it out without help. I never thought of option 1 however. That is a simpler solution that I will be able to remember easier.