3 Replies Latest reply on May 23, 2012 2:25 PM by philmodjunk

    Compare Prices from Different Dates



      Compare Prices from Different Dates



      I've read through the Filemaker Training Series and still can't grasp reporting and structuring tables.  Here's what I want to do and thanks in advance for any help.

      I have a product, price and date and want to compare prices on different dates.  So apples are $5 on 1/1, $6 on 2/1 and $7 on 3/1.  How do I set up my table(s) and how can I get a report that allows me to choose "apple", "date 1" and "date 2" and will yield the 2 prices.



        • 1. Re: Compare Prices from Different Dates

          There are multiple options possible for doing that.

          The most simple (for the designer) that I can think of:

          Table: Transactions

          Date   : Date field
          Price   : number field
          Name  : Text field

          What you describe would be three records.

          to see the records and prices for all apples purchased from 1/1/2012 to 2/1/2012 (don't know if you are using MMDDYYY or DDMMYYY, but it doesn't matter for this example):

          Enter find mode.
          put "apple" in the name field
          put 1/1/2012...2/1/2012 in the date field.
          Perform the find.

          A table or list view can enable you to see the results of this find as multiple rows of records in a "spreadsheet" like format. This can also be done with a script.

          • 2. Re: Compare Prices from Different Dates

            Great.  Thanks.  Now, how would I set up to report just 2 dates (not consecutively).  So if I wanted to compare the 1/1/12 price vs the 5/1/12 price, and none in between.  (I'll eventually add a calculation field to show % change, etc).

            • 3. Re: Compare Prices from Different Dates

              Option 1:

              Enter find mode

              specify date and name

              select "new request" from the Requests menu

              specify date and name (same name, different date)

              Perform the find.

              And this can also be done in a script.

              There's also a "duplicate request" option you can use so that you need only change the date for the second request. It doesn't make much difference in this example, but that can save a lot of effort if you have two requests where the criteria in mulitple fields is the same.

              Option 2:

              Enter find mode

              Specify date and name

              Perform find

              Enter find mode again

              specify date and name

              Select "Extend found set".