The request was simple enough, get the total number of days for a status change from one custom list field to another custom list field in a Sharepoint List Item. There’s a few ways to do this. Assume you have a Sharepoint workflow that changes a list item through various statuses and various fields. A simple way is to create a Date Changed field for each field that you want to track, then to populate it in your workflow as it goes along.
If you don’t have date fields set up in your list, you can take advantage of the version history if version history is turned on. The key here is to loop through your list items, know which field is the field you want, and then take the first version where the value has changed to the desired value. For example, if you are waiting for a “Status” field to say “Approved,” you will take the first version where that field has been set to “Approved.”
Now the tricky part, the field names are buried in an xml field. Find your List ID, then you can find all the fields and their corresponding SQL column names. Insert normal disclaimer here about messing with SQL back end directly for Sharepoint. Stick to select statements, and do them off hours.
Here is the SQL query to find the xml containing field names:
@ListID as uniqueidentifier
SET @ListID =(SELECT tp_id
WHERE tp_title =‘Your List Name Here’
–Stores field names and nvarchar columns in xml
WHERE tp_ID = @ListID
If you take that output, put it into a text document, add <xml> at the beginning, and </xml> at the end, you can browse it in a friendlier xml format.
Notice how each list field has a corresponding SQL field such as “nvarchar20.” This is the SQL field you want to query against.
Now, for the fun part. You’ll want to query the Sharepoint tables based on this new found knowledge. First, you’ll want to get to know your tp_listID, tp_id and other fields in your tables.
The goal here was to get the number of days between a deal was started, and when a contract was finally sent, by using only the version history. As mentioned before, you could architect date fields into your list design up front.
I like to show the finished product first. That seems the best way that I like to learn from blogs so here is the final product:
–Gets all the Started Date, by getting first version where Started is Green, joins back on itself to get other columns
–store resluts in a temp table
select alluserdata.tp_id, nvarchar1 as ‘Customer’, float1 as ‘RDT ID’, alluserdata.tp_version as ‘Version’,
alluserdata.tp_modified into #StartTable
select alluserdata.tp_id, min(tp_version) as Version, tp_listID
Where AllUserData.tp_ListID = @ListID
AND nvarchar39 = ‘Green’
group by tp_id, tp_listid
as Sub inner join AllUserData on AllUserData.tp_id = Sub.tp_id and Alluserdata.tp_version = Sub.Version and alluserdata.tp_Listid = sub.tp_listid
–Gets all the Date Contracts sent, by getting first version where Contract Sent is yes, joins back on itself to get other columns
–Join on temp table to get start date
select alluserdata.tp_id as ‘Dashboard ID’, nvarchar1 as ‘Counterparty’, float1 as ‘RDT ID’,
#StartTable.tp_modified as ‘Date Started’, alluserdata.tp_modified as ‘Date Contract Sent’
select alluserdata.tp_id, min(tp_version) as Version, tp_listid
Where AllUserData.tp_ListID = @ListID
AND nvarchar39 = ‘Green’ and nvarchar31 = ‘Yes’
group by tp_id, tp_Listid
as Sub inner join AllUserData on AllUserData.tp_id = Sub.tp_id and Alluserdata.tp_version = Sub.Version and allUserData.tp_LIstiD = sub.tp_listid
#StartTable on alluserdata.tp_id = #StartTable.tp_id
drop table #StartTable
In my case, I wanted to find the dates when a list item changed it’s status field to “Yes.” This was a custom field that I found to be nvarchar39 using the technique described at the beginning of this post. The field nvarchar31 was a “Started” custom field. I wanted to limit my results to only those that were “Started.”
I have two main result sets that are joined by the INNER JOIN at the end. The first result set is all the list items where my custom field “Started” has the value of “Green.” I pull the item ID, Counterparty, Version, and modified date. This gives me the date when the list item “turned green” by taking the first version where the value was what I wanted it to be. These results are stored in a temp table called “#StartTable.” You can query this table to see the results here:
*FRom #StartTable Orderby tp_id
The second result set is my set of list items and the date the contract was sent. The query pulls the first version of the list item that has the custom field value (nvarchar31) = to “Yes” By getting the tp_modified date, I can see when that version was modified.
Then, I join the 2 result sets, and get my final list of ID’s, and the dates when the contract started, and the date the contract was sent.
The drop command at the start was just so I could keep running this. You’ll want to drop the temp table when you are finished .
If you’ve come this far, you probably can see how this can query any list by examining the version history and the date field values were change for each version.
Good luck and please post any comments.