Querying incosistent SharePoint lists using SPSiteDataQuery

Site data query is powerful querying mechanism in SharePoint. We can use it to query data from multiple lists in web or over webs hierarchy. But it works well only until lists or content types are consistent by structure and there are now bad hacks or tricks made by power users. But how to survive the worst case – lists with messed up fields? I have quick temporary solution to provide.

Site data query is represented as SPSiteDataQuery class in code. If you are not familiar with this class and you want to find out more then take a look at SPSiteDataQuery class documentation.

Inconsistent lists

There was case where customer had similar lists – one for every sales department. They named fields in different lists with same name and ended up with the result they needed: all lists have fields with same display names. But… unfortunately with different internal names.

Display name Internal name
List 1 List 2
Deadline Location Deadline
Budget Budget Category

Using SPSiteDataQuery I was able to get most of data they needed but some fields were empty and some fields had incorrect values.

Hard to change

Although I first suggested to fix these lists and make them consistent, it was not possible option for multiple reasons:

  1. There are custom web parts that probably stop working after changes.
  2. There are built-in web parts that depend on current structure of lists.
  3. Some of web parts are built and deployed by external parties.
  4. We need our new report to be ready for next week as we have auditors coming.

So, on one hand customer is okay to make fixes but on the other hand it’s more work and planning but they need some working solution right now.

Calculated fields as solution

I came out with simple and maybe not so nice solution – calculated fields. I can add calculated fields with same names to all lists I have to query and they always return me correct values whatever is the internal name of actual field.

For Deadline field in list where Location was renamed to Deadline I added calculated field called QueryDeadline with formula shown on image below.

Calculated field formula

If field Deadline if renamed from Location in one list and it is added as a new field to another lists then both lists have field QueryDeadline that has the same internal name and gives me back always correct result.

Handling values of calculated fields

The next problem I faced was the format of calculated field values in data table:

string;#In progress

I wrote simple loop that cuts off type definition from field value. These nested loops there go through the data table I got from site querying and it removes type definition from fields given in lookupColumns array. I’m using array because in my case there was about twelve columns that needed this little fix.


var lookupColumns = new string[] { “QueryDeadline” };

 

foreach(DataRow row in dataTable)

    foreach (var colName in lookupColumns)

    {

        if (row[colName] == null || row[colName].ToString() == “”)

            continue;

 

        var value = row[colName].ToString();

        if (value.IndexOf(“#”) > -1)

            value = value.Split(“#”.ToCharArray(), 2)[1];

 

        row[colName] = value;

    }


You can use the same code also to cut off ID from lookup field values in data table.

Problem solved?

No, the problem is not solved. Correct solution is to make lists consistent so they use same structure. Solution provided here is temporary one that helps customers to have new functionalities available for users but for sure they have to later change it. No, I don’t like hacks like these but sometimes you just don’t have any better options.


Leave a Reply

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