Thank you for your post.
Unfortunately, your relationship table image does not display.
Let's assume you have the following tables:
For this example, I'll assume the "Product ID" field is the key field (marked with an asterisk) that links the two tables together.
In the ORDERS table, if you enter a "Product ID" that exists in INVENTORY, then all information from that record should be available to the ORDERS table.
Can you give me an example? Please be specific where the information originates.
Don't know what happened to my first response but here I go again:
Customer - CustomerID
CustPurch - CustpurchID
Order - OrderID
On the orderitem, for orderitemIDfk, I have a value list that looks at the custpurch table and lists products authorized for the customer. I can get the correct item, however it does not select the correct price. It is usually the price for the first item for the customer in the custpurch table.
Thank you for the clarification.
I'm assuming there is an inventory file. That is probably through the "ItemID" field, so you should be pulling the Price from that link; not from another table. It sounds like you are pulling from the custpurch table, which would be items that the customer already purchased; not from the inventory file. Does that make sense?
There is no inventory file as it is not tracked. There is an item file which stores all of the data about the item including 9 prices for the item depending on the location and shipment load. This is the basis for calculating the price that goes into the custpurch table for each item.
The custpurch file is a list of what is authorized to be purchased by the customer, not a table of what has been purchased, and also includes a price override field for the item. So, if the calculation returns a price of $30 and the override field is $31, the the printed price (stored in custpurch) which is also the invoice price will be $31.
Thanks for the additional information.
It sounds like you have the 9 prices in separate records, and you need an additional key besides just the ItemID. There needs to be an additional key field that narrows the search down to the correct price. You say this is dependent on Location and Shipment Load, so that key needs to be calculated first so you can link it to the Item file and retrieve the correct price. Does that make sense?
What is the criteria needed to determine the correct Price?
The 9 prices are in 1 record. The calculation for the correct price for the item for the customer is done at the type we create the authorized item for the customer in the custpurch table.
I have created a second instance of the custpurch table and am able to pick the correct price for the order, but not the correct itemID.
If the nine prices are in one record, and there is one itemID per record, then if you cannot pick the correct itemID, then your relationship is not built on the itemID. Make sure the itemID is linked to the appropriate itemID in both tables. Then, at least you are on the right record, and then with your calculation, retrieve the correct price.