1 Reply Latest reply on Jun 18, 2015 9:03 AM by philmodjunk

    How to calculate Latest Supplier and Latest Cost?



      How to calculate Latest Supplier and Latest Cost?


      Hi All!


      i have a Products Table with fields that i would like to tell the latest supplier and latest cost of the product

      latest_supplier = the current supplier where i bought the product

      latest_cost = the price of the product from the current supplier


      i will be using a Purchase Invoice Form with Portal Line Items to enter bought Products from a supplier

      Sometimes another supplier will give you a better price, and you will have a new supplier.

      The Products last_supplier will be different or the same depending on where you bought the products.


      what is the basic way to do this?


      Thank You!




        • 1. Re: How to calculate Latest Supplier and Latest Cost?

          Do you have a table where each purchase is logged with the product ID, supplier ID, Cost and purchaseDate are recorded?

          And do you have a relationship between your products table and this table that matches records by productID?

          If so, you need only define the relationship to be sorted by purchaseDate in descending order. This makes the most recent purchase of that product the first related records.

          From the context of Products, you can then just refer to fields of this related table and the relationship will reference the most recent transaction.