4 Replies Latest reply on Aug 9, 2012 11:41 AM by justinc

    ExecuteSQL context different in calc field definition?

    justinc

      I have been working on an implementation of a checklist system and have run into a snag with trying to get a calculation field to figure out how many items on a list are checked off.

       

      I have two 'template' tables for checklists and line items, and then one central 'Responses' table where all the responses to the checklists are kept. A 'response' is a record in that table, copied from the Line Items template. So each Line Item is a different record. A record has the ParentID of the object it is attached to, and the ListID fo the Checklist it is for. (The records in Responses are created by scripts, copying information from the two template tables.) I attached a number of screen captures to show the structure. Briefly:

       

      Checklists:: <= 'ListID' => Line Items::

       

      I am now trying to figure out how to get the right summarization numbers for a given checklist. (I want to provide the end user with a percentage done number, for each checklist. A dashboard overview kind of thing.) I am recording the total number of line items in the Responses table when creating the records. I was then trying to figure out how to get a calculation field to get populated with the number of items marked as 'done' (i.e. checked off the checklist). I can get it to work in the Data Viewer, but when I put that into the field definition calc window, I only get a question mark on my layout.

      Here's the code that works in Data Viewer:

      Let ( [
      _CrsFld = GFN(Checklist_Responses::aParentID_fk);
      _ListFld = GFN (Checklist_Responses::aListID_fk);
      _FromTable = GTN (Checklist_Responses::aaResponseID_pk);
      _RespFld = GFN (Checklist_Responses::LineItem_Value)
      ];

       

      ExecuteSQL(
      "Select count(*) FROM " & _FromTable &
      " WHERE " & _CrsFld & " = " & _CrsFld & " AND " & _ListFld & " = ? " & "AND " & _RespFld & " = ?"
      ; "" ; "" ; Checklist_Responses::aListID_fk; "1" ) //END SQL

       

      ) //END Let

       

      And here's the code that doesn't seem to work in the field definition calc:

      Let ( [
      _ParentFld = GFN(Checklist_Responses::aParentID_fk);
      _ListFld = GFN (Checklist_Responses::aListID_fk);
      _FromTable = GTN (Checklist_Responses::aaResponseID_pk);
      _RespFld = GFN (Checklist_Responses::LineItem_Value)
      ];

       

      ExecuteSQL(
      "Select count(*) FROM " & _FromTable &
      " WHERE " & _ParentFld & " = " & _ParentFld &
      " AND " & _ListFld & " = ? " &
      " AND " & _RespFld & " = ?"
      ; "" ; "" ; aListID_fk ; "1" ) //END SQL

       

      ) //END Let

       

       

       

      I have also attached an image that shows a few screen captures of the table schema (field definitions), the calc field window, and the dataviewer window with result. (End result for this particular list should be 4 checked, out of 49 total.)

       

      A description of what I was thinking of doing in the SQL for the calc field:

      Essentially each record would end up with calculated value for the percentage done in it. Not the greatest way to do it, but my Responses are all in one monolithic table; I thought about creating a CheckList_Summary kind of table as an intermediary.

      The SQL is intended to count the records in the Responses table that match the current record's ParentID, the current record's ListID, and has a Response Value = 1. So, from the context of the record, it should only see responses that are from that same list, and thus be able to narrow it down to the marked off ones.

       

      I hope I provided enough detail about the structure for someone else to follow along. It is all pretty clear to me.

       

      Thanks,

      J

        • 1. Re: ExecuteSQL context different in calc field definition?
          justinc

          (This is most of a posting from the FileMaker forums; so sorry if I failed to repeat parts here that were in that original thead.)

           

          Also, the GTN() and GFN() are custom functions.  They just return the Table Name or Field Name of the provided field.  Some code-robustness copied from Kevin Frank, FileMakerHacks.com.

          • 2. Re: ExecuteSQL context different in calc field definition?
            Mike_Mitchell

            ExecuteSQL is completely context-independent. Hence, you always have to use the fully-qualified field name, including the TO name. I think the problem might be coming from the last bit in your calc. "aListID_fk" should be "Checklist_Responses::aListID_fk".

             

            Give that a try and see if it helps.

             

            Mike

            • 3. Re: ExecuteSQL context different in calc field definition?
              justinc

              I think I had tried both forms and gotten the same result.  I will double check, though.

               

              The form that is there, the not qualified one, is what comes up when you click through the widgets of the calculation box, though.  That is, if I go above and pick my table from the drop down and then double click the field, I just get the field name in the text area.  Seems odd; I guess it doesn't know that I am using ExecuteSQL. 

               

              --  J

              • 4. Re: ExecuteSQL context different in calc field definition?
                justinc

                Well, I did end up putting in a Summary table layer in the structure.  It, surprise, acts to summarize data and points at the individual responses (i.e. each response is associated with one summary).  In that table, this is what I used to count the total number of responses:

                 

                Let ( [

                   _ParentFld = GFN(Checklist_Summary::aaListSummaryID_pk);

                   _ParentFld2 = GFN(Checklist_Responses::aSummaryID_fk);

                   _FromTable = GTN (Checklist_Responses::LineItem_Value);

                   _RespFld = GFN (Checklist_Responses::LineItem_Value)

                ];

                 

                ExecuteSQL(

                    "Select count(*) FROM " & _FromTable &

                    " WHERE " & _ParentFld2 & " = ? " &

                    " AND " & _RespFld & " = ?"

                ; "" ; "" ; Checklist_Summary::aaListSummaryID_pk ;  "1" )    //END SQL

                 

                )   //END Let

                I think that part of my problem was in that I was using field names where I needed actual values: e.g. ID = ID instead of ID = 5.  The custom functions might have obscured that; each function only returns the TEXT of the field passed to it.

                 

                -- J