InfoPath Dev

Hi:

I need to run a custom query when a user selects a value from a drop down. I have code that is working (found on this great forum and adapted) but it is attached to a button. What I would like is for the custom query to run when the user changes the value in the drop down. It strikes me that it might be possible using xd:Extension but I am a bit out of my skill level. Here is my code as it stands, being used as an OnClick for a button:

function CTRL138_8::OnClick(eventObj)
{
	
	
// Get the default SQL command for the form.
  var strOrigSQLCommand = XDocument.QueryAdapter.Command;
  
  
  var queryCase = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:Approvers");
  var strAmount = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:Approvers/@Amount").text;
  
  // Ask InfoPath to construct an SQL command that is based on all other field values.
  var strMySQLCommand = XDocument.QueryAdapter.BuildSQLFromXMLNodes(queryCase);
  var strCase = queryCase.selectSingleNode("@Amount").text;
  queryCase.selectSingleNode("@Amount").text = "";
  
  // Construct the full query string.
 var strSQLQuery = "";
 strSQLQuery += "shape ";
 strSQLQuery += "{select ID,Department,Subject,Policy_Ref,Activity from ";
 strSQLQuery += "dbo.SubjectTable as SubjectTable} ";
 strSQLQuery += "as SubjectTable ";
 strSQLQuery += "append ";
 strSQLQuery += "({select ID,Approver,Parameters,Amount,Conditions,SubjectID,FullConditions from ";
 strSQLQuery += "dbo.Approvers as Approvers Where Amount <= " + strAmount + "} ";
 strSQLQuery += "relate ID TO SubjectID) as Approvers ";
 
 
 
  // This is the query.
XDocument.UI.Alert(strSQLQuery);

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

}

Again, I am a bit of a newbie when it comes to code, so this was in part copied from samples posted here and I understand generally what it is doing, but not entirely.

Anyone have any ideas or approaches to share?

Thanks so much,

Hi Hilary,

Well I'm glad to hear that the forum has been a good resource for you!

It sounds like you are trying to execute different actions upon the selection of an item in a DDL, kind of turning the DDL into a list of 'buttons' per se. To do this, I think you will want to use the xdExtension, like you mentioned with a little extra twist.

Let's say that the options in the DDL are: but1, but2, and but3. On the DDL you will want to follow the instructions found here:http://blogs.msdn.com/infopath/archive/2005/06/17/430347.aspx except you will want to add an AND clause before calling each custom function. So, you're conditions will look like this:

Rule 1:
DDL 'is equal to' 'but1' AND
xdExtension:FunctionForBut1()

Rule 2:
DDL 'is equal to' 'but2' AND
xdExtension:FunctionForBut2()

etc.

I hope this helps and I hope I understood your situation correctly!

Hi, Matt:

Thanks for your fast response. My drop down list choices are pulling from a SQL database, which makes them dynamic. Sometimes they would be but1, but2 and other times they might change. Regardless of what is selected from the DDL I want the same function to run, the query that pulls in the selected amount from the DDL and asks the database for anything equal or less than. I initially tried calling my button function from the conditions for the DDL, as explained in the article you referenced. But when I put in the name of the function, I get an error--so if I put in as a condition xdExtension:CTRL138_8::OnClick(eventObj) an error pops up based on the "::OnClick" part of the function. If I remove the "::OnClick" from the function so that I can save the rule, nothing happens when I change the amount in my DDL--it is as if it doesn't fire. Any ideas?

Thanks again for your help.

--Hilary

You will definitely want to take the ::OnClick() off of the function name. I suggest following the tutorial exactly as it is stated and then once you get it working you can build the function out in the code to do all that you want it to do.

I hope this helps, if you have any more trouble feel free to paste the code that you are trying to use so that we might help you debug it.

Hi, Matt:

I have worked through the tutorial and all was successful. I have renamed my function CustomQuery and the code looks like this:

function CustomQuery()
{
	
// Get the default SQL command for the form.
  var strOrigSQLCommand = XDocument.QueryAdapter.Command;
  
  var queryCase = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:queryFields/q:Approvers");

  
  var strAmount = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:Approvers/@Amount").text;
  
  // Ask InfoPath to construct an SQL command that is based on all other field values.
  var strMySQLCommand = XDocument.QueryAdapter.BuildSQLFromXMLNodes(queryCase);

  var strCase = queryCase.selectSingleNode("@Amount").text;
  queryCase.selectSingleNode("@Amount").text = "";
  
  // Construct the full query string.
 var strSQLQuery = "";
 strSQLQuery += "shape ";
 strSQLQuery += "{select ID,Department,Subject,Policy_Ref,Activity from ";
 strSQLQuery += "dbo.SubjectTable as SubjectTable} ";
 strSQLQuery += "as SubjectTable ";
 strSQLQuery += "append ";
 strSQLQuery += "({select ID,Approver,Parameters,Amount,Conditions,SubjectID,FullConditions from ";
 strSQLQuery += "dbo.Approvers as Approvers Where Amount <= " + strAmount + "} ";
 strSQLQuery += "relate ID TO SubjectID) as Approvers ";
 
 
 
  // This is the query.
XDocument.UI.Alert(strSQLQuery);

  // Run the query.
  XDocument.QueryAdapter.Command = strSQLQuery;
  XDocument.Query();
  
 //Restore the default table command (for the next time).
  XDocument.QueryAdapter.Command = strOrigSQLCommand;
  
}

This code is erroring in two separate places. First, on the line

queryCase.selectSingleNode("@Amount").text = "";

