InfoPath Dev

Hello! I am new at posting to this form but have used the site for most of my questions in the past...I love this site!!! I hope I do not sound too confusing but I have an InfoPath form where the requester enters their username and based on that I want it to populate their supervisor (so the form can automatically be submitted to their supervisor on submit. I have a database connection set up to query an SAP worker lookup table which we use to match employees with supervisors. The problem is the SQL code in the Data Connection is hard coded to a specific person's username and I need to change the statement to refer to the InfoPath field that is filled in on the form. It produces the results when using a specific username in the SQL code but I cant figure out how to make it reference the form field not "johndoe"

Here is the SQL data connection code:
select "worker_no","company_username" as "SupervisorUsername" from "dbo"."SAP_worker" as "worker" where worker.worker_no = (SELECT supvr_worker_no FROM reference.dbo.SAP_worker WHERE company_username = 'johndoe')

The field name on the InfoPath form is "Employee_Username"

Any help or suggestions would greatly be appreciated! Thanks in advance!!

Hi BoKnows and thank you for loving this site! :) We're all about InfoPath love here at InfoPathDev! :)

Unfortunately, out-of-box SQL connections won't support passing parameters. Yes, I know, it's a major gap. That's why we developed our WSSP Web Service and Query Builder tool. Check out free stuff here: http://www.qdabra.com/en/products/docs/WSSP23.aspx. Download is available through the DBXL product page: http://www.qdabra.com/en/products/DBXL.aspx.

Good luck!

 

Thank you Patrick

My knowledge is very limited in this area so I hope this makes sense. Do you think it would be possible using Microsoft Visual Studio?

 Unfortunately the DBXL is only a trial version and I will not be able to purchase the full version do to cost reduction measures that are in place at our company.

 Thank you again for your support!

Yeah, you could code it in Visual Studio. You'll have to create your own Web Service and send a parameter to it that you run your SQL query on. It'll take a few hours, but shouldn't be too bad. There are examples out there of simple Web Services that you can use.

Another idea: install DBXL trial and get it working. Provide a nice testimonial (1-2 page write up) to us, and we'll give you a free BASIC copy. Offer expires on 9/1. :)