Populating InfoPath form SQL

Question

Hi everyone, this is my first time posting here so I’m going to apologize in advance for anything.

I have a SQL database that has about 130,000 columns (or records). I tried to use a combo box to select the Account Number and fill in the rest of the text boxes. That works fine when I have 2 or 3 text boxes to populate, going on 5 or more and its way to slow. So I saw something about an External Item Picker. I set that up and got it working, I like the way that you can search and see the data before you select it. That kind of a situation would be perfect for my users, but when the data is selected (Account Number), that is the only thing that I can get to populate on my form. The other fields like Address, City or state don't populate, and I've read that it's because that data has to be Mapped to the Identifier, which doesn't work when I try it. Does anyone have any ideas what I can do?

Thanks,

Matt

Answer 1

Hi Matt,

Yes, this is by design. And if you want to also show related Address, City and state according to current selection in the External Item Picker control, please

1. Create an external list from the external content type.
2. Create a data connection to receiver data from the external list in InfoPath template.
3. Add 3 text boxes in the form the display related Address, City, and state.
4. To go text box properties for each text box control > click the function button (fx) under Default value option > click Insert Field or Group > Select Address field from the external data source > Click Filter > Add > Special Filter condition similar like this:
Account Number from external list data source is equal to EntityDisplayName from Main data source under group > pc:BDCAssociatedEntity > BDCEntity group.
5. Repeat step4 for the other 2 text boxes to display City and state fields.

Best regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 2

Thanks for the help Emir,<o:p></o:p>

When I select the address field there are two options from the Secondary data; queryFields and dataFields does it matter which one I select?<o:p></o:p>

Thanks,<o:p></o:p>

Matt<o:p></o:p>

Answer 3

Hi Matt,

Please select the Address field under dataFields.

Thanks & Regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 4

Hi Emir

Thanks for
helping me out. I'm very close to getting this, but for some reason the address
is not updating when the Account Number is selected. The address is getting
displayed when the form opens. I've played around with the filter options but
so far nothing is working. Could it be that something with my list in
SharePoint?

Once again
thanks you for your time,

Matt

Answer 5

Hi Matt,

1. Do you set the value with filters as the default value for address field?

If so, only when BDC field changed, the address field will change.

2. Or create another test field to get the EntityDisplayName field as its default value and see if you are getting a correct filter.

3. When you select the Address field from the external list data source, please select the field under dataFields group but not queryFields group.

Best regards,
Emir

---

Emir Liu

TechNet Community Support

Answer 6

Hi Emir,

I'm double checking everything, I know that the AccountNum does display in a text box from EnitityDisplayName. I've been playing around with the filters and I've also been looking into the ETC to make sure it's working right and that the List has no issues.

Thanks,

Matt