Qdabra Database Accelerator

Re: Custom code best option for stored procedure?

imstac — Fri, 02 Apr 2010 18:16:49 GMT

Thanks Jimmy, that worked!

Re: Custom code best option for stored procedure?

Jimmy — Fri, 02 Apr 2010 17:34:14 GMT

If the rule logic isn't too complicated, it may be simplest to just put the exact same set of rules on both fields.

Re: Custom code best option for stored procedure?

imstac — Fri, 02 Apr 2010 17:23:31 GMT

Rock On! Got it working. Thanks so much Jimmy, Hillary and Ernesto!

I just have one other question. The value of my field 'estno' depends on two filters w/n the querydb, 'year' and 'branchoffice'. I have put all of my actions for assigning my 'estno' value in my 'year' rules. If I change the year field my 'estno' value will update; however if I change just the 'branchoffice' value and leave the year value as is my 'estno' value does not update. How/Where do I put my rule actions to where if either the branch office or year field changes the value of my 'estno' field will requery?

Re: Custom code best option for stored procedure?

ErnestoM — Fri, 02 Apr 2010 15:12:07 GMT

You'll need to modify the first action in the rule (from the screenshot you included above). Right now you have:

Next_Estimate = concat("<query...

But you need

queryxml = concat("<query...

Where queryxml is a node in the secondary data source you created to query the database (NextEstimateNumber).

Re: Custom code best option for stored procedure?

imstac — Fri, 02 Apr 2010 15:02:04 GMT

How do I set the value of queryxml?

Re: Custom code best option for stored procedure?

ErnestoM — Fri, 02 Apr 2010 14:50:32 GMT

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

Re: Custom code best option for stored procedure?

imstac — Fri, 02 Apr 2010 14:20:02 GMT

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.

Re: Custom code best option for stored procedure?

ErnestoM — Fri, 02 Apr 2010 13:12:05 GMT

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

Re: Custom code best option for stored procedure?

Hilary Stoupa — Fri, 02 Apr 2010 00:57:35 GMT

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...

Re: Custom code best option for stored procedure?

imstac — Thu, 01 Apr 2010 22:58:51 GMT

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?

Re: Custom code best option for stored procedure?

Hilary Stoupa — Thu, 01 Apr 2010 22:30:01 GMT

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.

Re: Custom code best option for stored procedure?

imstac — Thu, 01 Apr 2010 19:42:25 GMT

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

Re: Custom code best option for stored procedure?

Jimmy — Thu, 01 Apr 2010 19:34:10 GMT

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.

Re: Custom code best option for stored procedure?

imstac — Thu, 01 Apr 2010 19:25:58 GMT

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.

Re: Custom code best option for stored procedure?

Jimmy — Thu, 01 Apr 2010 18:59:26 GMT

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.