1 2 Previous Next 15 Replies Latest reply on Aug 14, 2015 2:02 PM by erolst

    Complex SQL query

    sccardais

      I would like some help writing an SQL query to export unique records from a single table, USERS. USERS is a master list of all current and previous Users of our product. There are about 75,000 records.

       

      After excluding previous clients and inactive users, we want to generate a report that lists a count of unique Usernames and a count of unique email addresses for each of our current clients.

       

      Unique Users and emails by Org.png

       

      So, in summary, we want to create a found set of records in USERS with USERS::Status = "Client" and USERS::Restrictions = "None" From this found set, we want to create a report with three columns:

       

      • Org ID
      • Count of Unique Usernames
      • Count of Unique email addresses.

       

      Ideally, I would like to export this report to a spreadsheet.

       

      We are using FMPA 14.01.

       

      This seems like something better suited for an SQL query but its complexity is beyond my experience. Any help would be appreciated.

        • 1. Re: Complex SQL query
          erolst

          Try

           

          ExecuteSQL ( "

            SELECT id_org, COUNT ( DISTINCT email ), COUNT ( DISTINCT username )

            FROM Users

           

            GROUP BY id_org

            " ; "" ; ""

          )

          • 2. Re: Complex SQL query
            sccardais

            Thank you for the fast reply.

             

            Regarding the first steps to limit the scope of the report to Status = “Client” and Restrictions ‘ “None”, YES. Very easy to do with a simple find in FMP but I am assuming these filters will need to be part of an SQL query to get a count of unique values for username and email_address. I have very little experience with SQL hence my request for help.

             

            The fields needed to generate the report are in a single table, USERS.

             

            The fields needed are:

            Org_ID

            not a unique field in this table. a foreign key as you said

            Username .

            Unique value in this table.

            Email_address

            not a unique field. Users sometimes share email addresses. We want to quantify this.

            Status

            Restrictions

            The “Count of Unique Email" and "Count of Unique Usernames" are results I want to generate for each ORG_ID with the SQL query.

             

            Thanks again for the fast reply.  Hope I answered all of your questions.

            • 3. Re: Complex SQL query
              sccardais

              I re-read your reply and it triggered another idea.

               

              I have another table, ORGS that holds information about past and present customers. It is linked to USERS with ORG_ID.

               

              I could add fields in ORGS to hold a count of the Unique Usernames and Unique email-addresses from Users. Since we really want to know the count of unique usernames and email addresses for each Org, perhaps it makes more sense to create fields in ORGS to hold this information and write a script to loop through the records sequentially.

               

              The script could be run periodically overnight. Doesn’t need to be real time.

               

              Do you think that would be a better approach?

              • 4. Re: Complex SQL query
                erolst

                Sorry, forgot about the search criteria.

                 

                You didn't mention this (before), but you have of course an Orgs table, since status and restrictions are attributes of an organization, not a user. So try

                 

                ExecuteSQL ( "

                  SELECT U.id_org, COUNT ( DISTINCT U.email ), COUNT ( DISTINCT U.username )

                  FROM Users U

                 

                  JOIN Orgs O ON U.OrgID = O.id

                 

                  WHERE

                    O.Status = ? AND

                    O.Restrictions = ?

                 

                  GROUP BY U.id_org

                  " ; "" ; "" ; "Client" ; "None"

                )

                • 5. Re: Complex SQL query
                  erolst

                  sccardais wrote:

                   

                  I re-read your reply and it triggered another idea.

                   

                  I have another table, ORGS that holds information about past and present customers. It is linked to USERS with ORG_ID.

                   

                  I could add fields in ORGS to hold a count of the Unique Usernames and Unique email-addresses from Users.

                  The script could be run periodically overnight. Doesn’t need to be real time.

                   

                  Do you think that would be a better approach?

                   

                  That really depends on your requirements; if you need these data continuously on display, you could define calculation fields instead of a script**; if you also want these data stored, then use a script with the same calculations to write the results.

                   

                  But if you only need them for export, then just calculate them on demand.

                   

                  btw, you wouldn't necessarily need a loop; if there is no danger of record locking, two Replace Field Content steps with

                   

                  ExecuteSQL ( "

                    SELECT COUNT ( DISTINCT email ) /* COUNT ( DISTINCT username ), respectively */

                    FROM Users

                   

                    WHERE

                      Status = ? AND

                      Restrictions = ? AND

                      OrgID = ? /* the foreign key */

                   

                    " ; "" ; "" ; "Client" ; "None" ; OrgID // the primary key from Orgs

                  )

                   

                  **with the caveat that when using ExecuteSQL, this can impact your performance negatively under certain circumstances.

                   

                  You can do all this also with “native” tools.

                   

                  Considering that, per your screenshot, each username is unique across the table (and you validate it as such), it must also be unique within an org; so you wouldn't need DISTINCT, and you could count them simply with

                   

                  Count ( Users::id )

                   

                  To get a unique count of (shared) addresses, create a value list from the field eMail, and use

                   

                  ValueCount ( FilterValues ( ValueListItems ( "" ; "eMailVL" ) ; List ( Users::eMail ) ) )

                  • 6. Re: Complex SQL query
                    sccardais

                    Thank you.

                     

                    This looked like it was going to work (testing in Data Viewer) but after about 30 seconds, FileMaker “stopped responding” (Mac OS X error message and I had to force quit.

                     

                    Do you think this could be caused by the number of records in USERS? Total over 75,000 but only about half that current and not restricted.

                     

                    I’ll try the query again to see if FileMaker freezes up again.

                    • 7. Re: Complex SQL query
                      sccardais

                      Progress …

                       

                      It took a while but eventually it reported the results in DataViewer.

                       

                      But, for some reason, every row (OrgID) showed only 1 unique email. The count of unique Usernames appears to be correct but the count of unique email address isn’t. Every row shows a count of only 1 unique email address.

                      • 8. Re: Complex SQL query
                        erolst

                        sccardais wrote:

                        I’ll try the query again to see if FileMaker freezes up again.

                         

                        Make sure to use the amended code using the JOIN, which should get rid of the undesired 50%.

                         

                        btw, maybe post your code so we have a version that uses your nomenclature.

                        • 9. Re: Complex SQL query
                          sccardais

                          Don’t see amended code using JOIN.

                           

                          I will post my code when I return from short errand.

                           

                          Thanks again for your help.

                          • 10. Re: Complex SQL query
                            erolst

                            Actually, my code had the opposite problem, because "username" is an SQL keyword; it's count will always be 1, since there is always one current user in the system. Maybe your email field name is also a keyword?

                             

                            You can test this (and remedy the problem) by renaming the field, or using a quoted reference: COUNT ( DISTINCT \"emailaddress\" )

                             

                            Again a good reason to use functions that guard against name changes by extracting the file or table name and quoting it; who can memorize all those keywords (or would want to)?

                            • 11. Re: Complex SQL query
                              sccardais

                              erolst

                               

                              Thank you very much. It worked.

                               

                              It took a little over 5 minutes for the calculation to complete but, it worked.

                               

                              This is the SQL expression.

                               

                              ExecuteSQL ( "

                               

                                SELECT OrgID, COUNT ( DISTINCT Email_User ), COUNT ( DISTINCT ABC_UserName )

                               

                                FROM TO1_USERS

                               

                               

                               

                                WHERE

                               

                                 Status = ? AND

                               

                                  Restrictions = ?

                               

                               

                               

                                GROUP BY OrgID

                               

                                " ; "" ; "" ; "Client" ; "None"

                               

                              )

                              ————

                               

                              I greatly appreciate your help.

                               

                               

                              FYI … we use FMP to track Customer Surveys. (table = SURVEYS). Each month, we create a new invitation list from the USERS table that excludes users who have submitted a Survey in the previous 12 months. The link between SURVEYS and USERS is “username.” We’ve been using the Username as the basis for exclusion. Since many of our Users share email addresses with others at their company, Users who have submitted a Survey in the past 12 months are not necessarily excluded if they share an email address. We were considering changing the exclusion criteria to email address but wanted to understand the impact.

                               

                              The report generated by your query tells us that over half of our customers have less email addresses than username so excluding by email address could have a big impact.

                               

                              My next challenge will be linking SURVEYS to USERS to see exactly how many Users will be excluded if we use the email address of people who have submitted a Survey in the previous 12 months.

                              • 12. Re: Complex SQL query
                                erolst

                                You're welcome. Good to see it works.

                                 

                                On to the next challenge …

                                • 13. Re: Complex SQL query
                                  sccardais

                                  erolst

                                   

                                  I see your JOIN code now. Don't know how I missed it before.

                                   

                                  Actually, "Restrictions" is an attribute of a User, not an Org. If a User leaves and organization but the Org continues as a customer, the User record is flagged in USERS:: Restrictions.


                                  Status is an attribute of an Org.

                                   

                                  But, other than for practice with SQL, there's no need to use the Join table to get the count I was looking for since the other approach worked.

                                   

                                  Thanks again

                                  • 14. Re: Complex SQL query
                                    sccardais

                                    erolst

                                     

                                    What’s the protocol here? Should I create a new discussion or continue this discussion for the next challenge I mentioned?

                                    1 2 Previous Next