Can you create a relationship between a primary key in one table and a user list field in another table?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have a Sales Rep table full of sales reps which has a Primary Key field, this Primary Key field is a text field. This primary key also the users/sales reps name. For example John Biggs or Sally Smith.

I have a separate table, full of clients. Some of these clients are big clients who work with us globally and as such can be managed by multiple sales reps. As such there is a user list field on this table that identifies which reps are responsible for each client. E.g. ABC client is managed by John Biggs and Sally Smith.

I am looking to create a relationship between the Sales Rep table and the Client table between the Primary Key on the Rep table and User List field on the client table. Is this possible?

Photo of Ashley


  • 70 Points

Posted 4 years ago

  • 0
  • 1
There is not a way to make an actual relationship.  But I can think of a way to have the user push a button on the Client record called Show Rep Detail.  That would open up a section on the form to show an embedded table of their sales reps where the sales rep is included in the List User field of the client.  So, for example, the  report could include any fields on the sales rep record, such as their contact info.  Let me know if that would solve the problem and i can describe in general terms how I would go about it.
Photo of Eric


  • 40 Points
You can create a formula field, then setup a new relationship selecting your formula field as its reference field. This probably won't work for your situation however as you would need to create ten relationships. Instead, I recommend creating a many-to-many relationship as outlined here: so that you can select as many sales reps per client as you want.