5 Replies Latest reply on Jun 27, 2011 4:34 PM by philmodjunk

    Auto-Create Potal entry?

    DeanMoray

      Title

      Auto-Create Potal entry?

      Post

      FM 11

      New to FM so please bear with me.  I have a PRODUCTS table with information about the products with a related PRICE table that has the key field, a date field, and a price field. I want to easily update prices but also track price changes for each product.so i can track price history.

      What I am trying to do is have a list of all products from one vendor with the current prices showing and if I change the price in that list, it auto-enters the new price and current date into PRICE table for that product.

      I figured out how to have a price field in the Product table be a calculation of the last portal (sorted by date). But if I use that field in my products list, it is a calculation and I dont want to enter the price directly into it and delete that calculation

      I am looking for suggestions on how best to accomplish this.

      Thanks.

        • 1. Re: Auto-Create Potal entry?
          philmodjunk

          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.

          • 2. Re: Auto-Create Potal entry?
            DeanMoray

            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

            • 3. Re: Auto-Create Potal entry?
              philmodjunk

              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)
                                        |
                                        ^
                                    PricingList>------Vendors

              You can also add additional table occurrences of Products and vendors to directly link vendors to product records and PricingList records to Products records.

              • 4. Re: Auto-Create Potal entry?
                DeanMoray

                 

                I have it set up somewhat differently as shown below. Does mine work?

                                                               Vendors  

                                                                |
                                                                ^
                       Invoices----Line Items ----Products                         
                                                               |
                                                               ^
                                                         PricingList
                
                

                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?

                • 5. Re: Auto-Create Potal entry?
                  philmodjunk

                  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.)