Forgot to add i will need my line items portal to filter the latest sales price for the selected item. I could not achieve this on my own. I was getting the old prices or non at all.
Apologies for the size of this "info dump" but here are some links on conditional value lists:
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.
The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
i will need my line items portal to filter the latest sales price for the selected item.
If you have a date field in the Pricing table that records when the price goes into effect, you can include that date as a match field in the relationship linking it to line items. You can use an inequality operator and sort your relationship in descending order by Date in order to access the most recently dated price record for a given item that doesn't have an effective date that is still in the future.
Define a date field in LIneItems that auto-enters the creation date.
Define your relationship between LineItems and Pricing like this:
LineItems::CreationDate > Pricing::EffectiveDate AND
LineItems::ProductID = Pricing::ProductID
Double click a relationship line in Manage | Database | Relationships to open a dialog for specifying relationship details. Not only can you use this dialog to select a second pair of match fields and change the default = operator to >, you can click a sort button for Pricing and specify that the records be sorted by EffectiveDate in descending order in order to make the most recently dated related record the first related record so that you look up feature will work to copy over the correct data.