How to make a list based on a relationship that references a calculation field
I am having trouble creating a list for a drop down. Some background first.
The list I want generated is a product name list. There is a 'product' table and a 'preferred products by customer' table in the database. The field that holds the data is a calculation field in the 'preferred products by customer' table (the calculation is basically - if product ID in 'Product' equals product ID in 'preferred products by customer', give me the product name). It is done this way to solve the problem of the product names changing in the 'product' table (for misspellings, etc.)
The problem is that the product name field, as a calculation, can not be indexed. And so when I try to create a relationship that references this field to display only the customer's preferred products, the drop down does not work. Well, it does work, but it takes 30 seconds a pop to populate the drop down which is a latency that is unacceptable.
How can I accomplish both of these functions?