SharePoint 2007. The max/min value of filed in SharePoint list
Today I'll show you how you can get the minimum or maximum value of the field in SharePoint list.
Solution
The solution lies in the fact that we simply sort the elements in this field from the previous to the late receipt at the minimum value (or vice versa to obtain the maximum). Also, we will limit the number of elements of one.
For convenience, I made a method for the extender SPListItem, which takes as a parameter to the internal name of the field. Here I will give methods for obtaining the maximum value. Getting the minimum value will differ only in the direction of sorting.
/// <summary>
/// Maximum value of the field in the list
/// </summary>
/// <typeparam Name="T">type values in the field </typeparam>
/// <param Name="list">List</param>
/// <param Name="fieldInternalName">Internal name of the field </param>
public static T GetMaxValue <T> (this SPList list, string fieldInternalName)
{
var query = New SPQuery
{
// Take the only field that we are interested in
ViewFields = string . Format ( "<FieldRef Name='{0}' />" , fieldInternalName),
// Build a query
Query = string.Format(@"<OrderBy><FieldRef Name='{0}' Ascending='False'/></OrderBy>",
fieldInternalName),
// Get only the first row
RowLimit = 1
};
// Execute a query
var items = list.GetItems (query). Cast <SPListItem> ();
// If the collection is empty (for example, the list contains no elements)
// Then return the default value for type
if (items.Count () == 0) return default (T);
// Return the value of the field
return items.First () [fieldInternalName] is T
? (T) (items.First () [fieldInternalName])
: default(T);
}
To increase the functionality, you can add one more parameter - wherePredicate and CAML-transfer request for pre-filtering the data. You will be able to filter out null values before the search for the minimum value, for example:
/// <summary>
/// Maximum value of the field in the list
/// </summary>
/// <typeparam Name="T">type values in the field </typeparam>
/// <param Name="list">List</param>
/// <param Name="fieldInternalName">Internal name of the field </param>
/// <param Name="wherePredicat">Predicate for the pre-filter </param>
public static T GetMaxValue <T> (this SPList list, string fieldInternalName,
string wherePredicat)
{
var query = New SPQuery
{
// Take the only field that we are interested in
ViewFields = string . Format ( "<FieldRef Name='{0}' />" , fieldInternalName),
// Build a query
Query = string.Format(
@"{0}<OrderBy><FieldRef Name='{1}' Ascending='False' /></OrderBy>",
wherePredicat, fieldInternalName),
fieldInternalName),
// Get only the first row
RowLimit = 1
};
// Execute a query
var items = list.GetItems (query). Cast <SPListItem> ();
// If the collection is empty (for example, the list contains no elements)
// Then return the default value for type
if (items.Count () == 0) return default (T);
// Return the value of the field
return items.First () [fieldInternalName] is T
? (T) (items.First () [fieldInternalName])
: default(T);
}
Using
For example, to maximize the creation date and maximum values of ID:
var maxCreated = list.GetMaxValue<DateTime>("Created");
var maxId = list.GetMaxValue<int>("ID");