5 Replies Latest reply on Jan 28, 2016 5:46 PM by Steve Wright

    Comparing Search Results over a Time Frame

    rabendroth

      My db is tracking year over year results, say of widgets sold daily.  Each widget sale makes a record with a date and a dollar value.  In analyzing my data, I can easily find a specific time frame (like all of 2014) and see the total Widget sales per month or by color, or whatever.  What I can't do, and want to, is to compare the results of 2014 to 2015 .... compare search results of different time frames.  Anyone know how to do this?

       

      I was thinking of searching says 2014 and using "Set Variable 1" of search one and "Set Variable 2" for search two and then maybe placing the results of "Set Variable 1" and "Set Variable 2" in new fields of a new record that can then compare ... am I on the right track or is there a better way?

        • 1. Re: Comparing Search Results over a Time Frame
          Steve Wright

          There's always more than one way to skin a cat in FileMaker

           

          Doing what you propose with variables is one way you could do it, although you don't necessarily need the new fields / records to display the results unless you wanted to store them for future reference etc.   You could instead use global fields / global variables and merge fields to achieve the same without having to manage temporary records.

           

          You can also make a sub summary report:

          Simple example of a subsummary report | FileMaker

           

          And then there's ExecuteSQL and no doubt many other ways.

          • 2. Re: Comparing Search Results over a Time Frame
            siplus

            Create 2 repeating fields, comparethis and comparewith, each having 12 repetitions, numeric, fill them with whatever you want via a script (yes, ExecuteSQL can help filling the repetitions) and use them.

            • 3. Re: Comparing Search Results over a Time Frame
              rabendroth

              I understand your answer and can easily execute it.  For my level of FM coding, it works.  I am going to explore the ExecuteSQL talked about by siplus.  Thanks for your help.

              • 4. Re: Comparing Search Results over a Time Frame
                rabendroth

                The SQL solution rocked my world.... cool ... clean and simple ... now I have to figure out how to write SQL and embed it in Filemaker.  Feels like I am starting all over again....

                 

                After reading your post, I went and read the Function Reference for ExecuteSQL: ExecuteSQL

                In the link, I recreated the databases for their example and copy pasted the ExecuteSQL code ... and to my shock it worked ... not certain how that ragged looking code did anything ... thought it was abbreviated and would need cleaning up:

                 

                100 * Salary::Salary / ExecuteSQL("select sum(S.salary) from Employees E join Salary S on E.EmpID = S.EmpID where E.Department = ?"; ""; ""; Department)


                How does S.salary mean anything (shouldn't it be sum(Salary::Salary)) ... or from Employees E join Salary S (what the heck)... even the E.EmpID = S.EmpID looks abbreviated (expected EmpID = Salary::EmpID)....

                 

                Where to begin?

                • 5. Re: Comparing Search Results over a Time Frame
                  Steve Wright

                  rabendroth wrote:

                   

                  The SQL solution rocked my world.... cool ... clean and simple ... now I have to figure out how to write SQL and embed it in Filemaker.  Feels like I am starting all over again....

                   

                  After reading your post, I went and read the Function Reference for ExecuteSQL: ExecuteSQL

                  In the link, I recreated the databases for their example and copy pasted the ExecuteSQL code ... and to my shock it worked ... not certain how that ragged looking code did anything ... thought it was abbreviated and would need cleaning up:

                   

                  100 * Salary::Salary / ExecuteSQL("select sum(S.salary) from Employees E join Salary S on E.EmpID = S.EmpID where E.Department = ?"; ""; ""; Department)


                  How does S.salary mean anything (shouldn't it be sum(Salary::Salary)) ... or from Employees E join Salary S (what the heck)... even the E.EmpID = S.EmpID looks abbreviated (expected EmpID = Salary::EmpID)....

                   

                  Where to begin?

                   

                  Have a look through here for more on ExecuteSQL (lots of links and helpful info) even if you know SQL

                  http://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/


                  To answer your question.. basically yes it's shorthand but not for the table overall.. shorthand for that occurrence of the table. Instead of "from Employees E" or "join Salary S" It can also be written "from Employees as E" or "join Salary as S"

                   

                  Using joins / more complex queries, you may refer to multiple occurrences of the same table, so you can no longer refer to it as just "TableX", much in the same way as you cannot in the relationship graph.


                  To be fair, there's likely somebody else out there who can explain it all better than me.. I know how to write SQL queries, I understand what's what (mostly) but when it comes to explaining things... I usually hide in a corner