3 Replies Latest reply on Mar 31, 2010 1:35 PM by philmodjunk

    How to create something like a SQL-view in FM 11?

    Heribert

      Title

      How to create something like a SQL-view in FM 11?

      Post

      Hello!!

       

      I'm new to Filemaker and just testing FM 11 since yesterday. ;-)

       

      My background are all kind of relational databases like Oracle, SQLServer ... and the appropriate tools.

       

      FM is doing well so far, except for some glitches that can be ignored for now...

       

      But i really have a problem that i  need to get solved...

       

      I have a table Tabelle(Datum date, Flag int, Wert1 int, Wert2 int)

      Now, what i need to do is to get the average of Wert1 and Wert2 at each date and flag.

      In SQL, all i would need to do is to create a View from the following SELECT Statement

       

      select Datum, Flag, avg(Wert1),avg(Wert2) from tabelle

      group by Datum, flag

      order by Datum, Flag

       

      and select/display my data from this view.

       

      And now i'm stuck. ;-)))

      How can i accomplish something like this in FM?

       

      Thank you very much for your help!!!!

       

      Cheers,

      Heribert

        • 1. Re: How to create something like a SQL-view in FM 11?
          mrvodka

          You can create a subsummary report grouped by the date and then have two summary fields that takes the average for Wert1 and Wert2. These summary fields shouldbe placed in the subsummary part. Keep in mind that a subsummary report has to be sorted by the break field in this case the date field.

           

           

          • 2. Re: How to create something like a SQL-view in FM 11?
            Heribert

            Hi Mr. Vodka,

             

            that's what i tried today...

            Create a report with summaries, define Datum and Flag as grouping criteria and Wert1 and Wert2 as data.

            To get what i want, i had to hide the data and header sections, so that only the summaries were displayed.

             

            OK, now i have a report that shows the correct values..... but.... i still can't work with the aggregated data....

            I can not use the results as input for let's say graphs or further calculations.

             

            Think about further aggregations... What is the average value of all those average values from above per week?

            That's my problem.

             

            Views are a vital part of any relational database.

             

            If it's not possible to define views, or complex queries, it's a KO criteria for me.

             

            Kind regards,

            Heribert

            • 3. Re: How to create something like a SQL-view in FM 11?
              philmodjunk

              "I can not use the results as input for let's say graphs or further calculations"

               

              If the summary fields are computing sub-totals like they are in this case, use the getSummary function to access the subtotaled amount in a calculation. You also have aggregate functions that can compute totals, counts, averages etc much like a summary field. Sometimes they make a better choice than a summary field.

               

              I've also been able to use summary fields in graphs in Filemaker 11.