Restrict values coming from Sqlserver

[BowieM]

Hi,

I have a stored procedure that accepts parameters to restrict the selected
values returned by the procedure. For example, the stored procedures accepts
a supplier code to select and return only products associated with that code.

Now I'm aware of filtering and conditional formatting but this relies on the
stored procedure returning all suppliers and their products and doing the
restriction within InfoPath.

My question rely is: Is there a way in InfoPath of passing a parameter to a
data connection that receives data from an external source such as a web
service in such a that the parameter is passed on to the stored procedure as
described above?

Thanks.

 

[Paresh]

Hi,

I have a stored procedure that accepts parameters to restrict the selected
values returned by the procedure. For example, the stored procedures accepts
a supplier code to select and return only products associated with that code.

Now I'm aware of filtering and conditional formatting but this relies on the
stored procedure returning all suppliers and their products and doing the
restriction within InfoPath.

My question rely is: Is there a way in InfoPath of passing a parameter to a
data connection that receives data from an external source such as a web
service in such a  that the parameter is passed on to the stored procedure as
described above?

Thanks.

You can write a web service method that accepts parameter(s), add this
method as a dataconnection in infopath, set the queryfield parameter
of this secondary dataconnection by some suitable value or field
value, query this secondary dataconnection.

HTH,
Paresh

 

[Paresh]

You can write a web service method that accepts parameter(s), add this
method as a dataconnection in infopath, set the queryfield parameter
of this secondary dataconnection by some suitable value or field
value, query this secondary dataconnection.

HTH,
Paresh- Hide quoted text -

- Show quoted text -

I would like to add one more point to the earlier reply of mine.

In teh web service method, after collecting the parameter from the
infoPath form, you can write suitable ado.net code to set the database
stored procedure parameter and execute the database SP.

Do let me know, in case you need more clarification.

Thanks,
Paresh

 

[BowieM]

Hi Paresh,

Thanks for leading me thus far. I guess I did not try harder earlier on.
Hope I'm not doing the same again but I don't know how.

In the parameter details where I need to set the sample value, how can I
enter an Xpath expression that refers to an InfoPath field?

Bottomline is that the sample value is not to be hardcoded.

 

[Paresh]

Hi Paresh,

Thanks for leading me thus far.  I guess I did not try harder earlier on..
Hope I'm not doing the same again but I don't know how.

In the parameter details where I need to set the sample value, how can I
enter an Xpath expression that refers to an InfoPath field?

Bottomline is that the sample value is not to be hardcoded.

--
Regards,
Bowie M







- Show quoted text -

For instance, consider that you have 2 controls on your form, 1)
Textbox field 2) Button
Assuming youalready have written a web service method and added this
method as secondary data connection to you infopath form, you can
assign the textbox field value to the secondary dataconnection's query
field and query the secondary dataconnection on the button click, as
follows:

Double Click the button, then click Rules, then "Add"
Click Add Actions, select "Set a field's value" in Actions Dropdown
Click the button adjacent to Field textbox. This will give you the datacource window. Select your secondary datasource from the dropdown list
Select your parameter expanding the queryfields node
Click the formula (fx) button besides the value textbox, click "Insert a field or group" and select the textbox field of the main datacource
click OK for 5-6 times and you have set the desired rule

Now everytime you click the button, the text in textbox field will be
passed to the web service method and you can play with the database
using this value to fetch the desired records.

These are steps for InfoPath 2007. I haven't tried with Infopath
2003.

Do let me know if this helps.

thanks,
Paresh

 

[BowieM]

Hi Paresh,

I am also working with InfoPath 2007.

Thanks again for your assistance.

The way you describe it works perfectly.

However, I have a "small" problem. What I have is a textfield and a
list-box. I have applied the example you gave for a button to my list-box but
of course the small problem is that clicking the list-box does not call the
web service. Is they any other way other than "forcing" the user to click a
additional button?

Thanks.

 

[Paresh]

Hi Paresh,

I am also working with InfoPath 2007.

Thanks again for your assistance.

The way you describe it works perfectly.

However, I have a "small" problem. What I have is a textfield and a
list-box. I have applied the example you gave for a button to my list-box but
of course the small problem is that clicking the list-box does not call the
web service. Is they any other way other than "forcing" the user to click a
additional button?

