2 Replies Latest reply on Jan 15, 2014 10:51 AM by MarkMartin_1

    Date Range Value Lookup



      Date Range Value Lookup


           I need to create script that will look up a price based on the date it was sold.


           I have 2 tables with a relationship between the 2 ProdID fields

           Product table:ProdID, Description and Image fields

           Price table: ProdID, PriceDateStart, PriceDateEnd and ProdPrice fields


           Hope this makes sense



        • 1. Re: Date Range Value Lookup

               You don't actually need the PriceDateEnd field.

               You can't look this data up into the Product table as it does not have a date sold field nor should it. I will assume that you are looking up this price in a LineItems or InvoiceData field using the typical Invoices---<LineItems>----Products data model.

               Set up this relationship.

               LineItems::DateSold  > Price::DateStart AND
               LineItems::ProdID = Price::ProdID

               Double click the relationship line for this relationship, click the sort button on the Price table side and specify a sort order that sorts the records by DateStart in descending order. The first related record in this relationship then becomes the most recent price where the date start is on or before the datesold.

               You'll need to set up LineItems::DateSold to auto-enter that value from a date field in your invoice table.

               With this relationship, your LineItem (or InvoiceData) table can auto enter the correct price based on the data the item was sold. Past and future prices will be ignored.

          • 2. Re: Date Range Value Lookup

                 Worked great! I appreciate it, never would have figured out that solution.