SharePoint 2010 Gotcha: Associated ECT Column Does Not Show Up in Filter Choices

I have a relational table in SQL Server that I access in SharePoint 2010 as an External Content Type. The table, disks, has an FK (serverID) that is associated with a PK in another table, servers, via an Association Operation. Once I create a list and add it to a webpage, I am unable to select the serverID column or its associated lookup column serverName.

After two days of troubleshooting and research, I decided to just stuff the code into the web part and surprisingly enough, it worked! Not only that, once I forced the query in the code, the column magically appeared in the Field Names drop down in the Filter Criteria. Wanna show SharePoint who’s boss? Replace <Query/> in your web part’s code with something like this:

<Query>
     <Where>
          <Eq>
               <FieldRef Name="ServerID"/>
               <Value Type="Integer">{ServerID}</Value>
          </Eq>
     </Where>
</Query>

The above is what I used to filter the results using ServerID in the querystring and it worked just as expected.

Chrissy is a Cloud and Datacenter Management MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SharePoint, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *

*