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:
Declare
@ListID as uniqueidentifier
SET @ListID =(SELECT tp_id
FROM dbo.AllLists
WHERE tp_title =‘Your List Name Here’
)
–Stores field names and nvarchar columns in xml
SELECT tp_fields
FROM AllLists
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
from
(
select alluserdata.tp_id, min(tp_version) as Version, tp_listID
from alluserdata
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’
from
(
select alluserdata.tp_id, min(tp_version) as Version, tp_listid
from alluserdata
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
INNER JOIN
#StartTable on alluserdata.tp_id = #StartTable.tp_id
drop table #StartTable
—————————————————————————————————-
Explanation-
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:
Select
*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.
i am having an issue where i use this query to get the persons name who approved the document but the user tells me she was not the one who approved it.
can my query be off?
SELECT AD.tp_Modified AS Modified,AD.tp_Created AS Created,AD.tp_DirName AS DirectoryPath,AD.tp_LeafName AS DocumentName,AD.nvarchar3 AS FileType,AD.nvarchar1 AS ApprovedBy,AD.nvarchar2 AS AddedBy
FROM AllUserData AD with (NOLOCK)
WHERE (tp_ContentType = ‘Document’) AND (tp_LeafName NOT LIKE ‘%.css’) AND (tp_LeafName NOT LIKE ‘%.jpg’) AND (tp_LeafName NOT LIKE ‘%.png’) AND (tp_LeafName NOT LIKE ‘%.wmf’) AND (tp_LeafName NOT LIKE ‘%.gif’) AND (tp_DirName NOT LIKE ‘%Template%’) AND (tp_IsCurrentVersion = 1) AND (tp_LeafName NOT LIKE ‘%.xsl’)
AND AD.tp_DeleteTransactionID = 0x
ORDER BY AD.tp_Created DESC
Dave,
using this query to find the list fields,
SELECT tp_fields
FROM AllLists
WHERE tp_ID = [list id]
I see that nvarchar1 corresponds to the “Modified By” field. This may or may not be the person who approved it. You would need to check the value of the Approved field to see which version the value changed to ‘Approved,’ then see the person who modified it on that version. Also, there may be another column somewhere that stores the Approval and Approved by values, but I haven’t looked into it. Hope that helps.
Miles
Hi Miles
Do you need to be local to the SP server to run these SQL queries.
I am not local nor can I remote into the Sharepoint server, is there a way I can still query the version history?
thks.
rd
RD,
For these queries you need access to the SQL Server database. This is the ‘back door’ approach. There are other ways to do it such as 1) writing code against the SP object model and installing on the SP server, and 2) using the SP webservices to iterate through the items via webservice. This would be time consuming in my opinion, but you gotta do what you gotta do.
Miles
Thanks Miles,
Writing code against the SP object model requires local access as well I presume, so since I don’t have it, i’m limited to writing a script through SP Webservice. Do you know where I may find sample scripts to pull version history this way? I also presume these would be entered in a webpart or query Web part.
rd
RD, using a webservice could work, but you would need permissions to access the list data. I don’t have an example handy for the webservice calls, but i think it would be cumbersome to get all the versions and loop through them.
Even Querying SharePoint DB’s is un-supported, as they could cause read-locks. I would prefer object model code.
Refer the below post for Generating Version Report along with their Sizes.
http://salaudeen.blogspot.com/2012/02/versioning-size-report-for-entire.html
Miles, is there a way to do something similar in Access? I’m trying to get a version history from SP to track modified date based on status change field. Thank you.
George, I’m not sure how you would use Access. The SQL query is to go against the SQL databases that SharePoint uses natively. You need to have read access to those databases to get the information.
For your application, one idea is to write an event handler to populate your own database with the status change information.
Thanks