Thanks.
--
Regards,
Bowie M










- Show quoted text -

I didnt try using ListBox, but you can try setting all the rules on
the listbox which you have set for button. It should work.
Let me know if you still face any problem with this,

Thanks,
Paresh

 

[Clay Fox]

Just to add. The Database Accelerator, downloadable from
http://www.qdabra.com , contains an enumeration web service which will allow
you to query any table you want and even allows complex select statements.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

 

[Phil]

Following up on this question - is it possible to submit different parameter
values to the same web method/data connection from different controls, so
that different data is filtered in each drop-down?

The scenario we have is of 2 drop-down controls on a form and we would
prefer to use the same data connection and web method for both, just using
different input parameters, to save having to create a seperate data
connection for each control. Currently one drop-down is bound to a data
connection with a default value - which works fine - but we want to change
the default for the second drop-down (or use a hidden field etc.) without
resorting to code. Potentially dozens of controls would use this data
connection, with different input parameters, so this would save us some
development effort.

TIA
--
Phil
http://www.clarity-integration.com
http://www.phil-austin.blogspot.com

Hi Paresh,

Thanks for leading me thus far. I guess I did not try harder earlier on.
Hope I'm not doing the same again but I don't know how.

In the parameter details where I need to set the sample value, how can I
enter an Xpath expression that refers to an InfoPath field?

Bottomline is that the sample value is not to be hardcoded.

--
Regards,
Bowie M







- Show quoted text -

For instance, consider that you have 2 controls on your form, 1)
Textbox field 2) Button
Assuming youalready have written a web service method and added this
method as secondary data connection to you infopath form, you can
assign the textbox field value to the secondary dataconnection's query
field and query the secondary dataconnection on the button click, as
follows:

Double Click the button, then click Rules, then "Add"
Click Add Actions, select "Set a field's value" in Actions Dropdown
Click the button adjacent to Field textbox. This will give you the
datacource window. Select your secondary datasource from the dropdown
list
Select your parameter expanding the queryfields node
Click the formula (fx) button besides the value textbox, click "Insert a
field or group" and select the textbox field of the main datacource
click OK for 5-6 times and you have set the desired rule

Now everytime you click the button, the text in textbox field will be
passed to the web service method and you can play with the database
using this value to fetch the desired records.

These are steps for InfoPath 2007. I haven't tried with Infopath
2003.

Do let me know if this helps.

thanks,
Paresh

 

[Clay Fox]

Sure Phil.

All you would need is to change the query parameter and requery before each
control that needs a different subset of data.

Check out the Database Accelerator at http://www.qdabra.com and you can
install several webservices which you can do this with.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

 

[Phil]

Thanks for the reply Clay.

I'm not sure how we would requery after the form has loaded without
resorting to event programming (ie using the OnChange event of the first
drop-down to requery for the second) this would also mean the second control
wouldn't be populated when the form loads which is a little inelegant. Is
there a way using the InfoPath design GUI? Not sure licensing your software
is going to be the answer tbh

 

[Clay Fox]

It can be done using rules and actions on each control or coding but you can
use webservices completly codeless.

If you want them sequential then you would load the data for the first
control and put a rule on the first control which queried the next one and so
on. You would need to evaluate whether creating unique ones or reusing ones
would be the best fit. You definetly can wait to execute the queries until
you need them using rule action to trigger them.

If you would like a demo let me know.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com

 

[Phil]

Thanks for that Clay - I think I've got it. I've got the web method
requerying after the value in the previous control is changed using the
Rules->Actions dialogs. However this changes the data for all of the
drop-down controls not just the one we're interested in changing - obviously
because the controls are all bound to the same data connection. Is there a
way to stop this behaviour in the designer?

Thanks again.

Phil.

 

[Clay Fox]

That is definitly the catch. They are all using the same data and if it
changes then it is changed for all who use it at that particular time.

You either need to create unique connections and data sets or would need to
make copies fo the data to preserve it for later use. I think you would need
to evaluate where you can reuse and where you can't or how to handle if a
user wants to start over.
--
Thanks

Clay Fox

Qdabra Software
http://www.qdabra.com

InfoPathDev.Com
The Largest InfoPath Forum in the World
http://www.infopathdev.com