Re: User chosen Operator Search on a number field

Jimmy — Wed, 07 Jan 2009 11:54:17 GMT

Looks like there's a potential problem with l_section and l_rank. You are not concatenating a logical operator between them so you can wind up with something like

from [lessons] where [lessons].[l_section] = "lesson category" [lessons].[l_rank] < 4

I'm not sure if that's the problem here, but the first thing you can do to troubleshoot this issue is display the sql command you're using in a textbox on your form and inspect it for problems. Feel free to post it here as well.

Re: User chosen Operator Search on a number field

jh_dempsey — Wed, 07 Jan 2009 11:33:25 GMT

The following is the function that runs when i submit the query. It is based upon an example from an MSDN Tech Note.

function MyQuery::OnClick(eventObj)
{

// Get the query node(s) that you want to modify.
var queryProjects = XDocument.DOM.selectSingleNode(
    "/dfs:myFields/dfs:queryFields/q:projects");
var queryLessons = XDocument.DOM.selectSingleNode(
"/dfs:myFields/dfs:queryFields/q:lessons");
var queryPagination = XDocument.DOM.selectSingleNode("/dfs:myFields");

// Obtain project code
var strProjCode = queryProjects.selectSingleNode("@proj_code").text;

// Obtain project title
var strProjTitle = queryProjects.selectSingleNode("@proj_title").text;

// Obtain project type
var strProjType = queryProjects.selectSingleNode("@proj_type").value;

// Obtain project keywords
var strProjKeywords = queryProjects.selectSingleNode("@proj_keywords").text;

// Obtain project sector
// var strProjManager = queryProjects.selectSingleNode("@proj_manager").text;

// Obtain Lesson Category
var strLessonCat = queryLessons.selectSingleNode("@l_section").value;

// Obtain Lesson Category
var strLessonRank = queryLessons.selectSingleNode("@l_rank").text;
XDocument.UI.Alert("Lesson Rank Term:" + strLessonRank);

// Get the default SQL command for the form.
var strOrigSQLCommand = "shape {select * from [projects] as [projects] ORDER BY [projects].[proj_id] ASC} as [projects] append ({select [l_id],[proj_code],[l_section],[l_actionby],[l_targetdate],[l_problem],[l_solution],[l_rank] from [lessons]} relate [proj_code] TO [proj_code]) as [lessons]";

var mySQL1 = "";
var mySQL2 = "";

// Add Project Code to SQL statement if its not blank
if(strProjCode != "")
{
   mySQL1 = mySQL1 + "[projects].[proj_code] = \"" + strProjCode + "\"";
}

// Add Project Title to mySQL statement if its not blank
if(strProjTitle != "")
{
   if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
   mySQL1 = mySQL1 + "[projects].[proj_title] LIKE \"%" + strProjTitle + "%\"";
}

// Add Project Type to mySQL statement if its not blank
if(strProjType != "")
{
   if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
   mySQL1 = mySQL1 + "[projects].[proj_type] = \"" + strProjType + "\"";
}

   // Add Project Keywords to mySQL statement if its not blank
if(strProjKeywords != "")
{
   if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
   mySQL1 = mySQL1 + "[projects].[proj_keywords] LIKE \"%" + strProjKeywords + "%\"";
}

// Add Project Manager to mySQL statement if its not blank
/*if(strProjManager != "")
{
   if(mySQL1 != ""){ mySQL1 = mySQL1 + " AND "; }
   mySQL1 = mySQL1 + "[projects].[proj_manager] LIKE \"%" + strProjManager + "%\"";
   } */

// Add Lesson Category to mySQL2 statement if its not blank
if(strLessonCat != "")
{
   mySQL2 = mySQL2 + "[lessons].[l_section] = \"" + strLessonCat + "\"";
}

// Add Lesson Category to mySQL2 statement if its not blank
if(strLessonRank != "")
{
   // Set up all the Regular Expressions
   var re1 = new RegExp("^[<][2-5]");
   var re2 = new RegExp("^[<][=][1-5]");
   var re3 = new RegExp("^[>][1-4]");
   var re4 = new RegExp("^[>][=][1-5]");
   var re5 = new RegExp("^[1-4]-[2-5]");
   var re6 = new RegExp("^[1-5]{1}");
   var num = "";

   if (strLessonRank.match(re1))
   {
       num = strLessonRank.substr(1);
       mySQL2 = mySQL2 + "[lessons].[l_rank] < " + num + " ";
   }
   else if (strLessonRank.match(re2))
   {
       num = strLessonRank.substr(2);
       mySQL2 = mySQL2 + "[lessons].[l_rank] <= " + num + " ";
       XDocument.UI.Alert("Recognised as <=");
   }
   else if (strLessonRank.match(re3))
   {
       num = strLessonRank.substr(1);
       mySQL2 = mySQL2 + "[lessons].[l_rank] > " + num + " ";
   }
   else if (strLessonRank.match(re4))
   {
       num = strLessonRank.substr(2);
       mySQL2 = mySQL2 + "[lessons].[l_rank] >= " + num + " ";
   }

   else if (strLessonRank.match(re5))
   {
       num = strLessonRank.split(",");
       mySQL2 = mySQL2 + '[lessons].[l_rank] BETWEEN ' + num[0] + ' AND ' + num[1] + ' ';
   }
   else if (strLessonRank.match(re6))
   {
       mySQL2 = mySQL2 + "[lessons].[l_rank] = " + strLessonRank + " ";
   }
   else
   {
       XDocument.UI.Alert("Not Recognised Lesson importance Search Term");
   }
} // End of if(strLessonRank != "")

// Construct the final query string.
var strSQLQuery = strOrigSQLCommand;
if( mySQL1 != "")
{
   strSQLQuery = strSQLQuery.replace("from [projects] as [projects]", "from [projects] as [projects] WHERE " + mySQL1 );
}
if(mySQL2 != "")
{
   strSQLQuery = strSQLQuery.replace("from [lessons]", "from [lessons] WHERE " + mySQL2 );
}
XDocument.UI.Alert (strSQLQuery);
// This is the query.
// XDocument.UI.Alert(strSQLQuery);
// queryLessons.selectSingleNode("@l_problem").text = strSQLQuery;

// Run the query.
XDocument.QueryAdapter.Command = strSQLQuery;
XDocument.Query();

// Restore all the user entries to the Query fields so that the user entries will
// be available if you want to modify and to rerun the query.
// queryProjects.selectSingleNode("@proj_code").text = strProjCode;
// queryProjects.selectSingleNode("@proj_title").text = strProjTitle;
// queryLessons.selectSingleNode("@l_section").text = strLessonCat;

// Restore the default table command (for the next time).
XDocument.QueryAdapter.Command = strOrigSQLCommand;

// Switch to data entry view to see results.
//XDocument.View.SwitchView("Data Entry");
}

Re: User chosen Operator Search on a number field

Jimmy — Wed, 07 Jan 2009 11:16:58 GMT

I think there's surely a way to do this, but before that, could you show us the code you're trying to use?

User chosen Operator Search on a number field

jh_dempsey — Wed, 07 Jan 2009 10:13:30 GMT

I have a field in a database called 'l_rank' which stores the "importance" of the lesson learned on a scale of 1-5

I want the user to have many different search options such as >=3, <=2, 2-4 etc... to show only lessons that have an importance of greater than or equal to 3, less than or equal to 2, in the range 2-4 etc...

How is best to go about this in InfoPath?

I tried letting the user type in the operators into the query field like the examples given above. Then using JScript i was able to build the correct SQL statement. The problem lies in the fact that InfoPath seems to be doing some automatic checking of the fields, and because the l_rank field is set to the number type, as soon as it sees any sybols such as <>=! then it throws an error. At the moment, when i try to run a query with any of these symbols in the l_rank query field, i get the query field highlighted in red and an error saying

File Cannot be validated by XML schema
Union doesn't support this value
The attribute with 'l_rank' with value '<=5' failed to parse

Anyone got any ideas on how to solve this problem?