InfoPath 2010 – Designing External List Forms
Posted by Clayton Cobb on October 28, 2009
This blog entry is a direct follow-up to Business Connectivity Services (BCS) – Creating an External List from an ECT. Through this blog and the rest in the series, we will be interacting with the External List that I created based on the External Content Type from the first blog: Business Connectivity Services (BCS) – Creating an External Content Type (ECT) based on a SQL 2008 Table.
I enjoyed learning about the new BCS features and how to quickly create both an ECT and External List. However, the area I’m MOST excited about is InfoPath 2010 and all of its many improvements. One major – and I mean MAJOR – change is the ability to now modify SharePoint 2010 (SP2010) list forms with InfoPath. Being that I love to use InfoPath forms for conditional formatting purposes so that my users only see what they’re supposed to see when they see it, this is a massive improvement for me. I have not been able to do that with my lists, so it has always been a struggle. To do anything remotely close to this, you had to use SharePoint Designer (SPD) 2007 to modify your ASPX pages and their web parts. This was very cumbersome, ineffective, and even had issues like the well-known Attachments Bug (fixed in SP2, I believe, but annoying for years). Using SPD 2007, you also didn’t have the full fidelity of a forms builder like you do with InfoPath. Well, that has all changed now, and although I wish I had have had time to do a regular list form for you, we are going to focus on modifying the list forms of an External List. I was very curious as to whether or not this would be doable, because I had proven the concept with normal lists in Tech Beta build, but I couldn’t get ECTs to work. So, when I got one day to work on a newer build, I focused 100% on ECTs and anything I could associate with it. I do have a customized list form in my personal SP2010 build from which I will get screenshots and do a separate blog later. For now, we’re taking the InfoPath list form concept and combining it with the awesome External List feature.
Here are the tools used in this blog entry:
- SharePoint Server 2010
- InfoPath 2010 Beta
- SharePoint Designer 2010 Beta
- SQL Server 2008
- BCS – External Content Type
- BCS – External List
Here are the steps of this blog entry:
- View existing data in SQL Server 2008
- View existing SQL data in an SP2010 External List
- Choose to customize list forms with InfoPath from within SPD 2010
- Customize list form in InfoPath 2010
- Create new list item
- View resulting data in the SP2010 External List
- View resulting data in SQL Server 2008
View Existing Data in SQL Server 2008
First off, we just take a quick jaunt over to SQL Server Management Studio (SSMS) to take a look at the Biz table in our ECT database, because that is the table feeding our External List. As you can see, it has 10 records (Fig 1).
Fig 1 – Existing data in SQL 2008
View existing SQL data in an SP2010 External List
Next, we mosey on over to SP2010 and browse to our External List that looks just like a normal SharePoint list except that we see the exact same data here as we did in our SQL table.
Fig 2 – Existing SQL data in an SP2010 External List
Choose to customize list forms with InfoPath from within SPD 2010
Unfortunately, in my haste to get screenshots before the machine got shutdown, I forgot to take a screenshot of this button in SPD 2010. This part is actually very important, because the behavior is different than a regular list form. With a regular list, you just browse to the list in SharePoint, click on the List tab in the ribbon (like you see in Fig 2), and you click the InfoPath icon in order to immediately customize your list forms. However, in an External List, it doesn’t work like that. In my early SP2010 build, the icon was there, but when it opened InfoPath, I couldn’t do anything. What I learned at the conference is that you have to go open the External List in SPD 2010, go to the Design tab in the ribbon, and choose “Customize List Forms in InfoPath” or something of that nature. This is the screenshot I need to get for you and add to this blog entry later. For now, though, if you get a chance to try it, you will be able to follow those instructions easily.
Once you click that button, it immediately invokes InfoPath 2010 and shows you the whole data structure for the list along with a pre-defined Main Submit data connection so that you don’t have to configure that. Your customization options within an External List form are much more limited than in a regular list form. I don’t yet have a list of what those are, but I will catalog them and add them here:
- External list forms do not support additional data connections – I learned this one right away, because it’s one of the first things I tried. In my regular list form, I was able to do this but not on the External List form. I verified with the InfoPath product team that this is a definite limitation that will not go away. Bummer, because I love to add the UserProfileService as a data connection to 99% of my forms for user validation. My earlier blog posts explain one very good reason for this: InfoPath – User Roles in Browser-Enabled Forms Using AD Groups. To be more clear, the bolded text in this bullet is the exact error I got when trying to add a data connection.
- Cannot change the schema – This is not an exact error, but with regular list forms, you can add a field in design time, and it will add that as a column to the list. With an External List, the schema is set and can’t be changed, so you can’t add nodes to the schema in InfoPath. I will get more verification and improved wording on this.
- More to come…
Customize list form in InfoPath 2010
So, again, I don’t have any snaps of my form during design time, but you will see it at run time below. In this scenario, I didn’t do anything fancy at all, because I just wanted to prove the concept. However, fancy or not, one thing I could not figure out was how to change all three forms – new, edit, and display. With regular list forms, all three can be selected and changed separately. With an External List, there is just one form that gets used for each instance. No, you can’t edit the display form, but it’s the same design. The main issue I had was that the Edit form was the same as the New form, and I love to change those up as well as my Display form. All I could find in SPD 2010 were threw new PAGES that got created after I chose to customize my list forms with InfoPath. The three new pages were named newifs.aspx, editifs.aspx, and dispifs.aspx if my memory is correct. When I went to edit those directly in SPD 2010, I didn’t ever get prompted with InfoPath. What I didn’t realize was that these new pages were using the brand new InfoPath Form Web Part, which is another awesome addition. Each of those pages had a single IP Form Web Part, and in the settings of that web part, you can choose which view to display as well as which action to perform upon submit. I didn’t look closely enough to notice this, but the InfoPath team explained it to me.
So, here is how you would go about setting up three separate “forms” for an External List:
- It’s just one form
- Create 2 new views (3 total) and design them according to their purpose (new, edit, display)
- Go to the editifs.aspx and dispifs.aspx pages in SPD 2010, edit them, modify the form web part, and set their view to the corresponding view of that page (editifs.aspx = Edit view).
- Also, a trick for the display view is to set your display view as the PRINT VIEW in the properties of the default view. This will apparently cause that particular view to always show up when a display page is opened
That’s pretty simple to me.
Create new list item
After publishing your form, browse to the External List again and simply click New Item (Fig 3).
Fig 3 – Click New Item in External List
As I mentioned before, this particular form has no bells and whistles, but I did make a few changes (Fig 4). I changed the Partner field, which is not a Boolean data type, to a checkbox. In the properties of that control, I set the checked and cleared values to match the exact syntax of what is in SQL so that there would be no conflicts. I also disabled the toolbars that are built-in and added a custom Submit button instead. I like to do this, because it allows me to put conditional formatting on my buttons to only show certain buttons to certain people at certain times and to do multiple sets of rules on each one. With the built-in submit button, you can’t do that.
As you can see, I added some info for a new business, and then I clicked Submit.
Fig 4 – InfoPath-based External List form
View resulting data in the SP2010 External List
After submitting, the form returned me to the External List where I immediately could see the new business along with all of its associated data (Fig 5).
Fig 5 – Resulting data in External List
View resulting data in SQL Server 2008
A quick job back over to SSMS (Fig 6) reveals that the data was in fact written directly to SQL Server 2008 without a hitch…
Fig 6 – Resulting data in SQL 2008
Some points to consider:
- This is just a simple, flat table structure, not a complex data structure with depth and relationships. If this is not quite complex enough for what you need to do, then please consider the DBXL tool created by Qdabra
- I have no middle layer between SharePoint and SQL, which is not a best practice. It would be best to use a web service
- I used the permissions of my administrator account to create the ECT and was using that same account when interacting with the form. You have the ability to connection permissions at the point you create the ECT and in the list itself
- Not being able to add additional data connections limits the options you have for doing advanced customization and logic
- Despite all of this, it is still awesome!
Next in line for this series:
- InfoPath 2010 – Connecting a Form Library template to an External List
Clayton's SharePoint Madness 
Sebastian Renzi said
Hi Clayton,
Really a great post, I want to ask you if had the chance to do this with a table that has a Primary Key (uniqueidentifier), and want to add a record?. I can not make this working. My PK is setted as Identifier inside the External List, and then you cannot add a value for that field, so my insert operation fails.
thanks in advance
Clayton Cobb said
It should work, but I will try it and let you know.
Hemant said
Was there any resolution to this issue? I am running across the same issue.
Thanks
Hdatta
Clayton Cobb said
Hemant, what is your exact issue? In my environment, the uniqueidentifier is auto-generated when I create the record. I even create new records in the External List with workflows, and it works as long as I have the right parameters, but I don’t try to set the PK – it sets itself in the database.
Links « Knowledgebase said
[…] https://claytoncobb.wordpress.com/2009/10/28/infopath-2010-designing-external-list-forms/ […]
Guruprasad marathe said
Hi.
I am consuming SAP services and i made CRUD method to my entity.
For that entity 4 more entities are related. I can submit the form and can we it. but the problem is. i want a drop down box instead of external picker,.(now while creating new item, i am getting 4 external pickers) just want to make them drop down and 2nd drop down depend on the first drop down.(how can i achieve filtering??)
one more.
I want to prepopulate few information such as empname, id,email(all are readonly).while creating new item.
how can i do i thru infopath? suggestions please.
Clayton Cobb said
With ECTs, you can only provide an external picker. It can’t be changed to a dropdown list. To have dropdown lists populated by external data, you can’t use External List forms – you have to build a regular custom list form or form library form. To pre-populate fields with user profile data, you also can’t use External List forms – you have to build a custom list form or form library form.
Basically, to do all this, you’ll need to leave the external list alone, create a separate ECT and external list for each table, then create a custom list where you customize the list form with InfoPath and create data connections to each external list and associate all the data inside that form. When you submit that form, fire off a SharePoint Designer 2010 workflow that goes back and updates the external lists, which writes the data to SAP. This is what I’m doing on a 2010 project I have now.
Azam Abdul Rahim said
Hi Clayton,
You said: “then create a custom list where you customize the list form with InfoPath and create data connections to each external list and associate all the data inside that form”.
Does the custom list need to replicate the external list (with exactly the same columns)?
Clayton Cobb said
Azam, it depends on what you’re trying to do. In my Leave Request demo, I only really care about the Leave Days column, because that is the only value I update in the external database after a Leave Request is approved. I do also reference the username for each person in the database for foreign key purposes.
Guruprasad marathe said
Hi Clay
thank you very much,I have created ECT type for each tables. after deploying thru VS2010. i can see those ECT in central adimin under Business data connectivity models.
DO i need to create a list for each ECT in my sharepoit site?
while customizing fields in info path , there i can see manage data connection. while adding it asks for SOAP,REST,Libray,Database,XMl,
if i create list for each ECT i can use those list in my custom forms,so that i can choose sharepoint library in that i can give my site url,
How can i do it without creating list so that those list will be available for me globally? can we make those BDC entity models as services? or is there any other approach? I’m not using Databases here.. consuming SAP services.
Guruprasad marathe said
I have created list for each ECT in my site. I have added all ECT list to my custom form(thru manage data connections), when i want to bind the Name text box to Userinfo(Secondary)to its Datafield(Ename field in the userinfo connection) it says Binding a non repeating control to repeating control not possible. I tried to add rule
rule1) when page load, when the Name is blank action query for data.there i can only select the data connection not able to select field. how can i pre populate that field?
give some suggestions
Clayton Cobb said
I can’t follow what you’re saying – it’s too jumbled. Please explain clearly in detailed steps what you want to do.
Guruprasad marathe said
okey
scenario is applying business card by the employees
1) I have created a CRUD method for Business Card entity
2) and few other entities such Location business type and desgination i have created an association between the card and those entities just to pull those data as a external content type.
3)i used VS2010 (BCS) after deploying this whole BCS project.i got
ECT in central admin>manage services>Business Data Connectivity Service Application
I have given the permissions for all ECT( i think its entity models)
——-
opened my site in designer and in external content type i find all the entities which i have created.
1) I have created a external Content type by using that Business Card entity.
2) Now i can apply ,submit and save n view my business cards.
while creating new item. i find external pickers.
as i told u i dont want that. you have suggested me to create a custom list form
No I m creating it.
3) I have created a custom list opened it in info pathdesigner2010
4)at the bottom I have option to manage data connections.
5) there i can add connection it gives me 5 options
SOAP REST sharepoint library list xml, database.
ISSUE;
if i want to add those entities. i need to create list for all those external content type so that i can select the Sharepoint library and list.
from there i need to give my site url. so that it wil populate all the list ( if n only if i created lists for all ECT)
How can i add those entitied wich i have created earlier without creating list.
is that the only way to add those entities(ECT models)?
Now i have created the custom form. How can i make that form to connect with my Business List (ie my ECT)?
Clayton Cobb said
Yes, you have to create external lists for each ECT, because that’s how InfoPath will interact with the ECTs – same for the SharePoint Designer workflows when you need to write back to the DB.
Guruprasad marathe said
thank you Clay.
work flow is giving error.
“The workflow could not create the item in the external data source. Make sure the user has permissions to access the external data source and create items.”
i m the admin and i have given permission to myself in central admin too..
Clayton Cobb said
Yes, to update the external list with a workflow, you must be using a Secure Store ID for the External Content Type, and the app pool account for your content web application must be included as a member of that SSID. Also, the app pool account must have permissions to the ECT entity in the BDC service application.
Guruprasad marathe said
HI Clay,
Can you please send some screen shots as how to add the APP pool account to SSID considering my connection is for a .Net Assembly.
Regards
Guru
Guruprasad marathe said
Hi Clay,
one help
Hi,
I have created the external list for my remote server with “connect with user identity ” in share point designer 2010.
what I have done?
step1: in share point designer 2010. created new external type.
2)Given name Employee and > add connection> selected Sql Server type>
given server Database server database name and choosed “connect with user identity”.
i got the tables n its contents. created all operations for hat ECT.
3) created the list from that external list
4) while displaying the list is giving me error “Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’.”
note: i have full access to sql server 2008. I am using OS: windows server 2008 r2
share point server enterprise 2010(stand alone) share point designer 2010 (64 bit) office 2010 (64 bit)
central admin> manager services> business connectivity service> employee ECT. there also i have given permission to myself.
help please I am new to share point.
regards
Guru
Clayton Cobb said
Those are the right steps as long as you gave your user account access to the ECT in the BDC service application, and that user has direct access to the individual database in the SQL server.
guru said
Yes i have given permission to my Employee ECT which present under central admin> manage services> BDC service
to whole service I have given the permission to my self.
I app pool for that site the identity is network also.
M. Tag said
[SPS2010 Ent + Infopath 2010)
Clayton,
I have an external list which I want users to be able to add to items to using repeating sections/fields in Infopath 2010. I can’t seem to find any info on how or if this is possible. Could you advise?
User clicks “new order” – then can add multiple entries into one form.
M. Tag
Clayton Cobb said
It’s not an available function. There isn’t a way to parse repeating values out of an InfoPath repeating table and put them into SharePoint as individual line items. Each form is one line item. To parse them out requires custom code, such as what Qdabra has put in their qRules.
M. Tag said
Hi Clayton, thanks for that. Confirmed what I thought.
I’ve got the trial of qRules – how would I go about doing this?
Clayton Cobb said
Use the “Submit to SharePoint List” qRule – I think that’s the name. The folks on InfoPathDev will also help you if you ask questions in the qRules forum. Qdabra owns/runs that forum, and the company is full of InfoPath experts, including 3 MVPs.
Jane said
I have a number of views in one form template. Is there a way of formatting all views at the same time. That is can I do something so I can apply one theme to all views?
Clayton Cobb said
Jane, there is not a way to do that, unfortunately.
Mahak said
Hi, I have an issue. I have created an external list that also inserts new items into SQL server database. In my sql table there are two columns of “datetime” data type that take value in the following format: 2010-08-14 00:00:00.000 when an entry is made directly or data import is done from excel to SQL server table. but when I use SharePoint external list to insert data to SQL server, it makes a wrong entry for the date. eg. If i select 12-Aug-2010 from date time picker, it submits date as following in SQL table: 2010-08-11 18:30:00.000. It always save sdate of one day before the date selected in date picker of infopath form. How can I save the exact date selecetd from external list infopath form to SQL server. ANy help is much appreciated.
date selecetd in sharepoint external list new item form: 12- Aug- 2010
Saved in SQL table as: 2010-08-11 18:30:00.000
David Smith said
Do you know of any resources that explain how I can create an ECT in visual studio that uses entities from multiple dB tables (with some one to many relationships) and then modify InfoPath forms associated with the External list to use Repeater sections for those one to many relationships?
Clayton Cobb said
David, I don’t do anything with code and don’t use Visual Studio, so I can’t help there.
The only type of InfoPath form that can use repeating sections is the form library template type, so you won’t be able to do anything like that with an External List (or any list for that matter). I also don’t think BCS can connect to multiple tables. You’ll have to create a view and create an ECT for that view. However, you can’t submit back to a view natively with BCS, so you’ll have to create triggers in SQL that can accept the submit/update request and then parse out the different pieces of data to each table.
Sheila Ochner said
We were excited by the ability of SharePoint 2010 to create globally available workflows. However, we use a number of List Lookups. With those in place we get a “Cannot publish globla workflow. Before a workflow can be globally reused, you must remove any lookups to lists in the current site.” error when we try to publish.
How would you propose getting around this limitation in the best way?
Clayton Cobb said
Perhaps make the workflow reusable instead of globally reusable and then deploy it to each site where you plan to use it. As long as the lookup column is a site column on the top-level site, then all subsites in that site collection should be able to use it.
Derek said
Hey Clayton,
I was hoping you may beable to help me on this issue.
I have a form that is about 10 views in complexity, on one of the views I query a SP List in a pull down in order to insert 6 fields of relevant data in a repeating table. Prior to today the list was to be updated or added to, by someone in another department. Now it is going to be hadled by the person using this form. The data that will be entered is an attachment to this form when submitted. I would like the form user to be able to add data to the list on the same view that the query takes place. In order to eliminate the user from having to jump back and forth between the 2 forms.
I tried to follow the example here
http://blogs.msdn.com/b/infopath/archive/2007/03/26/submitting-to-a-sharepoint-list.aspx
And here
http://www.infopathdev.com/blogs/matt/archive/2006/02/02/Add-SharePoint-List-Items-with-InfoPath.aspx
But I am not able to get the data to save back to the list.
Using Sp 2010, IP 2010 browser form.
I know I can pull in the data and query fields from the data connection to the list, I just cant figure out how to right back to it.
Thanks
Derek
Clayton Cobb said
Derek, InfoPath can query list data easily, but it cannot submit to lists natively unless the form itself is bound to the list where you’re submitting data. If you are trying to submit data to a separate list than where the form is published, then it requires code, CAML, or workflows. http://www.bizsupportonline.net/blog/2008/12/5-ways-to-submit-an-infopath-form-to-a-sharepoint-list/
Derek said
I am trying to use the CAML method, UpdateListItem from the list web service, but I am not seeing the data getting written back to the list.
Derek said
Clayton,
I got this working, however I want to use it in a form that has validation rules in other views. Is there a way to send the data to the list (upon submitting to the webservice) that will ignore validaton in the rest of the form. Since I am trying to add items t a list in view “x” not submit the form to it’s library!
Thanks
Derek
Clayton Cobb said
Derek, no, because you’re going outside of what InfoPath was built to know, so it is unfamiliar with the custom submit-to-list action you’re doing. It is going to validate the whole form when you try to submit. You would have to make your validation conditional on everything so that it doesn’t apply when submitting the list items.
Derek said
Ok.
I think I will be able to make it work now tht I looked further. All the initial validation is for user “A” befor the first submit and they will not be using the web service submit, user “B” will not have those validation rules since they will be opening the form after first submital.
Thanks
Derek
Derek said
Clayton,
I am revisiting the Caml and Web service method for adding Items to an unbound Sp List. What I am trying to do is populate the “Field” associated with the list to where the data is going to be sent, with data in fields from within the form. Since the method of using the Caml and Web Service only allows a repeating table on the form, I tried to inject data to thosed fields filtered by the “Name” field
Repeating table(Batch)
—————————–
“Name” | “Field”
—————————–
Requestor | (want to set this value to my:FormRequestor)
—————————–
Description |
—————————–
I want to set the value “Field” = my:FormRequestor
(filter[Where “Name”=Requestor])does not work
(filter[Where “Field”=Requestor])does not work
I tried a few other functions like eval, contains, etc.
I am unable to figure this out without code.
SP2010, IP2010 browser form.
Any ideas?
Derek
Clayton Cobb said
Derek, you posted this in the Designing External List forms comment area. Are you talking about trying to populate external lists DIRECTLY from a separate InfoPath form? Is there a reason you aren’t able to use a workflow?
Derek said
I posted here befor on this issue, didn’t know where else to post actually, sorry!
Can a workflow do a dynamic update While the form is open and in use? Or do I just throw a button on the form to “Save Changes” and have the workflow trigger on update?
I could try the workflow method, but does’t it makes more sense to have it run from Web service? I really do not know the answer. and as long as it works, smoothly, any method is fine.
Clayton Cobb said
The form would have to be submitted – that can be with a button or a field change (any change event in the form), which then updates the item in the current list/library and kicks off the workflow to then update one or many items/fields in other lists/libraries.
InfoPath isn’t intended to update other lists through the lists.asmx web service. In code, sure, this is what you do, but what you’re trying to do is far outside how InfoPath was built to be used. If it works, then great, but it’s not a design pattern for InfoPath use…at least in this particular case, not with all web services.
Bhavin Sankhat said
I have created External list of customer table(CustomerID is primary key)using SPD 2010.And then I have create customize External List Forms Using Microsoft InfoPath 2010. I face following issue.
1. Can we have code behind in customize External List Forms?
2. When I publish the Infopath form to the server and try to insert data into CustomerID field which is PK in database list not allow me to insert value into it.
Can you please advice on the same!! Thanks
Clayton Cobb said
1. No, you cannot
2. Databases automatically insert their own unique IDs. Are you sure your database is set up to allow you to manually type in the primary key?
Bhavin Sankhat said
Thanks you Clayton for your quick reply.
Kelvin said
Nice works.
Is there a work around for the issue “External list forms do not support additional data connections” ?
Can this be overcome by customise forms in VS2010 ?
If yes, how could it be done ?
Clayton Cobb said
Kelvin, there is no workaround that I am aware of. The InfoPath team told me that it can’t be done, but that doesn’t mean there isn’t some method of doing it via custom code – I just haven’t seen anyone do it or claim to do it.
Osama said
Hello Clayton,
I understand that external content type list won’t work with one to many relationship tables but my challenge now is how can you add an external data connection to an InfoPath form based on external content type list,
The external data connection it’s getting the data from our ERP server
Please help.
Regards,
Osama
Clayton Cobb said
Osama, you do it by creating receive data connections to the External Lists – they behave like normal lists, although you have to watch out for ECT item limitations.
Osama said
Thanks Clayton for getting back to me,
I am still not able achieve my goal.
The InfoPath form I have is based on external content type list if I add a receive data connection and try to publish the form I get the error message “External list forms do not support additional data connection”
The reason why I want to use ECT InfoPath form is to allow me to submit data to SQL without creating a web service or creating a very complex workflow plus make the form a web based in sharepoint.
We have alot of “Infopath 2007 filler form” type which they take along time to load plus we have to install infopath client on each PC.
Regards,
Osama
Clayton Cobb said
Ok, you didn’t say you were converting the external list to InfoPath. You just asked how to create data connections to external lists from InfoPath.
External lists converted to InfoPath cannot have secondary data connections just like the error says.
Kedrick said
Hi Clayton,
Whenever I design an External List form in InfoPath 2010 that contains an External Item Picker (SharePoint automatically includes an External Item Picker when there is a foreign key based association), I get the following error whenever I select an item using the External Item Picker:
“There has been an error while processing the form.”
Have you come across this error before? It’s driving me nuts cuz the form works fine if there’s no External Item Picker or if I don’t change the form in InfoPath 2010. Even if I just load the form into InfoPath without making any changes and the publish it back to SharePoint… I get this error.
I appreciate your help!