I have a client file with some 84 fields. The client would like to have some means to report on the 84 fields which do contain data. I think ExecuteSQL is the best way to go about the problem, however I am an lite on implemention the ExecuteSQL function.
To begin with, I have to use this in a script and loop through the script to evaluate each of the fields. The return number results go into a repeating field named EmptyValues. So, what I have is this.
Set Var to $_field_list ; FieldNames (Get (FileName) ; Get (LayoutName) )
Set Var $_rep to 1 (for EmptyValues field)
Set Var $_counter to 1
Set Var $_field_list_rep to 1 (number for the GetValue function)
Set Field EmptyValues[$rep] ; ExecuteSQL ( Select Count ( GetValue ( $_field_list ; $_field_list_rep ) FROM Facility WHERE ( GetValue ( $_field_list ; $_field_list_rep ) NOT IS NULL" ; "" ; "" )
Set Var $_rep to $_rep +1
Set Var $_conter to $_counter +1
Set Var $_field_list_ rep to $_field_list_ rep + 1
the results I am getting back is ?. I am going wrong somewhere in the syntax. I am not sure if it's the variables, the WHERE clause or what.
Appreciate any insight from any SQL Pros out there.