justinc

ExecuteSQL context different in calc field definition?

Discussion created by justinc on Aug 2, 2012
Latest reply on Aug 9, 2012 by 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

Outcomes