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 ?
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,
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
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.
User | Count |
---|---|
42 | |
25 | |
25 | |
17 | |
12 |