SQL Database Integration

Re: Auto Fill

Clay Fox — Tue, 08 Jan 2008 17:58:51 GMT

Matt,

Click on my name and send me an email and we can set up a demo.

Active Directory is a database, so the web service queries it directly and loads the fields right into your form for its use. It does not copy it to an additional SQL table. So when the form opens it queries AD and returns the user's username, which you can use to filter specific content or query for all AD information such as email address, manager, etc.. You also can check for group membership.

Then you use the username to do a lookup on your other data and automatically display them. We use it all the time for security and audit tracking. Another great thing is that this works over VPN and RAS and gets the user's network authentication not their local user account.

Re: Auto Fill

mattlaw — Tue, 08 Jan 2008 17:34:31 GMT

Hi Clay,

Thanks for the advice.

Unfortunately I am not a developer, I am a security consultant but I would be interested in a demo.

Just to clarify...this product will pull the relevant information from AD into a SQL database then the information can be used as a data source from there? If this is the case how does it then populate the form with the currently 'logged in' user details?

I do not want the user to have to use a drop down to select an 'ID' which then populates the rest of the form.

If it does what I am after please let me know and we can arrange a demo.

Many Thanks

Matt

Re: Auto Fill

Clay Fox — Tue, 08 Jan 2008 16:31:16 GMT

Hi Matt.

I would recommend you check out both the Database Accelerator package of webservices and the AD Web Service. Qdabra Database Accelerator Suite Version 2.1 - Developer

The Database Accelerator is a great platform for Enterprise InfoPath solutions, especially if you do not have SharePoint. The ADWeb Service in particular, will get you the active directory information you need without needing any code.

If you would like a demo let me know.

Re: Auto Fill

mattlaw — Tue, 08 Jan 2008 15:31:53 GMT

Hi,

I wonder if anyone can help me or if I need to start a new post although it is to do with Auto Fill.

Basically I have a Form creted that requires a level of user information and then digitally signing.

I would like the form to auto-fill from the information stored in AD or using information within the certificate stored on a smartcard. This will need to correspond with the user that is logged into windows when the form is loaded from the web page. Then when the form is saved it automatically signs it with the key pair on the smartcard.

We do not have sharepoint in place so I need the info to come direct from AD using the windows authentication etc.

Thanks

Matt

Re: Auto Fill

Patrick Halstead — Fri, 30 Sep 2005 06:14:04 GMT

Hi Dave,
What trouble are you having? Please do explain.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Re: Auto Fill

davehook — Sun, 25 Sep 2005 11:33:49 GMT

Does anyone have a sample form to show this as I am having trouble following the instructions (I am new to this and the answer suggests some kind of previous understanding)

Many Thanks

Re: Auto Fill

Agni Jonnalagadda — Thu, 15 Sep 2005 10:03:59 GMT

Hi,
Ok Let me tell you from the beginning

1. Take a database connection
2. Map the dropdown to show Location values from DB dataconnection.
There are two approaches from here
a. With Rules.
b. With Code

a. With Rules
1. Double click on the dropdown
2. Click on the Rules button.
3. Click on add Action button.
4. Select the field to which you want to set value
5. Click on "fx" Button to set value.
6. Click on "Inser Field or Group"
7. Select the DBConnection.
8. Select field value that you want to set
9. Click on filter button
10. In the first dropdown select the "Location"(ID)
11. In the second dropdown select "is equal to"
12. In the third dropdown select "Select a field or group"
13. Here select the value from main Dom to which Dropdown is
mapped to.
14. Click ok 8 times.

Similarly you can add any number of actions for one rule.

b. With Code
Add an onAfterChange Event to the dropdown and wite the following in the dropdown.
1. //These are the Namespaces used by the secondary db connection
string sNameSpaceForSecondaryDoms = "xmlns:d='http://schemas.microsoft.com/office/infopath/2003/ado/dataFields' xmlns:ns1='http://autonomysystems.com/webservices/' xmlns:dfs='http://schemas.microsoft.com/office/infopath/2003/dataFormSolution'";
2. //Get the secondary dom to which the dropdown is mapped.
IXMLDOMDocument2 dbDom = (IXMLDOMDocument2)thisXDocument.GetDOM("DBConnection");
dbDom.setProperty("SelectionNamespaces", sNameSpaceForSecondaryDoms);

3. //Get the dropdown value
string dropDownValue = thisXDocument.DOM.selectSingleNode("/my:myFields/my:field1").text;

4. //Get the Node that is selected in the dropdown, Here i have selected the node by filtering with the selected value in the first dropdown
IXMLDOMNode parentNode = dbDom.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Data[@location = '" + dropDownValue + "']");

5. //Set the Values for which you want to set values.
thisXDocument.DOM.selectSingleNode("/my:myFields/my:address").text = parentNode.selectSingleNode("@address").text;
thisXDocument.DOM.selectSingleNode("/my:myFields/my:city").text = parentNode.selectSingleNode("@city").text;
thisXDocument.DOM.selectSingleNode("/my:myFields/my:state").text = parentNode.selectSingleNode("@state").text;

Hope this Helps!

Agni
InfoPath Dev India

Re: Auto Fill

Patrick Halstead — Tue, 13 Sep 2005 08:33:26 GMT

Hi Cajun and MJ Wireless and welcome to our forum,
I think you will have to write code to do this. I will ask one of our engineers to show an example of this.
Thanks

Patrick Halstead [InfoPath MVP]
InfoPathDev

Re: Auto Fill

mjwireless — Mon, 12 Sep 2005 21:00:50 GMT

I am needing the same solution and I still don't quite understand how you show the other fields on the form that are in the same access recordset as the field selected for the drop-down.

Re: Auto Fill

markperry — Thu, 08 Sep 2005 17:07:51 GMT

OK, let's see how well I can explain an answer to what (I think) you're trying to do.

1) Set up your Access DB as a secondary data connection. Do Tools --> Data Connections --> Add and choose to receive data. Pick a SQL / Access DB and then select the relevant database. It will ask you to pick tables / columns etc. Make sure you're happy and finish the wizard.

2) You will have dropped your data / query fields from the Data Source onto your form. Pick the field you want to become your Drop Down List (DDL) and right click it. Select Change To --> Drop-Down List Box

3) Right click the DDL and select Properties. At the bottom, set List Box Entries to look up database. Change your data connection to the one you set up in step 1) and use the Select XPath icon to choose which field in the table you want to use as a look up. The value and display name fields should be set to "."

4) Preview your form and the DDL should populate from the database. I think this is OK for submission - the form should submit the data in the DDL to your submission database (if that's where you're sending it to) OK.

Let me know if this works.

Auto Fill

Cajun — Wed, 31 Aug 2005 18:35:05 GMT

Situation:
1 MS Access Database w/ 1 Table
1 Infopath form
--
I need to use a Drop Down Menu to select an item,
then auto fill in 5 different lines on the form from feilds
in the DB. (IE I need to select the location, then auto fill the address, city, state, zip, contact info for that record onto the form)
The DDM will be using info from the DB.

Any help would be great.