InfoPath Dev

I have a field in my form that I need to populate with the output from a SQL stored procedure.  The stored procedure has two input parameters; these values will be filled out by the user in two controls that appear on the form.  Is custom code the only way to accomplish this?  I have an InfoPath book for designing forms for form services and it comments that it is best to use rules if you can.  I just want to make sure custom code is the best/only way to go.

Thanks. 

 I was thinking this may be a 'it depends' answer so let me explain exactly what my stored procedure does/what I'm trying to accomplish.

I have an Estimates table that has an estimate number field in it.  The value of this field is dependent on what office the estimate is for and the year of the estimate. For example, if the estimate is for Austin, the estimate number will start with a '0'.  The first estimate of every year will end in '01' and for all subsequent records the estimate number will be the next available estimate number.  To determine the next available number for the estimates I created a SQL view that groups by office and year and selects the max estimate number + 1. 

Then I created a stored procedure that has two input parameters (office & year) and one output parameter (estno).  The stored procedure looks for a record in my view based on the two input parameters. If it finds one then it returns the estimate number from my view. If the estimate number is null then it assigns the output a 'beginning of the year' estimate number based on the office.  So if Austin didn't have a record for the year the estimate number would '001'.

Is this possible to do the functionality of my stored procedure using rules instead?

Thank you for the description of your stored procedure.  It certainly helps.

 Couldn't you use QueryDB to query this SQL view, filtering on office and year to get the value?  Then if your query returns no rows, your form could use the default estimate number (001).

 Jimmy,

How do I do parameters for the branch and year in QueryDB?  The view filter would depend on the office the user selects from the dropdown list and the year they enter into a textbox in the form.

 QueryBuilder should be able to help you build the query, but essentially you would use something like:

 concat("<query database='databasename' table='viewname'><filter><and><eq><column name='office'/><value>", officeNumber, "</value></eq><eq><column name='year'/><value>", year, "</value></eq></and></filter></query>")

Here you would need to replace the bold underlined parts with the actual names, and put references to your fields in the underlined italicised parts.

 Thanks Jimmy.  I got a couple of different errors.

My view fields are Branch_OfficeID and Estimate_Year.  My filter fields in my form are named 'branchoffice' and 'estyear'

First, I tried the following:

 <query database='#Marketing_Resumes#' table='MaxEstimateNumber_Web' ><columns><column name='Branch_OfficeID'/><column name='Estimating_Group'/><column name='Estimate_Year'/><column name='Next_Estimate'/></columns><filter><and><eq><column name='Branch_OfficeID'/><value>", branchoffice, "</value></eq><eq><column name='Estimate_Year'/><value>",estyear, "</value></eq></and></filter></query>

I got an error that it could not convert varchar branchoffice to int.  I checked my branch office dropdown list and changed the data type to integer but this didn't make a difference.

I then tried:

 concat("<query database='#Marketing_Resumes#' table='MaxEstimateNumber_Web' ><columns><column name='Branch_OfficeID'/><column name='Estimating_Group'/><column name='Estimate_Year'/><column name='Next_Estimate'/></columns><filter><and><eq><column name='Branch_OfficeID'/><value>",branchoffice, "</value></eq><eq><column name='Estimate_Year'/><value>",estyear, "</value></eq></and></filter></query>")

When I used this one I got the error: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.ArgumentException: Invalid parameter
Parameter name: queryXml ---> System.Xml.XmlException: Data at the root level is invalid. Line 1, position 1.
 

 Are you perhaps using these queries in the Add Data Connection Wizard?  In the wizard, you should be using the simpler query that QueryBuilder provides for that purpose.

 Hmmm...I was entering this into the value of my data connection.  Is this not where I'm supposed to enter this? 

Hi, Stacie:

To use a QueryDB query that takes advantage of values in your form, set up the data connection with a simple query, like:

<query database='#Marketing_Resumes#' table='MaxEstimateNumber_Web' ><columns><column name='Branch_OfficeID'/><column name='Estimating_Group'/><column name='Estimate_Year'/><column name='Next_Estimate'/></columns></query>

You can deselect the checkbox on the last screen of the data connection wizard that asks whether the query should be run when the form opens.

Then, in a rule (on one of the fields you wish to use as a parameter, perhaps, or a button, whatever makes sense in your form) you can set the queryXml node for your data connection to a formula with a concat, like Jimmy described, that adds your filter with your values. The second action for the rule would be to run the query.

Thanks Hilary.

Okay, so I clicked the Rules button on my estyear field and then clicked Set Action

 I selected Query using a data connection and then selected my data connection

Then I clicked Set Action again and chose Set Field's Value. I pasted in the code that Jimmy helped me with and made sure I didn't have any errors.

When I previewed the form instead of showing the correct value it actually showed the code that Jimmy gave me.  Do I have a quote wrong or something? 

 Also, I need to do an if statement with the value.  If the query returns a null value (meaning that there is no record for that office and year combination) then I have to create the estimate number (the first digit of the estimate number will be based on the branch office they choose and the last 2 digits will be 01).  Is it possible to accomplish this?

Your first action needs to be the one that sets the queryXml field. It should not be setting a field in the main data source, but a field in the secondary data connection you created to QueryDB. Your second action runs the query. At that point, your secondary data source should have correct, filtered data in it. What you are doing with the two actions is first setting a query node in the secondary data connection to the correct xml for the query you want to run. Then you run the query to get your data back.

As far as your if statement, I'd think you could do that in the same set of rules, just add conditions to the rule -- if there are no rows returned by your query, do one thing, if there are rows, do something else...

For more information on using QueryDB, check out these links:

• QueryDB User Manual
• Implement Static and Dynamic queries using QueryDB
  

Okay, now I'm really confused.  So I have attached a snapshot of my Rules actions in my estyear field :

 First Action: "It should not be setting a field in the main data source, but a field in the secondary data connection you created to QueryDB"

So am I supposed to make the Next_Estimate (this is the field in my data connection in which I want to populate my estno field) equal to the concat query?

Like this: Next_Estimate = concat("<query database='#Marketing_Resumes#' table='MaxEstimateNumber_Web' ><columns><column name='Branch_OfficeID'/><column name='Estimating_Group'/><column name='Estimate_Year'/><column name='Next_Estimate'/></columns><filter><and><eq><column name='Branch_OfficeID'/><value>", branchoffice, "</value></eq><eq><column name='Estimate_Year'/><value>", ., "</value></eq></and></filter></query>")    The period between the value tags after Estimate_Year are for estyear. 

Second Action: I selected Query a Data Connection and selected my NextEstimateNumber

Third Action: I set my estno field = Next_Estimate

An estimate number is returned but it is incorrect.  It looks like it is just pulling the Next_Estimate value from the first record in the my database table.

 Not sure what I'm missing here.

You'll need to change the first action to set the queryxml node in the secondary data connection. The execution steps then become:

1. Set the value of queryxml of the the query data connection

2. Execute the query data connection

3. Set the desired field to the result obtained from the query data connection

How do I set the value of queryxml?