How to search labels in dropdown

[rheinisaac]

Hi, 
Your help will be really appreciated

So I have 3 tables related to this concern,
Product table = Contains ProductID(Key),ProductName(label) and more details
Stock Table = References ProductID , contains quantity, cost, and srp
OrderDetails= references Stockid , Soldprice and quantity

Now, Here is the dropdown from orderdetails view


But when I type the product name in search bar, in this case, I'll search for "One piece Keychain PVC"


I believe the search bar uses the productID key value that's referenced in the Stock table instead of the label.
This will lead to a problem when my employee tries to search for an item.

Any idea how to solve this ? 

[Suvrutt_Gurjar]

In a similar example below, the Order Details table has Product ID as reference from the Products table. In the dropdown, it does show product names (labels) and one can search also by product names,

[rheinisaac]

Hi Survutt Gurjar

Thanks for your reply, 
Here is my Design

Table OrderDetails has stockID references from the Stock Table.

Stock table has ProductID references from the Product Table

And here is the product Table

So the flow will be starting from the Order Form view .
After putting the basic details, click "New" at orderdetails 

Then this form will show up where you need to specify which stock is being purchased

I think the difference between what you showed and my design is that the orderdetails is not referencing the product directly. in my case, it references to stock table as 1 product can have multiple stock with various cost

[Suvrutt_Gurjar]

---

Okay. Got it. Thank you for the new additional details as those are important for suggesting . Earlier the schema was not clear.

Please try the following. Please add a virtual column called [ProductName] in the Stocks table with an expression something like [ProdutId].[ProductName] and make this newly added column as label column of the Stock table in place of the existing label column.

Please try thereafter to search by name in the StockID column in the OrderDetails table.

---

@rheinisaac wrote:

, it references to stock table as 1 product can have multiple stock with various cost

---

However with this arrangement,  multiple stocks will appear with appear with the same product name in the dropdown list of the StockID.  You will need to decide an approach to identify same product with different costs.