4 Replies Latest reply on Jul 18, 2010 4:55 AM by TheGor

    Calculation with selected Records from one Table

    TheGor

      Title

      Calculation with selected Records from one Table

      Post

      Hallo all,

      I am new in Filemaker but have some experience in SQL but dont now how to do that in Filemaker.
      I want to calculate some selected records inside a table see below.

      Timestamp StationCode In Out
      11.7.2010 0:00 62753 1267147 1178922
      11.7.2010 0:00 62754 2042639 1963830
      11.7.2010 0:00 62755 526172 575644
      11.7.2010 0:00 62756 846324 814993
      11.7.2010 0:00 62757 771005 732987
      11.7.2010 0:00 62758 865027 873640
      12.7.2010 0:00 62753 1269803 1181184
      12.7.2010 0:00 62754 2042689 1964010
      12.7.2010 0:00 62755 526172 575644
      12.7.2010 0:00 62756 846424 814993
      12.7.2010 0:00 62757 771211 733143
      13.7.2010 0:00 62753 1270687 1183020
      13.7.2010 0:00 62754 2042689 1964010
      13.7.2010 0:00 62755 526172 575644
      13.7.2010 0:00 62756 846424 814993
      13.7.2010 0:00 62757 771231 733143
      13.7.2010 0:00 62758 865027 873640

      I WANT:
      (In - Out) from 13.07.2010 and (In - Out) from 11.07.2010 where StationCode = 62753
      (1270687 - 1183020) - (1267147 - 1178922) = -558

      In my Layout i want to define Time Perion and Sation Code, how to get this working in Filemaker?


      Thanks Igor

        • 1. Re: Calculation with selected Records from one Table
          LaRetta_1

          In FileMaker, queries are finds.  And you can use relationship to filter your queries.  So go to your graph (File > Mangage > Databases and select your Relationships tab.  Select your table occurrence and click ++ to duplicate it. Then use cursor to drag line from Station Code to Station Code (it will establish relationship 'filter' on Station Code using = ). This is called a selfjoin so for this example, name it sj.

          Now create a calculation in your table as:

          Let ( [

          first = sj::out - sj::in ;

          last = Last ( sj::out ) - Last ( sj::In )

          ] ;

          last - first

          )

          There are other approaches but it would depend upon 1) your FM version, 2) how you want the results displayed (whether you want to see each calculation result when view the Station Code records or whether you want a report.  

          If you want a report and the results to look something like this ...

          July, 2010

          62753 ... 558

          62755 ... 90

          62756 ... 110

                TOTAL July, 2010 ... 700

          August, 2010

          62753 ... 300

          (etc)

          ... then we would need to also join on month/year and you will need to create a find for the time period and then create a columnar list layout grouped first by month/year and then by Station Code with sub-summary. 

          • 2. Re: Calculation with selected Records from one Table
            TheGor

            Hi LaRetta,

            how can I define in this way start and end date? My result should be displayed im "Form View"
            becouse of better visualisation i will use some grafics in background.

            1) FM 11
            2) Want the results in Form View

            Maybe it is simpler when you see what i mean:

            Here is my example_file


            Thanks Igor

            • 3. Re: Calculation with selected Records from one Table
              LaRetta_1

              Great example!  I've uploaded your file with the changes.  I'm not clear whether you want the start to be EQUAL to the timestamp you enter or if you want simply the earliest Station Code entry after the start time you entered.  Same with end.  And it seemed confusing that the In is greater than the Out, so this can't be time seconds.

              I'm sure you can adjust to meet your needs; if not please let us know and we can help you adjust it.  One thing - you will want to remove the dash from your field name because FM (and many database programs) don't like it. :^)  You can download the revised file here:

              http://www.4shared.com/file/DYDf3x64/exampleREV.html

              UPDATE: For some reason, I cannot use links - sorry about the format of it.

              • 4. Re: Calculation with selected Records from one Table
                TheGor

                This is extra good! :-) thanks LaRetta!

                Now i understand how to use self-join, FM rocks :-)