13 Replies Latest reply on Feb 17, 2017 10:36 AM by dale_allyn

    ExecuteSQL SELECT Count Calculation w/JOIN

    dale_allyn

      I thought this would be quick and easy, but it's stumped me today:

       

      Tables: Clients -< Rpt_Submissions -< Sub_Line_Items

       

      On Clients main layout, I'm trying to populate "Total Report Submissions" calculation field for the current client record, via script trigger on record load.

       

      This statement (below) yields the total number of records in Rpt_Submissions, not filtering for client ID. Each ID field type is the same (Text). $$clientID is set on record load (it's used for other scripts moving out/In to the layout). Hardcoding the client ID into the statement (in lieu of the variable) yields 0 records. I'm missing something obvious, but it's not obvious to me today.

       

      ExecuteSQL ( "

      SELECT COUNT (Sub_Line_Items.subLine_UUID) FROM Sub_Line_Items

        JOIN Rpt_Submissions

        ON Rpt_Submissions.submssn_ID = Sub_Line_Items.Submission_ID

        WHERE Rpt_Submissions.client_ID =? ";""; $$clientID

        )

       

      Running a native find query works, but it's ugly (to me) to jump to the Rpt_Submissions layout, do the find, set a variable and return, just to populate a field for a count. If there's a more performant approach I'd love the input as well.

       

      Thank you.

        • 1. Re: ExecuteSQL SELECT Count Calculation w/JOIN
          nicolai

          I think you are counting the wrong things - line items instead of submissions.

           

          Try this:

           

          ExecuteSQL ( "

            SELECT COUNT (Rpt_Submissions.submssn_ID) FROM Rpt_Submissions

            WHERE Rpt_Submissions.client_ID = ? ";""; ""; $$clientID

            )

           

          I assume submssn_ID is a primary key in the Rpt_Submissions table and it has client_ID foreign key.  If it is the case you can simplify formula into:

           

          ExecuteSQL ( "

            SELECT COUNT (submssn_ID) FROM Rpt_Submissions

            WHERE client_ID = ? ";""; ""; $$clientID

            )

          Also, you can pass Rpt_Submissions::client_ID into the calculation instead of global variable

          • 2. Re: ExecuteSQL SELECT Count Calculation w/JOIN
            nicolai
            Hardcoding the client ID into the statement (in lieu of the variable) yields 0 records.

             

            I think, you are missing one ""; parameter from your ExecuteSQL statement.

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL SELECT Count Calculation w/JOIN
              dale_allyn

              Thanks very much, Nicolai, for your reply.

              nicolai wrote:

               

              I think you are counting the wrong things - line items instead of submissions.

              I need to count the submission line items. Each submission is a transaction where the client may present 1 to n items for analysis. A single submission may have 100 items (for example), each with a separate report. I need to count the total number of reports ordered.

              • 4. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                dale_allyn

                nicolai wrote: "I think, you are missing one ""; parameter from your ExecuteSQL statement."

                 

                Thanks. I'll look at that for testing the SQL statement, but I'll need the variable filter in some form.

                • 5. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                  nicolai

                  I need to count the submission line items

                   

                  In this case your statement looks fine. Did you add another ""; to the formula?

                   

                  I would start select from Rpt_Submissions, although I do not think it will make much difference:

                   

                  ExecuteSQL ( "

                    SELECT COUNT (Sub_Line_Items.subLine_UUID) FROM Rpt_Submissions

                    JOIN Sub_Line_Items

                    ON Rpt_Submissions.submssn_ID = Sub_Line_Items.Submission_ID

                    WHERE Rpt_Submissions.client_ID =? ";""; ""; $$clientID

                    )

                  • 6. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                    dale_allyn

                    nicolai wrote:

                     

                    Hardcoding the client ID into the statement (in lieu of the variable) yields 0 records.

                     

                    I think, you are missing one ""; parameter from your ExecuteSQL statement.

                    Thanks for this. At least it created a change. I added the missing quote pair ("";) parameter, for both hardcoded and variable, but now it returns the count for the first account in Clients only, on all client records. The value is correct for the first record in Clients, but every client record has the same value.

                    • 7. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                      nicolai

                      Check the $$clientID variable is populated correctly, but I think the problem is with your calculation not being refreshed. Try to make it unstored calculation and add commit records/refresh when you set the global variable

                      • 8. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                        dale_allyn

                        nicolai wrote:

                         

                        Check the $$clientID variable is populated correctly, but I think the problem is with your calculation not being refreshed. Try to make it unstored calculation and add commit records/refresh when you set the global variable

                        Yes, I'm watching the data viewer (and throw a debugging dialogue when I'm really feeling paranoid ) and the variable is correct. It's correct on each Client record.

                         

                        I swapped the target table, but that broke it (returning 0 records).

                         

                        Your added ""; parameter helped me get a step closer, in that the output is correct for the first record in "Clients", but it's not updating on each Client record. That is, not recalculating for each client record as the record is loaded.

                         

                        Thanks again, Nicolai.

                        • 9. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                          nicolai

                          Copy and paste the formula into the data viewer and check if it is changes when you switch from one record to the other.

                           

                          As you use this as a calculation for the field, I think using a filed value as opposite of global variable is a better approach. Try to use Client::client_ID instead of $$clientid

                           

                          ExecuteSQL ( "

                          SELECT COUNT (Sub_Line_Items.subLine_UUID) FROM Sub_Line_Items

                            JOIN Rpt_Submissions

                            ON Rpt_Submissions.submssn_ID = Sub_Line_Items.Submission_ID

                            WHERE Rpt_Submissions.client_ID =? ";"";""; Client::client_ID

                            )

                          2 of 2 people found this helpful
                          • 10. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                            dale_allyn

                            Update: So the $$clientID is not populating correctly for the purpose of this SQL statement. The global variable is being set correctly, but the SQL statement is ignoring it. If I hardcode the client_ID into the SQL statement I get the correct value, but it is populated across all records. In other words, this function:

                             

                            ExecuteSQL ( "

                            SELECT COUNT (Sub_Line_Items.subLine_UUID) FROM Sub_Line_Items

                              JOIN Rpt_Submissions

                              ON Rpt_Submissions.submssn_ID = Sub_Line_Items.Submission_ID

                              WHERE Rpt_Submissions.client_ID =? ";"";""; "AC106"

                              )

                             

                            ...presents the correct value for that account (AC106), but populates the count field on all Clients records. This is the same result if the variable is in place. So, what is not happening, is when a record is loaded, the client_ID value is not evaluated in the SELECT COUNT.

                             

                            What am I missing?

                            • 11. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                              dale_allyn

                              Nicolai, you're a gentleman and a scholar. Thank you so much for this. This was where it broke. Replacing the variable with the actual table::field (Clients::client_ID).

                               

                              Debugging FMP SQL statements is not like debugging on LAMP stacks or similar. It's rather blind or unintuitive (to me).

                               

                              Also, thanks for catching my missing ""; parameter. I thought I had fixed that, but I think copy/paste caught me. I appreciate your help.

                              • 12. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                                beverly

                                Correct: debug not easy with this. The query is just TEXT that gets evaluated by the function. I might use the $variables to set the query and other things in a Let() statement just so I can see what gets what in Data Viewer. Using the EvaluationError() around ExecuteSQL() can give you a number clue for errors, as well.

                                EvaluationError(ExecuteSQL( ... ))

                                see this excellent article for another way to "debug":

                                beverly

                                2 of 2 people found this helpful
                                • 13. Re: ExecuteSQL SELECT Count Calculation w/JOIN
                                  dale_allyn

                                  Hi, Beverly, Thanks for this added information. I had a Let() function statement in place earlier, but when the SQL statement was breaking I pared everything down to the simplest form to remove potential errors on my part. I like your idea of wrapping it in the EvaluationError() to get some feedback. Thanks.

                                   

                                  I've read your excellent "Missing Manual for ExecuteSQL" and knew you'd have ideas here. I'll look at your link above for further reading. Thank you.