2 Replies Latest reply on Dec 13, 2016 4:56 AM by fmpdude

    Comparing result(s) from previous record


      I have a database of weekly performance results of my portfolio.

      I want to create a field which displays the amount of increase or decrease between today's result (current record) compared to last week (previous record).

      As I am undertaking a re-write of the database I have not established field names at this time.

      Any assistance to guide me on a suitable calculation would be appreciated.

        • 1. Re: Comparing result(s) from previous record
          Johan Hedman

          You have two approaches for this


          1. Save each week's totals in a new Table so that you easily can compare your records. This can be done creating a Script that you have FileMaker Server do a Scheduled Script runt every Sunday evening


          2. Use ExecuteSQL to compare two searches

          • 2. Re: Comparing result(s) from previous record

            Based on my understanding of what you said, I would start like this (using an ERD tool!)


            1. Create three tables (named more for understanding here):






            PERIOD holds information about each performance period (weekly, or other).


            This PERIOD table links 1:M to the PERFORMANCE table where you store one or more performance values for the Primary Key in the PERIOD Table.


            Finally, the PERFORMANCE TABLE links 1:1 with the RESULT table to display/store the value. That final joining line isn't shown above, but you would update the RESULT table.


            With this design, you can have as many "performance values" for a given "period" as you want and a single calculated result for a given period in the period table.


            In FileMaker ... (note that the ERD tool above, can CREATE YOUR FILEMAKER DATABASE DIRECTLY):


            (Note: FileMaker does not let you adjust the "cardinality" of joined relationships so it "incorrectly" assumes the join between PERIOD and RESULT is 1:M when it wouldn't be.)

            Without knowing more about what you want to do and seeing sample data, etc., this is just an initial idea and jumping off point.


            HOPE THIS HELPS.