I get the error "MSXML5.DLL
Attempt to modify a read-only node.
Error occurred during a call to property or method 'CustomQuery'."
Then on line

XDocument.Query();

I get the error "InfoPath
The query method on the Document object failed.
InfoPath cannot run the specified query.
Unspecified error
Error occurred during a call to property or method 'CustomQuery'." Again, the code works just fine when attached to a button, so I am not sure what I need to change to make it work when called with xd:Extension. Any ideas or suggestions are greatly appreciated.

Thanks,
Hilary

Okay, instead of using xdExtension, since this isn't really something conditional, I am calling the CustomQuery() function to the OnAfterChange event of the DDL. As it stands, though, it doesn't care for the line

queryCase.selectSingleNode("@Amount").text = "";

. Without this line, however, my query won't pull "less than" items, only "equal to".... Any ideas, anyone?

Thanks,
Hilary

This is interesting code, Hilary. I've never seen the BuildSQLFromXMLNodes() function used. Instead of setting this one node to a blank, you could try removing the entire tree, maybe something like this:

var queryFields = XDocument.DOM.selectNodes("/dfs:myFields/dfs:queryFields//*");
queryFields.removeAll();

I haven't tried this, but I think this will just remove all of the query fields and the query will only be what you have constructed in the strSQLQuery variable.

Hey, Matt, thanks, I get a script doesn't support this method on the .removeAll. I guess what is confusing me the very most is why I don't get the "less than" items without setting the value back to blank on the amount node. The query itself looks correct, even with a much simpler version of the code:

function CTRL138_8::OnClick(eventObj)

{
	
// Get the default SQL command for the form.
var strOrigSQLCommand = XDocument.QueryAdapter.Command;

//Get the node info 
var queryAmount = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:Approvers");
var strAmount = XDocument.DOM.selectSingleNode("//dfs:myFields/dfs:queryFields/q:Approvers/@Amount").value;
queryAmount.selectSingleNode("@Amount").value = "";
  
// Construct the full query string.
var strSQLQuery = "";
	strSQLQuery += "shape ";
	strSQLQuery += "{select ID,Department,Subject,Policy_Ref,Activity from ";
	strSQLQuery += "dbo.SubjectTable as SubjectTable} ";
	strSQLQuery += "as SubjectTable ";
	strSQLQuery += "append ";
	strSQLQuery += "({select ID,Approver,Parameters,Amount,Conditions,SubjectID,FullConditions from ";
	strSQLQuery += "dbo.Approvers as Approvers Where Amount <= " + strAmount + "} ";
	strSQLQuery += "relate ID TO SubjectID) as Approvers ";
 
 
// This is the query.
XDocument.UI.Alert(strSQLQuery);

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

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

Again, works perfectly with a button. With it being called as an OnAfterChange event for the DDL, I get the error that the number of calls to the OnAfterChange event for a single update exceeds the maximum limit. If I take out the line that sets the amount node to blank, the I get standard query results, no less than....

I guess what I am really wondering is why code works great with a button but not as a regular function? And also, why does the node need to be set to blank for the custom query to run?

Again, I appreciate all your help.

Hilary

I got it. I added a simple XML file as a new data connection to bind to the Amount drop down, and then called the following function from the OnAfterChange....

function Test2()
{

var myDataAdapter = XDocument.DataAdapters("Main connection");
var strAmount = XDocument.GetDOM("Additional").selectSingleNode("//Amount").text;
var strSQLQuery = "";
	strSQLQuery += "shape ";
	strSQLQuery += "{select ID,Department,Subject,Policy_Ref,Activity from ";
	strSQLQuery += "dbo.SubjectTable as SubjectTable} ";
	strSQLQuery += "as SubjectTable ";
	strSQLQuery += "append ";
	strSQLQuery += "({select ID,Approver,Parameters,Amount,Conditions,SubjectID,FullConditions from ";
	strSQLQuery += "dbo.Approvers as Approvers Where Amount <= " + strAmount + "} ";
	strSQLQuery += "relate ID TO SubjectID) as Approvers ";

myDataAdapter.Command = strSQLQuery;
myDataAdapter.Query();

}

Matt, thank you so much for your help--I really appreciate it. I also have to say again kudos to this fantastic forum--I can do stuff with InfoPath that I would never be able to on my own.

Namaste,
Hilary

Awesome I glad you got it to work! The only suggestion I was going to add to avoid the "too many calls to OnAfterChange" was to add a global boolean variable that is set to true on the first OnAfterChange, and the blocks future calls, details can be found here:
http://www.infopathdev.com/howto/tutorials/default.aspx?i=3551199a23ac41f3a155b3a2ffc412c5

However, it seems like you've gotten it to work, so let us know if there is anything else that comes up!

Thanks for the suggestion and the link--I'll check that out. Thanks again!
Hilary

And, just when I thought I had it all sorted....

The form works great when I preview it. And it works great the first time I open it. But, the second (third, fourth, etc.) attempts to open it fail. Here is the error message:

InfoPath cannot open the selected form because of an error in the form's code.
The following error occurred:

'msoxd__Additional__Amount' is undefined
File:script.js
Line:68

The control it is referring to is the OnAfterChange event of the DDL that I bound to a node of a simple XML file data connection. Any thoughts?

Well, I have gone ahead and recreated the form, starting with the just the bits that I needed to make sure I wouldn't run into the same problem, and starting from scratch seems to be fixing this. Who knows what I did! Fortunately, recreating is pretty easy once you have all the technical details worked out.

Thanks again!