Keep your pricing table separate from your products table, but relate to your other tables by product ID just like you should with your products table. Add an effective date to each such pricing record. If you plan to include upcoming price changes in this table, include the effective date field in your relationship.
Say you look up prices into a line items table for an invoicing or purchase order system:
LineItems::ProductID = PricingList::ProductID AND
LineItems::Transactiondate > PricingList::EffectiveDate
Double click the relationship line in Manage | Database | Relationships and specify that the PricingList records be sorted in descending order by effective date and your system will look up the price for a given item that has the most recent effective date that is not still a date in the future.
Thanks but I am still confused. I already have a separate table for line items which works fine, That's not my problem here. I have a list of products that I want to have a current cost but also track the price history.
I have a list view of all products from Vendor X which uses a price field that is a calculation of the last price in the price histry table for that product (which is sorted by date so the last entry is the most recent). That all works fine.
What I want is a list view of all products for Vendor X and be able to EDIT the price RIGHT IN THE LIST with a new prices which will update the price in the Price History Table along with todays Date so that the price history is maintained. Unless I misunderstand, I can't use the same price field because that is a calulated field and not editable without deleting the formula.
I thought about using a portal of the price change table in the Products layout List view but dont know if 1) that is possible: 2) whether updasting the price here will do what I want; or 3)that is teh best way to do it. Do I need to
Am I making sense? I am a newbie to FM
I referred to LineItems in an example of how to implement a relationship to a new table called PricingList. That is the new table where you can enter price changes while keeping a history of past pricing for the system. With this set up and the relationship I described, there's no longer a need for putting the current price in your Products table to be looked up into other tables such as line items. You'd look up the prices directly from the PricingList table using a date field to control when a price change becomes effective.
You can also create a vendors table with a portal to this Pricing List table, but you'll need to be careful to create new PricingList records, not to edit the existing records as this would eliminate your pricing history.
In Manage | Database | Relationships, your table occurrences might be linked up like this:
Invoices-----<LineItems>---Products (other data such as weight and description still come from this table)
You can also add additional table occurrences of Products and vendors to directly link vendors to product records and PricingList records to Products records.
I have it set up somewhat differently as shown below. Does mine work?
Invoices----Line Items ----Products
If so, how do I create a simple list of products from a particular vendor that has a field in the layout that allows me to enter a price that will creates a new entry in the price list file without it overwriting an existing value.
I assume I need to start with a list view using values from the Products table. But how do I get an editable price field on that layout that will do what I want it to?
You won't be able to set up price changes with effective dates that take place in the future with your set up as you no longer have a relationship to LineItems that is controlled by ProductID and also by Date. If you never institute future dated price changes, then it will work.
To create your price change list, you can use a script to pull up a vendor's products and then create one new blank record in PricingList for each such product. A looping script or even an Import Records step can be used to set up this list. (A self join can even be used to display the current price for each product.)
(Note, you can have it both ways, you can link one occurrence of Products directly to Line Items and a second occurrence that links directly to the Pricing List.)