What I see as a data model and what I read in your description seems to contradict each other.
For a given company, is there ever more than one associated Rep?
You show a relationship with a join table that would indicate that a company can be linked to more than one rep and that a rep can be linked to more than one company, but then in your text, you state:
In the companies table I select the rep assigned to the company
Which would seem to indicate that a rep might represent more than one company, but that there is only one rep associated with any given company???
Sorry for the confusion. There is never more than one associated rep...there is only one rep associated with a company.
Then reps should be linked directly to companies without the use of a join table. A join table would allow you to link multiple reps to the same company while still being able to link multiple companies to the same rep.
Then how would I select the correct sales rep from the Rep company? There are many reps that work at the rep company but I have to select only one.
Any thoughts on this? A big chunk of my database is not working properly because of this problem.
Select the Rep in a record from which table?
If there is only one Rep to link to that record you need only link an occurrence of the Reps table to the first table by a RepID--what I typically label as __pkRepID.
Keep in mind that you can create multiple Tutorial: What are Table Occurrences? of the same table in a FileMaker relationships graph as a way to define the needed relationships.
There are many reps who work at one rep firm. That is why I have created a join table. Within my media company database I have to select the correct rep (from the join table) from the rep firm that works with the media company. For example, radio station KFBK in Sacramento is represented by Clear Channel. So, I have selected Jean Mihalek from three others at the rep firm Clear Channel as she is my rep. However, for this station I deal directly with the sales rep Joel Mordhorst. So, to indicate that I work direct, the boolean field "Rep" is not checked. See attached screen shot of my media company table.
There are many reps who work at one rep firm. That is why I have created a join table
But this does not require a joint table--as I have previously stated here. You only need the join table if you have both of the following situations: 1 rep can work for/represent multiple companies AND one company can have multiple reps working for/representing one company.
I think that you are confusing the relationship needed for a conditional value list--a list that can limit the list of reps in a value list to just those from a single company with the relationship needed to show that a specific rep has been selected for a particular record in the order table. Same table, two occurrences and two different relationships.
Reps|Selected::__pkRepID = Orders::_fkRepID
Orders::_fkCompanyID = Reps|byCompany::_fkCompanyID
Your value list of Reps would be based on Rpes|ByCompany with "include only related values starting from orders" specified. The _fkRepID field in Orders would be set up with that value list. When you select that Rep ID from the value list, you then link that record in Orders to just one record in Reps|Selected.