InfoPath Autofill from Sharepoint List

[Jeff Dynes]

I have a form with a 2nd data source, sharepoint 2007 list. The list
contains company names with their corresponding contact information. I have
a drop down list in a repeating table where I can select the company. I want
to autofill the contact name automatically based upon the company selection.
I can only get the first contact name to display no matter which comapny I
select.

Any ideas how to correct this issue?

 

[Ben Walters]

Hey Jeffery
There is no way using a direct connection from InfoPath to SharePoint to
filter the data that's being returned. I would suggest creating a web
service interface that takes the selected company name and returns the
contact name

If you need more details on how to do this let me know

Cheers,
Ben

 

[Jeff Dynes]

Hi Ben,

Thanks for the reply. I am new to the InfoPath environment as well as the
whole web development thing!

If you have a suggestion I would really appreciate the help and feedback.

 

[Ed Thurber]

Jeff:

You will need to use a rule applied to the dropdown list. the rule(s) will
set the contact elements to the appropriate value where the @CustomerName
column in your list is equal to the value selected in the dropdown.

I will try to instruct:
Go to the properties of your dropdown, click the Rules button in the middle
of the first page.
Click Add
Name the rule "Set contact Info"
Condition will be left blank.
Click Add Action
Select Set a field's value
In the Field dropdown, select the field that will contain the first contact
element (I'll call it FirstName)
In the Value field, click the formula button.
in the formula dialog box, select insert field or group.
in the field dialog box, select your secondary data source from the dropdown
list at the top.
select the FirstName field
click the Filter Data...; button.
create a filter rule where the Customer field from your secondary data
source 'is equal to' the customer element from your main data source (the
element that is bound to the dropdown).
click OK 'bout a million times.
repeat the "add action" for each Contact elemeent that you want to populate.

Also, remove any default values from the elements (it sounds like that is
what you were using.

hope this helps

Ed

 

[Ed Thurber]

Jeff:

Just a little forward thinking. I have done something similar in the past.
I started with a single list, but it was then requested that i support
multiple contacts per customer.

I created 2 lists.
the first is the customer list. it contains only the name and address
fields for the customer.
the second is a contacts list. the first colun in this list is the customer
name and is a lookup column from the customers list.
the remaining fields are the contact info (name, email, phone...) and a
boolean called 'default'

Now when the user selects the customer, a single rule fires that sets the
contact name in the form to the the contact name from the list that is the
default for that customer. the contact name field is also bound to a
dropdown control that is populated from the contacts list with the names of
the contacts for that customer (filtered). the rules that i mentioned
previously are applied to the contact dropdown instead of the custome
dropdown.

caveat: this method is easy when using the full InfoPath Client. Need to
use code when using form services. have done this also but quite a few
hoops.

hope this helps

Ed

 

[Jeff Dynes]

Ed,

Thanks for the reply....

Here is what I have done so far and it is not working.....

My bound field for the drop down list is CONTACT_NAME_COMBOBOX and my form
field for the contact is called CONTACT_NAME_TEXTBOX.
My secondary data source corresponding fields are Supplier and Contact.
I created a rule and named it SET_CONTACT_INFO.
I clicked on 'Set a field's value'
The I clicked the f(x) icon next to the value option box and clicked 'insert
field or group'.
The field I selected is the secondary data source field named Supplier
I then clicked the Filter' button and the 'Add' button.
I set the secondary data source field ('Supplier' is equal to
'CONTACT_NAME_COMBOBOX') and my formula is @Supplier[._=current()].
This returns the same name that is in the CONTACT_NAME_COMBOBOX.
I can not figure out how to get the secondary data sorce field 'Contact' to
return to the "CONTACT_NAME_TEXTBOX' FIELD.

 

[Frederik Van Lierde]

Hi

I will try to explain how to do it, my article with screenshots isn't
ready yet

So You have a Repeating Section
In the repeating section you have a DropDownList with a link to the
Second DataSource, Value = the ID of the company, Display Name = Name
of the company
In the repeating section you also have a textbox for the contactName

The idea is that the Value for the Contact is the following

SELECT ContactName FROM [Second Data Source] WHERE [Copany ID of
Second DataSource] = [The Selected Company ID]

[The Selected Company ID] = The Company ID in the Main DataSource, as
every data in the form is saved in the Xml behind;


Now the link
- Open Properties Dialog Box of the Dropdown Box
- Click on the Rules Button
- Click on the Add button in the Rule Dialog Box
- Click on Add Action
- Select Set a field's value in the Drop Down Box
- In the Field, select the CompanyContact field in the Main DataSource
- In the Value, open the Insert Formula dialog box by clicking the Fx
button
- Click Insert Field or Group (a dialog box opens)
- Select the second datasource in the datasource dropdown
- Select the ContactName from the second datasource
- Click on Filter Data
- Click on Add
- Select the ID in the first dropdown
- Select 'Select a field or group' in the third dropdown
- Select Main DataSource in the DataSource dropdown
- Select the Company ID (field = the field used for databinding the
company dropdown in your repeating section)
- Press 8 times OK


I hope this brings you closer to your solution

Frederik Van Lierde
http://www.SilverSandsAssociates.com
http://BlueInfoPath.blogspot.com

 

[Frederik Van Lierde]

Hi Jeff

I replied yesterday, but for some reason my message isn't in the
forum. So i'll send it again

Yes there is an out-of-the-box way of doing this. (Only a lot of
clicking to do)

To understand it right:

You have a repeating table
In that repeating table you have a dropdown list (cmbCompanies) with
data from a data connection , let say dcCompanies
Next to the dropdownlist you have a textbox , lets say txtContactName

What you want to do is the following

SELECT dcCompanies.ContactName FROM dcCompanies
WHERE dcCompanies.ID = dcMain.cmbCompanies

All selected fields or entered data by the user are stored in the Main
Data Source, therefore we need to link the second datasource with the
values in the Main DataSource

Step by Step.

- Open the properties dialogbox of the cmbCompanies
- Click on the Rules button (opens the rules dialog box)
- Click on the Add button (opens the Rule dialog box)
- Click on the Add Action (opens the qction dialog box)
- Select Set a fields valuein the action dropdown list
- Select txtContactName in the field textbox
- Click on the Fx button next to the value textbox (opens the Insert
Formula dialog box)
- Click on the Insert Field or group button (opens the select a field
or Group dialog box)
- Select the second datasource (dcCompanies) in the datasource
dropdown list
- Select the field who contains the ContactName
- Click on the Filter Data button (opens the filter data dialog box)
- click on the Add button
- Select in the first dropdown list the field, containing the ID of
the company
- Select in the third dropdown list 'select or field or
group...' (opens another dialog box)
- Select Main Data Source from the Data Source dropdown
- Select the cmbCompanies field ( is the field yhere the coépany
dropdown list is binded to)
- Press 8 times the ok button

Test your form

I hope this will help you solving your problem

Frederik Van Lierde
http://www.SilverSandsAssociates.com
http://www.BlueInfoPath.blogspot.com

PS if this answer was usefull, please rate this post.

 

[Frederik Van Lierde]

This is possible to do 100% out of the box

Your situation:
- A repeating table with a dropdown (Binding Field CompanyID), a
textbox (Binding Field ContactName)
- A second data connection with the list of Company Information (ID,
Name, ContactName,.....)
- The dropdown is linked with this Second Data Connection

You want to do the following

SELECT ContactName FROM SecondDataConnection
WHERE SecondDataConnection.ID = MainDataSource.CompanyID

Step by Step
- Open the Properties Dialog Box from the dropdown
- Click on the Rules button
- Click on the Add button
- Click on the Add Action button
- Select 'Set a fields value' in the Action dropdown list
- Select the ContactName from the Main DataSource + click OK
- Click the Fx button, next to the value textbox
- Click the 'Insert field or group' button
- Select the SecondDataSource in the Data Source dropdown list
- Select the field containing the contact name
- Click the Filter Data button
- Click the Add button
- In the first dropdown list, Select the field containing the
CompanyID in the second data source
- In the third dropdown list, Select the 'Select a field or group'
item
- Select the Main Data Source in the data source dropdown list
- Select the cmbCompanies field (containing the value the suer
selected)
- Press 8 times the OK button

I hope this post helps solving your issues

Frederik Van Lierde
http://www.SilverSandsAssociates.com
http://BlueInfoPath.blogspot.com

PS If this post was usefull, please don't forget to rate this post!

 

[Ed Thurber]

Jeff:

If I am understanding your description below it should look like this.

-apply the rule to the combobox (not indicated below, but seems like you are
doing this)
-the element that you want to set in the 'Set a fields value' is the
CONTACT_NAME_TEXTBOX (not indicated below, but seems like you are dong this)
-your equasion should be @Contact[.=current()] not @supplier[.=current]

you are creating a SELECT/WHERE clause in the Select Field dialog. what
you want to do is select the field you want to return and then apply the
filter to that field. in your case you want to SELECT @Contact WHERE
@Supplier = CONTACT_NAME_COMBOBOX

hope this helps.

Ed

Ed,

Thanks for the reply....

Here is what I have done so far and it is not working.....

My bound field for the drop down list is CONTACT_NAME_COMBOBOX and my form
field for the contact is called CONTACT_NAME_TEXTBOX.
My secondary data source corresponding fields are Supplier and Contact.
I created a rule and named it SET_CONTACT_INFO.
I clicked on 'Set a field's value'
The I clicked the f(x) icon next to the value option box and clicked
'insert
field or group'.
The field I selected is the secondary data source field named Supplier
I then clicked the Filter' button and the 'Add' button.
I set the secondary data source field ('Supplier' is equal to
'CONTACT_NAME_COMBOBOX') and my formula is @Supplier[._=current()].
This returns the same name that is in the CONTACT_NAME_COMBOBOX.
I can not figure out how to get the secondary data sorce field 'Contact'
to
return to the "CONTACT_NAME_TEXTBOX' FIELD.
--
Jeffrey J. Dynes

Jeff:

You will need to use a rule applied to the dropdown list. the rule(s)
will
set the contact elements to the appropriate value where the @CustomerName
column in your list is equal to the value selected in the dropdown.

I will try to instruct:
Go to the properties of your dropdown, click the Rules button in the
middle
of the first page.
Click Add
Name the rule "Set contact Info"
Condition will be left blank.
Click Add Action
Select Set a field's value
In the Field dropdown, select the field that will contain the first
contact
element (I'll call it FirstName)
In the Value field, click the formula button.
in the formula dialog box, select insert field or group.
in the field dialog box, select your secondary data source from the
dropdown
list at the top.
select the FirstName field
click the Filter Data...; button.
create a filter rule where the Customer field from your secondary data
source 'is equal to' the customer element from your main data source (the
element that is bound to the dropdown).
click OK 'bout a million times.
repeat the "add action" for each Contact elemeent that you want to
populate.

Also, remove any default values from the elements (it sounds like that is
what you were using.

hope this helps

Ed

 

[Jeff Dynes]

Ed,

Thanks so much for your help. This post helped a great deal. I finally
figured out what I was doing wrong as I followed along with your description.
I was about to give up on this when I tried one more time and something
clicked in your explanation!

Thank you very much. My project is now back on track!

 

[Ed Thurber]

very cool, glad i could help. I know what infopath epiphanies are like, had
a few myself.

Ed

 

[PC-Nut]

Ed, Thank you so very much for your detailed instructions. I was struggling
with this same issue as Jeff for almost 24 hours. It is working for me as
well. Best regards, Aiman

 

[Deepan S]

Thanks a lot!!

I have a form with a 2nd data source, sharepoint 2007 list. The list
contains company names with their corresponding contact information. I have
a drop down list in a repeating table where I can select the company. I want
to autofill the contact name automatically based upon the company selection.
I can only get the first contact name to display no matter which comapny I
select.

Any ideas how to correct this issue?
--
Jeffrey J. Dynes

On Sunday, October 26, 2008 4:19 AM Ben Walters wrote:
Hey Jeffery
There is no way using a direct connection from InfoPath to SharePoint to
filter the data that's being returned. I would suggest creating a web
service interface that takes the selected company name and returns the
contact name

If you need more details on how to do this let me know

Cheers,
Ben
news:[email protected]...

On Monday, October 27, 2008 1:24 PM JeffDyne wrote:
Ed,

Thanks for the reply....

Here is what I have done so far and it is not working.....

My bound field for the drop down list is CONTACT_NAME_COMBOBOX and my form
field for the contact is called CONTACT_NAME_TEXTBOX.
My secondary data source corresponding fields are Supplier and Contact.
I created a rule and named it SET_CONTACT_INFO.
I clicked on 'Set a field's value'
The I clicked the f(x) icon next to the value option box and clicked 'insert
field or group'.
The field I selected is the secondary data source field named Supplier
I then clicked the Filter' button and the 'Add' button.
I set the secondary data source field ('Supplier' is equal to
'CONTACT_NAME_COMBOBOX') and my formula is @Supplier[._=current()].
This returns the same name that is in the CONTACT_NAME_COMBOBOX.
I can not figure out how to get the secondary data sorce field 'Contact' to
return to the "CONTACT_NAME_TEXTBOX' FIELD.
--
Jeffrey J. Dynes


"Ed Thurber" wrote:

If I am understanding your description below it should look like this.

-apply the rule to the combobox (not indicated below, but seems like you are
doing this)
-the element that you want to set in the 'Set a fields value' is the
CONTACT_NAME_TEXTBOX (not indicated below, but seems like you are dong this)
-your equasion should be @Contact[.=current()] not @supplier[.=current]

you are creating a SELECT/WHERE clause in the Select Field dialog. what
you want to do is select the field you want to return and then apply the
filter to that field. in your case you want to SELECT @Contact WHERE
@Supplier = CONTACT_NAME_COMBOBOX

hope this helps.

Ed

news:[email protected]...

On Friday, October 31, 2008 5:14 AM Frederik Van Lierde wrote:
Hi

I will try to explain how to do it, my article with screenshots isn't
ready yet

So You have a Repeating Section
In the repeating section you have a DropDownList with a link to the
Second DataSource, Value = the ID of the company, Display Name = Name
of the company
In the repeating section you also have a textbox for the contactName

The idea is that the Value for the Contact is the following

SELECT ContactName FROM [Second Data Source] WHERE [Copany ID of
Second DataSource] = [The Selected Company ID]

[The Selected Company ID] = The Company ID in the Main DataSource, as
every data in the form is saved in the Xml behind;


Now the link
- Open Properties Dialog Box of the Dropdown Box
- Click on the Rules Button
- Click on the Add button in the Rule Dialog Box
- Click on Add Action
- Select Set a field's value in the Drop Down Box
- In the Field, select the CompanyContact field in the Main DataSource
- In the Value, open the Insert Formula dialog box by clicking the Fx
button
- Click Insert Field or Group (a dialog box opens)
- Select the second datasource in the datasource dropdown
- Select the ContactName from the second datasource
- Click on Filter Data
- Click on Add
- Select the ID in the first dropdown
- Select 'Select a field or group' in the third dropdown
- Select Main DataSource in the DataSource dropdown
- Select the Company ID (field = the field used for databinding the
company dropdown in your repeating section)
- Press 8 times OK


I hope this brings you closer to your solution

Frederik Van Lierde
http://www.SilverSandsAssociates.com
http://BlueInfoPath.blogspot.com