2 Replies Latest reply on Jul 8, 2016 8:23 AM by sccardais

    Help With Relationship to Count Related Records

    sccardais

      I'm trying to create a Customer Retention report and having trouble creating a relationship that will allow me to count records that match my criteria.

       

      TABLES

       

      • Table::YEARS holds starting and ending dates and additional fields to hold counts of customers at the beginning and end of each year. This table currently has 7 records starting in 2010.

       

      • Table::ORGs holds all customer information including Date Ordered and, if applicable, the Date Canceled.

       

      RETENTION REPORT

       

      Each row in the Retention report shows the Retention Rate for a specific Year.

       

      The Retention formula requires three values:

        • Count of Customers at the Start of the Year
          • Orgs that ordered before the Start_Date and didn't cancel before the Start_Date. (Problem getting this.)
        • Count of Customers at the End of the Year
          • Orgs that ordered before the End_Date and didn't cancel before the End_Date. (Problem getting this.)
        • Count of Customers Added during the Year
          • Orgs that ordered in the date range for each year. (Not a problem.)

       

      I've tried to get these counts using relationships between YEARS and ORGS but can't find a way to exclude organizations that canceled their service before the Starting period. To summarize:

       

      • The Count of Orgs at the start of each year should be the number of Orgs where the Order Date is before the Start Date minus the number of Orgs where the Date Canceled (if any) is also before the Start Date.

       

      • The Count of Orgs at the End of each year should be the number of Orgs where the Order Date is before the End Date minus the number of Orgs where the Date Canceled (if any) is also before the End Date.

       

      Is there a way to do this via relationships?

        • 1. Re: Help With Relationship to Count Related Records
          mikebeargie

          You might want an introduction to ExecuteSQL() so you do not need to establish relationships to get reporting data.

           

          This should be pretty simple:

           

          ExecuteSQL( "SELECT COUNT(*) FROM ORGs WHERE Start_Date >= ? AND Cancel_Date IS NULL" ; "" ; "" ; GetAsDate( "1/1/" & Year (get(currentdate)) ) )

           

          That counts the number of ORGs with a start_date greater/equal to the first of the current year, and the cancel_date is empty.

           

          You can use the Tools > Data Viewer to play around with that calculation until it returns the value you are expecting. There is a great guide on using the ExecuteSQL() function here:

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

          Make sure to read the comments as well.

           

          It's worth training on, since it makes reporting a LOT easier since you don't have to setup fields, calculations and relationships to get summaries through constrained or dynamic relationships.

          • 2. Re: Help With Relationship to Count Related Records
            sccardais

            Thanks, Mike.

             

            I’ve had trouble with ExecuteSQL before so I found another, very cumbersome way to do what I wanted. Using several relationships and count calc fields, I calculated all of the Orders prior to the Start Date and all of the Cancellations before the Start Date. Subtracted one from the other to get Clients as of Start Date. Then repeated for End Date.

             

            FYI … Matching on YEARS::StartDate > ORGs::Order Date threw me a curve until discovered that blank Order Dates in ORGs were apparently counted as matches but I got that sorted out.

             

            Anyway, thanks for your help. I’ve read all of the materials on SQL that you referenced but it seems very “fiddly” to me but I greatly appreciate your reply.

             

            Scott