Business Connectivity Services External List validation cannot be performed out of the box with Microsoft SharePoint or Microsoft InfoPath. In this blog post, we will show you how BCS External List validation can take place. Business Connectivity Services External Lists in SharePoint On-Premises and SharePoint Online provide an effective method for SharePoint business users to create, read, update and delete line of business data from external data sources. Such data sources may include Microsoft SQL, Oracle, SAP and many more external data sources. There are many services that form part of Business Connectivity Services including the web parts used to display data within a web part page, the External Data Column to lookup external data from within a SharePoint list or library, and also the search indexing of external data if you use Enterprise Search in SharePoint On-Premises. In this blog post, we will be focusing on External List forms which are used to write data back to an external data source such as a Microsoft SQL database.
Like ‘internal’ SharePoint lists, an External list is made up of views, and also forms which allow users to enter new records, read or update existing records, and also delete records assuming you allow it. Internal list forms such as NewForm.aspx, EditForm.aspx and DispForm.aspx can be modified using Microsoft InfoPath. When navigating to a SharePoint list such as a Task list, you will see the “Customize in InfoPath” button on the Customize List ribbon.
If you navigate to the List Settings page within a SharePoint List, you will also see a link that reads ‘Form Settings’. The Form Settings link will also enable you to launch Microsoft InfoPath to begin modifying the List Forms.
Business Connectivity Services External List validation cannot be performed using InfoPath. As well as the fact that Microsoft Announced the official retiring of Microsoft InfoPath, the option to customize the list forms on the ribbon is not present within External Lists. If you navigate to the List Settings for an External List, and click the link to modify the form link, you will receive an error message as per below:
InfoPath does not support customizing the form used for this list.
So, why would you want to customize the External List forms anyway? Well, typically list forms are customized so that layout and logic can be implemented into the SharePoint list to build a business solution. Often, business processes require you to do more than simply fill out fields. Take for example a Expense Report. You would need to calculate fields, notify the approver, display sublists, and add validation to ensure that expenses are not too high etc. Some of these features can be implemented in the SharePoint List settings, but not all.
If we look specifically at Business Connectivity Services External Lists, it is even more important to be able to modify the forms. Many databases consist of data validation to make sure that the data stored within the database is consistent. We certainly don’t want anybody entering a Phone number in the Company Name field, or worse still, we don’t want anyone entering a phone number in a QTY field when ordering stock! We are therefore going to need to add validation controls within the External List forms which currently is not possible when using SharePoint out-of-the-box and it is not possible using InfoPath either. Additionally, when you enter information that is of the wrong data type, users will receive a very unfriendly error message.
You may think it possible to add some of the validation to the columns within an External List as you could do with a standard SharePoint list. However, navigating to the SharePoint External List Settings will display your columns, but won’t allow you to change them at all.
Subsequently, as an Independent Software Provider (ISV) that has specialized in Business Connectivity Services since it was launched as the Business Data Catalog in MOSS 2007, Lightning Tools provides a mechanism whereby you can design External List forms to add logic, layout and design, as well as calculated fields and data validation. The forms can be modified within your browser providing a WYSIWYG design experience. Below, we have a standard newform.aspx from an External list that only offers validation on required fies.
You will notice that as well as the design being basic, we may want to spend time improving the look of the form as well as adding validation. In the below example, a header has been created as well as spacing in the label names.
Lightning Forms includes the ability to Enable or Disable fields based upon a calculation, set initial values or calculated values, and also add Business Connectivity Services External List validation conditions and validation text. In this example, we set a maximum of 100 in the Units On Order and notify the user that they entered a quantity that was too large prior to a database round tip.
Adding logic such as this simple validation doesn’t require any coding experience or external applications to be installed.
To learn how Business Connectivity Services External List validation can be achieved with Lightning Forms, or to arrange a demonstration, please visit: https://lightningtools.com/products/lightning-forms/
