7 Replies Latest reply on Oct 21, 2016 1:31 PM by sccardais

    Relationship Question based on dates

    sccardais

      I am trying to get a count of records in a related table based on date fields but having trouble defining a relationship that will do what I want.

       

      PERIODS: is used to create Rolling 12 Month reports. Each record is a 12 month period defined by two fields:

       

      • PERIODS::DateStart
      • PERIODS::DateEnd.

       

      ORGS: each records holds customer information. Two fields in ORGS should give me what I want;

       

      • ORGS::Date Ordered
      • ORGS::DateCanceled

       

      I want two new fields in PERIODS to show the client count at the beginning and end of each period.

      • PERIODS::ClientsAtStart
      • PERIODS::ClientsAtEnd


      The calculation for PERIODS::ClientsAtStart seems simple. ORGS::DateOrdered < PERIODS::DateStart AND ORGS::DateCanceled is NOT < PERIODS::DateStart.

       

      It's the "NOT"  part of the relationship that I can't work out.

       

      Can this be done with a Relationship?

        • 1. Re: Relationship Question based on dates
          mikebeargie

          No relationship needed, you can just use ExecuteSQL to do this:

           

          PERIODS::ClientsAtStart:

          ExecuteSQL( "SELECT COUNT(DateOrdered) FROM ORGS WHERE DateOrdered < ? And ( DateCanceled > ? OR DateCanceled IS NULL)" ; "" ; "" ; PERIODS::DateStart ; PERIODS::DateStart )

           

          PERIODS::ClientsAtEnd:

          ExecuteSQL( "SELECT COUNT(DateOrdered) FROM ORGS WHERE DateOrdered BETWEEN ? AND ? AND ( DateCanceled > ? OR DateCanceled IS NULL)" ; "" ; "" ; PERIODS::DateStart ; PERIODS::DateEnd ; PERIODS::DateEnd )

           

          Breaking that down, you are looking for:

          1) Orgs that had ordered before the Period start date, and also either have NOT canceled, or their cancelation was sometime after the start date.

           

          2) Orgs that had ordered between the start and end date, and also either have not canceled, or had a cancelation that was sometime after the end date.

           

          You can read up on the ExecuteSQL() usage here:

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

          • 2. Re: Relationship Question based on dates
            sccardais

            Thank you, Mike.

             

            I’ll give this a try. To keep things more simple in my original post, I didn’t mention that I also the Product in the relationship between PERIODS an ORGS.

             

            The calculations in PERIODS are all based relationships that include matching on a global field in PERIODS that matches on ORGS::Product. Changing the values in PERIODS::g_Product, changes the counts which I use for showing results for any combination of Products.

             

            I have very little experience in ExecuteSQL. Is it possible to add this additional criteria to the query you describe below?

             

            If not, is it possible to use Relationships to generate the counts I described?

             

            Thanks very much.

            • 3. Re: Relationship Question based on dates
              mikebeargie

              It is possible via relationships, but it will be much more difficult in the OR instances. EG cancelation being empty, or before/after a certain date.

               

              Almost certainly will be more flexible via eSQL.

               

              Seedcode’s SQLExplorer is a good utility you can hook into your database to get help building advanced queries, may be a good place to start.

               

              And yes, you can add additionaly AND, OR, AND ( OR ) criteria ad nauseum to achieve the results you want. I would suggest adding one at a time though and testing the result each time utilizing the data viewer in FMPA. You can also join multiple tables together to query across two tables at the same time.

               

              EG if you wanted to match a specific product by name, something like:

               

              ExecuteSQL( "SELECT COUNT(DateOrdered) FROM ORGS o JOIN PRODUCTS p ON o.productID = p.productID WHERE p.ProductName = ? AND  o.DateOrdered BETWEEN ? AND ? AND ( o.DateCanceled > ? OR o.DateCanceled IS NULL)" ; "" ; "" ; “Widget 1” ; PERIODS::DateStart ; PERIODS::DateEnd ; PERIODS::DateEnd )

              • 4. Re: Relationship Question based on dates
                mikebeargie

                You can also break up your ExecuteSQL a bit to make it more readable:

                ExecuteSQL( "

                   SELECT COUNT(DateOrdered)

                   FROM ORGS o

                   JOIN PRODUCTS p

                   ON o.productID = p.productID

                   WHERE p.ProductName = ? AND

                   o.DateOrdered BETWEEN ? AND ? AND

                   ( o.DateCanceled > ? OR o.DateCanceled IS NULL)"

                ; "" ; "" ;

                   “Widget 1” ;

                   PERIODS::DateStart ;

                   PERIODS::DateEnd ; PERIODS::DateEnd

                )

                • 5. Re: Relationship Question based on dates
                  philmodjunk
                  I want two new fields in PERIODS to show the client count at the beginning and end of each period.

                  Doesn't that specify redundant results?

                  Won't the count at the end of Period 1 be exactly the same as the count at the beginning of period 12?

                   

                  So maybe you can cut the job in half by calculating only one such value for each period?

                   

                  Edit note: I typed in the wrong period number in my question...

                  • 6. Re: Relationship Question based on dates
                    fmpdude

                    Small point, but I would recommend, instead, RazorSQL. It's totally amazing. Query assist, resizable everything, real errors, etc.

                    • 7. Re: Relationship Question based on dates
                      sccardais

                      Good point!

                       

                      You just cut the problem in half.

                       

                      Sent from my iPad