14 Replies Latest reply on Feb 2, 2016 9:22 AM by haggart

    Filemaker Dashboard Summing Records by Year

    haggart

      Hi all,

       

      I've spent a great deal of time trying to figure this out on my own, but am spinning in circles.  My FM is for lab spending for about 30 researchers with 3 research grants.  The dashboard idea is appealing because it is real time and when my boss taps me on the shoulder and asks "how's the budget"  it's easy to show.  We don't need to print out reports.

       

      I based my database on the really helpful demo video and download file from FM Academy.   I've spent many hours listening to the demo and have a fairly clear understanding of how the database was set up.  My own database is working perfectly EXCEPT for one thing.  I want to sum the individual researcher spending for the year.  What I get is a list of researchers with spending for all time.   I went back and checked the FM Academy demo file and it does the same thing!  In the demo video, Sameer Khan, mentions that the customer data is for the year, but it actually isn't.  It's for all time.  The good news is that I'm good at following instructions, the bad news is I'm not sure what to do next!  I've spent several days Google-ing, Lynda-ing and I'm stumped.  So what am I missing?  I'm including screen shots of my set-up vs the FMA set up.

       

      FMA set up and my (Mallory) set up are attached. 

       

      Help!  Please stop my wailing!  And thanks in advance.

        • 1. Re: Filemaker Dashboard Summing Records by Year
          mikebeargie

          Well, based on your screenshots you have cartesian joins, so it will show all of your users, regardless of if they'd have a transaction.

           

          To me, it sounds like your summary and calculations are not quite right.

           

          For each user, you should have a calculation that only calculates what they spent for a year. Then in your dashboard table, a calculation that is just Sum(UsersForYearDashboard::YearSpendingCalc)

           

          For YearSpendingCalc, you could use a pretty easy ExecuteSQL() calculation of:

          ExecuteSQL("SELECT SUM(amount) FROM Orders WHERE theOrdersUserKey = ? AND orderYear = ?";"";"";Users::primaryKey;Year(Get(CurrentDate)) )

          subbing in your field names where I bolded above.

           

          You could then even filter that portal on your dashboard so that only users with a calc of YearSpendingCalc > 0 show up. Omitting anyone that has not placed an order in the current year.

           

          Overall you're probably pretty close, just a few tweaks and you'll be good to go.

          • 2. Re: Filemaker Dashboard Summing Records by Year
            haggart

            Thanks Mike,

            Not there yet, but I've stopped sobbing!

             

            My first attempt did not work.  I got a list of names, but question marks next to them.  So I have a couple of questions about the SQL.

            orderYear - I used the field DateOrdered.  I don't have a field that specifies the year something was ordered.  Is this a problem? 

             

            The amount, theOrdersUserKey and the Users::primaryKey have to be from a related table, right?  Yes, I think.

             

            In my set-up, the UsersForYearDashboard is NOT related to the order field.   But I had created a Order TO called OrdersDateFilter.  So when writing the SQL, the bolded items need to come from either UsersForYearDashboard or OrdersDateFilter - yes?

             

            Yikes!

            Thanks again,

            M

            • 3. Re: Filemaker Dashboard Summing Records by Year
              mikebeargie

              1/1/2016 does not equal 2016. So you need to make a new field in your orders table, number type, called orderYear.

               

              Under field options, give it an auto-enter calculation of:

              Year ( dateOrdered )

               

              uncheck the box below auto-enter calculation so it updates and replaced anytime the dateOrdered value is updated.

               

              Now use the orderYear field in your ExecuteSQL() calc. That SQL calc field goes in your USERS table, NOT in your orders table. Basically with that SQL you're saying "Give me the sum of the amount from the orders table, where the order matches this year, and matching this user.

               

              ExecuteSQL() is context free, meaning it doesn't need to pull anything from related data.

               

              amount - this is from your orders table, which is why it says: Sum(amount) FROM Orders

               

              theOrdersUserKey - this is also from your orders table, which is why it says WHERE theOrdersUserKey = ?. The ? is a placeholder that allows you to pass in a value later. The value you pass in later is from your users table, the user's primary key. Basically this is the part that says, "give me the orders that match this user".

               

              Users::primaryKey - in reality, this will only really say "primaryKey", because Users:: is given since you're calculating this field as part of your users table. This is the value you're passing to that ? in the SQL to match the orders.

               

              Making more sense?

              • 4. Re: Filemaker Dashboard Summing Records by Year
                haggart

                Thank you Mike.  Makes more sense.  I know next to nothing about SQL, but it helps to know it’s context free. And your translation to English makes sense too. 

                 

                Still no luck.

                 

                Orders Table

                Field Name:  OrderYear

                Type:  Number

                Options:  Indexed, Auto-enter Calculation replaces existing value

                 

                Does this look right?

                 

                When I go to another layout based on Orders and add this field, there is nothing in it for any of the records.  Makes me think that I need to do something to go back and populate all the records with the order year. 

                 

                I was using the SQL in the Users table.  So that’s not it!

                Amount is from oOrders, yep got that – in my case the field is called TotalCost (of each other).

                 

                 

                Here is the SQL calculation from the Contest of Users (I added carriage returns to help me see better):

                 

                ExecuteSQL("SELECT SUM(Orders::TotalCost)

                 

                FROM Orders

                 

                WHERE Orders::_UserIDkf = ?

                 

                AND Orders::OrderYear = ?";"";"";__UserIDkp;Year(Get(CurrentDate)) )

                 

                 

                This is so much fun!   =:- O

                • 5. Re: Filemaker Dashboard Summing Records by Year
                  mikebeargie

                  When I go to another layout based on Orders and add this field, there is nothing in it for any of the records.  Makes me think that I need to do something to go back and populate all the records with the order year.

                   

                  Yes - replace the order date with a calculation of itself. This will cause the auto-enter to update. (backup a copy of your data first just in case).

                   

                  ExecuteSQL("SELECT SUM(Orders::TotalCost)

                   

                  NO - just this:

                  ExecuteSQL("SELECT SUM(TotalCost)

                   

                  you specify the table that TotalCost is from after the FROM keyword.

                   

                  WHERE Orders::_UserIDkf = ?

                   

                  NO - just this:

                  WHERE \"_UserIDkf\" = ?

                   

                  Note that I had to use \" to surround that. Because you're using a leading underscore in your field name and SQL doesn't like that. You can also rename your fields a_ instead if you'd prefer, then the \" _field \" wouldn't be required.

                   

                  AND Orders::OrderYear = ?";"";"";__UserIDkp;Year(Get(CurrentDate)) )

                   

                  Nope again:

                  AND OrderYear = ?";"";"";__UserIDkp;Year(Get(CurrentDate)) )

                   

                  Just use the field name for the match to =?, you don't need to specify the table.

                   

                  I would recommend you read this entire article, it will help you understand this function a lot better.

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

                  • 6. Re: Filemaker Dashboard Summing Records by Year
                    haggart

                    Thanks again, Mike.  I appreciate your patience.  I've eliminated _ on my key fields.  So this is the calc now:

                     

                    ExecuteSQL("SELECT SUM(TotalCost)

                    FROM Orders

                    WHERE kfUserID = ?

                    AND OrderYear = ?";"";"";kpUserID;Year(Get(CurrentDate)) )

                     

                     

                     

                    I'm not quite sure how to replace the "order date with a calculation of itself". 

                     

                    In the calculation pane I just entered  DateOrdered

                     

                    DateOrdered    Date   Indexed, Auto-enter Calculation replaces existing value

                     

                    When I browse an order layout, years are not showing up.  If I create a new record and add an order date, the correct year appears in the YearOrdered field.  What am I missing to get older records to update?

                     

                    I've read part of the article you suggested.  It's a lot to digest.  I feel like if I can get this working, I'll be up for the challenge of reading more.

                     

                    Thanks!

                    M

                    • 7. Re: Filemaker Dashboard Summing Records by Year
                      mikebeargie

                      Not an auto-enter field, using the "replace field contents" command.

                       

                      set your cursor in the field,

                      under the "records" menu, you will see replace field contents

                      set it to replace with a calculation of dateOrdered

                      You should now see years populated.

                       

                      Auto-enter only works when a new record is created OR when you change a value of any field referenced in the auto-enter calc. So we are forcing it to re-evaluate by updating the orderDate field on all records at once.

                       

                      Don't forget to back up your data first, there is no undo.

                       

                      When you get to it, start going through the filemaker advanced training series as well.

                      • 8. Re: Filemaker Dashboard Summing Records by Year

                        I would recommend that you take the W3Schools quick course on SQL.

                         

                        SQL Tutorial

                         

                        SQL is easy, yet extremely compact and powerful.

                         

                        With SQL you don't focus on the "how", but the "what" (declarative approach).

                         

                        SQL is EVERYWHERE. Learn SQL and it will be useful whether you're doing FileMaker, MySQL, SQL Server, Oracle, ....

                         

                        - m

                        • 9. Re: Filemaker Dashboard Summing Records by Year
                          beverly

                          Yes! W3Schools is one of my favorite sources.

                           

                          Then go get the PDF (and examples) to see how FM uses SQL in ExecuteSQL() function, because it's not quite 'standard' with other SQL...

                           

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

                          (Japanese version:

                               http://notonlyfilemaker.com/2014/01/missing-executesql/

                          )

                           

                          and more articles here:

                           

                               http://filemakerhacks.com/category/executesql/

                               http://www.seedcode.com/filemaker-13-sql/

                           

                          To help you write the queries (new version!):

                               http://www.seedcode.com/filemaker-sql-explorer/

                           

                          beverly

                          • 10. Re: Filemaker Dashboard Summing Records by Year
                            ninja

                            First things first...Mike's way is more elegant than what I'm about to say...if you can get his way, do it that way.

                             

                            That said, I don't know squat about SQL, but made a year by year dashboard just within FMP back before it could handle SQL statements.

                             

                            I simply self joined a number of TO's through filtered relationships.

                            On layout load (or in a startup script), I set global fields to 2008, 2009, 2010, etc.

                            There is also a calc field for Year(PODate)

                            In self join 1, I link by the ID field AND g2008 -- Year(PODate)

                            My 2008 results are gathered by Sum(TO2008::Value)

                            My 2009 results are gathered by Sum(TO2009::Value)

                            etc., etc.

                             

                            The relationship graph is a whole bunch of filtered self joins, and I have a list of sum fields...it is messy, but it works.

                            {and I told you up front that the SQL method was more elegant}

                            All the same, you can have your dashboard without the SQL function if needed.

                            HTH

                            • 11. Re: Filemaker Dashboard Summing Records by Year
                              haggart

                              OK, looks like I have some reading to do.  I'll be back!

                               

                              I'm coming from the perspective a science research lab manager.  I'm not a programer and I don't do it for a living.  This is the only database I'm going to develop.  Prior to posting on this forum, I came across some of the links mentioned above in the quest to find a solution to sum orders for researchers in a dashboard.  It's way over my head, but I will go back and see if I can glean anything to help solve this. 

                               

                              Thank you everyone, especially Mike - I'll report back soon.

                              • 12. Re: Filemaker Dashboard Summing Records by Year
                                haggart

                                Well, I've watched some SQL videos and read through some of the suggested  links.  Very interesting stuff, but still have a lot to think about.  Still had trouble with the formula Mike suggested.

                                 

                                ExecuteSQL("SELECT SUM(amount) FROM Orders WHERE theOrdersUserKey = ? AND orderYear = ?";"";"";Users::primaryKey;Year(Get(CurrentDate)) )

                                 

                                What finally worked was the following:

                                 

                                ExecuteSQL("SELECT SUM(TotalCost)

                                FROM Orders

                                WHERE kfUserID = ?

                                AND DateOrderYear = ?";"";"";Orders::kfUserID;Year(Get(CurrentDate)) )

                                 

                                I wish I understood why this worked as opposed to Mike's suggestion.  As I said, I have a lot to think about, but I agree it has the potential to be one powerful tool.

                                 

                                M

                                • 13. Re: Filemaker Dashboard Summing Records by Year
                                  mikebeargie

                                  It worked because you used your field names instead of my placeholders. Since I have no idea what your system looks like, I write the SQL with obvious placeholders.

                                   

                                  Sum(amount) would be the sum of your amount field.

                                  theOrdersUserKey would be the foreign key on the order matching the target user.

                                  etc...

                                   

                                  You came to the answer on your own, but your function is exactly the same syntax I wrote with just your fields put in place.

                                  • 14. Re: Filemaker Dashboard Summing Records by Year
                                    haggart

                                    Exactly!

                                     

                                    Most importantly, I'm grateful for your guidance in pointing me in the right direction Mike.

                                     

                                    Thank you,

                                    M