How to calculate date based on column "last modified"
Hi! I work in a NorwegianMOSS 2007 enviroment, but I hope I get the terms and synthaxes close to correct in english in this post...I want to make a view of documents changedwithin the last 15 days. I tried to make a calculated column in which I used the =[last modified]+15, and for it to return a date. When testing, as soon as document is read/modified, this calculated date becomes incorrect. Seems like the value for [last modified] gets set to 30.12.1899. So the returned date is always 14.01.1900. When I test the calculation; replace [last modified] with [Created], the calculated date always returns correct...So I wonder: is it not possible to calculate based on the last modified date??y.t.Ann-Merete
November 18th, 2009 12:57pm
Hi,You don't need a calculated column for that. Just create a view where you filter for the value of the last modified field, and compare it do the value of today-15. The view will evaluate the actual values of the items.'Created' shouldn't work either. Calculated columns are only updated if the item is modified, so calculated columns based on 'Created' or 'last modified' will be invalid for tomorrow unless you update the items every day.Hope that helps.Peter
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2009 1:04pm
There is no column last modified, there is [Modified] =[Modified]+15, and return type is DateTime
November 18th, 2009 1:04pm
Thank you, Peter! The filtering in views didn't even cross my mind, I was so hung up on calculation there :-] I am a SP-novise, I know!So the solution is: in a view for showing modified files within the last 15 days, I filter the returns by this condition: Show elements when column [modified] is > [today]-15. (or: [modified]>=[today]-14)Please correct me if this is wrong..?
Free Windows Admin Tool Kit Click here and download it now
November 18th, 2009 2:10pm
Hi,Yes, that should work, but is you prefer doing from code:SPList list = rootWeb.Lists["yourlist"];
StringCollection viewFields = new StringCollection();viewFields.Add("Title");//add other fields as requested
String searchQuery = @"<Where><Gt><FieldRef Name='Modified' IncludeTimeValue='TRUE' /><Value Type='DateTime'><Today OffsetDays='-15' /></Value></Gt></Where>";SPView view = list.Views.Add("Recent items", viewFields, searchQuery, 100, false, false);Peter
November 18th, 2009 3:01pm
Peter,
Have you worked with the SPSiteDataQuery?
I apologize in advance if this is posted in the wrong area, but thought it may be relative.
I'm attempting something similar, in code, but for all documents in the current Site Collection / Site:
SPSiteDataQuery query = new SPSiteDataQuery();
query.Query = "<Where><Gt><FieldRef Name='Modified'/><Value Type='DateTime'><Today OffsetDays=\"-30\"/></Value></Gt></Where>";
query.Lists = "<Lists ServerTemplate='101' />"
string fields = "<FieldRef Name='Title' />";
fields += "<FieldRef Name='Title' />
query.ViewFields = fields;
query.Webs = @"<Webs Scope='Site' />
// Change for the level you want
DataTable table = SPContext.Current.Site.RootWeb.GetSiteData(query);
foreach(DataRow row in table.Rows)
{
Guid listID = new Guid(row["ListId"].ToString());
Guid webID = new Guid(row["WebId"].ToString());
string url = SPContext.Current.Site.Url;
url += SPContext.Current.Site.AllWebs[webID].Lists[listID].DefaultViewUrl;
row["Title"] = "<a href=\"" + url + "\">" + row["Title"].ToString() + "</a>";
row.AcceptChanges()
}
table.AcceptChanges();
gridView.DataSource = table;
gridView.DataBind();
Currently, the part builds and displays, but without data. If this can be made to work, I'll add the fields to customize the part for displaying site, doc library, etc.
Free Windows Admin Tool Kit Click here and download it now
April 20th, 2010 12:14am
Hi Steve,
I suggest you to ask your question in a new thread, because:
- it is not strongly related to the original question.
- the original question is answered, so it is likely that other users than you and me not reading it anymore, you have less chance to get the answer.
- make it enable to give credits to the answerer of your question.
In the meantime I try to figure out the answer.
Thanks!
Peter
April 20th, 2010 3:27pm