, ,

The DataGrid control

The DataGrid control is useful to display data coming from other lists using a list query, a web service call or an sql query.

Type : DataGrid

Name

Provides a unique name for the control.

Binding

Not applicable for DataGrig control. Always unbound.

Enabled for new records

Sets if the control will be enabled or disabled when the forms handles a new record

Enabled for existing records

Sets if the control will be enabled or disabled when the forms handles an existing record

Required

Marks the cotnrol as Required.

By default, the system recognizes required fields and marks them with this flag. Additionaly, controls can be marked as required event if the bound column is not.

Enabled Formula

Set an expression then will be evaluated during run-time and enable or disable the control.

The expression must be in javascript and should return a boolean value.

Dependencies between controls are automatically identified and the expression is re-calculated every time a control affecting the formula changes.

Example :

Code

form.GetControl(“c_Status”).GetValue() != “Open” && form.UserInGroup(“Administrators”)

Visibility Formula

Define a valid formula (using javascript) that will be evaluated during runtime to show or hide the control.

The expression should return a boolean value.

For example :

Code

form.FieldValue(“c_Status”) != “Rejected” || form.UserID() == “1”

List Query

A predefined list query is bound to the value of the control. So during form initialization, the specified list query is executed and binds the data grid

Web Service Call

A predefined web service is bound to the value of the control. So during form initialization, the specified list query is executed and binds the data grid

SQL Query

A predefined sql query is bound to the value of the control. So during form initialization, the specified list query is executed and binds the data grid

Width

Defines the width of the control.

When the value is zero, the maximum allowed width will be used.

Height

Defines the control height.

If the value is zero, the height property will not be set.

H.Alignment

Defines the horizontal alignment of the parent cell (values : left, right, center)

V.Alignment

Defines the vertical alignment of the parent cell (values : top, bottom, center)

Fore Color

Defines the fore color of the control

Back Color

Defines the Background color of the control.

Cell Color

Defines the color of the parent cell.

Font Size

Declares the font size of the text

Margin

Sets the margin applied to the control, that is, the spacing between the control and the cell borders.

Font Bold

Changes the weight of the font used

Italics

Changes the font style for the control text

 

 

PageSize

Define the page size for the grid. Zero indicates no paging.

ShowGroups

This property enables or disables grouping for the grid data

ListQueriesPerPage

If this property is set to True, the Record List Queries defined for this grid, will not be executed all at once during data load, but for every page in the grid (in case paging is activated). This can greatly improve loading performance for the data grid

SelectionChangedScript

You can write a script that will be executed once the selection is changed. The ‘item’ variable is available containing the item selected or null if the grid has no item selected

DataLoadedScript

You can write a script that will be executed once the data to be bound to the datagrid are loaded, just BEFORE they are placed in the grid. The ‘items’ variable is available containing the items retrieved. This event is used if you want to alter the items before they are shown.
Code
for (var i=0; i < items.length; i++)
{
   items[i].KPILookup = GetNameFromLookup(items[i].KPILookup);
}

 

 

LoadCompletedScript

Occurs AFTER the data have been placed inside the grid. The ‘items’ variable is available containing the items retrieved

Example :

Code

for (var i=0;i<items.length;i++) {
var c = items[i].ItemCount;
items[i].Image = “<center><img width=’24’ height=’24’ src=\”http://wssdev1/Docs/chat.png\” /></center>”;
}

 

 

DaysToShowNewIndicator

Define the duration (in days) for which the new indicator will be shown in the Field(s) where ShowNewIndicator=true. The duration is compared with the Created Date (‘Created’ field) of the record.

NewIndicatorImageURL

Specify the url of the image to display as a ‘new’ indicator.

 

Fields

Add the fields of the data entry grid

Properties

Name : The name of the field. It should begin with a letter and only contain letters, numbers and the underscore character

Header : The field header

Width :  The grid column width. Leave blank for Auto sizing.

DisplayFormat : Indicates the way the value will be presented inside the data grid. (The value as-is, the ValueField or DisplayField for lookup controls (ID;#TITLE).

FormatString : Sets the format for Date and Number fields. For example ‘#,##0.00’ or ‘MM/dd/yyyy’.

ForeColor : The cell foreground color

FontBold :  The text weight

FontSize : The size of the font used

ColumnFiltering :  If selected, then the grid displays a dropdown menu when the user clicks this column, for filtering and sorting purposes.

HAlign :  Sets the Horizontal Alignment. (Left, Center, Right)

ShowNewIndicator :  If this property is set to True, the sharepoint ‘new’ indicator will be shown next to the column value.

 

 

Hyperlinks

We are very happy to announce this brand new property for the DataGrid control. The Hyperlink property converts cell contents to hyperlinks, which when clicked, open a popup form to edit the corresponding entities. These popup forms look exactly the same, like when editing the item on the parent Sharepoint list, since they share the same customization.

 

An example of using Hyperlinks on two Datagrid columns (Name and Primary Contact) is shown below:

Clicking on a Hyperlink, the popup form appears:

 

In order to set a column to contain hyperlinks, you should create a Link using the following parameters:

ListUrl : The list’s site Url.

ListName : The list’s name.

FieldName : The field on the Datagrid that will be converted to a hyperlink.

IDFieldName : The field that contains the ID that will be used to open the edit form. This will be either the ID or a lookup column.

FormWidth : The popup form’s width.

FormHeight : The popup form’s height.

Style :  The hyperlink’s style. For example : text-decoration:none;color:black;

 

The hyperlinks on the above picture were created using the following Link parameters:

 

RecordListQueries

Define additional List Queries that will be executed for each item record retrieved. These can be used to retrieve additional information, even from other lists, for each record in the DataGrid.
Properties
ListQuery : The list query name.
SearchTargetField :  The internal column name of the query that we want to apply criteria.
SearchSourceField :  A field defined on the DataGrid control, whose value will be used as a criteria to filter the list query results.
SearchOperator :  The criteria operator. Possible Values: BeginsWith, Contains, Equals, NotEquals, Greater, GreaterOrEqual, Less, LessOrEqual, IsNull, IsNotNull
SearchType :  The criteria type. Possible Values: Text, Counter, Lookup, DateTime, Number
InitScript :  This script will be executed for each item before executing the list query. The variable ‘item’ is available, containing the current record. You can write custom JS code, to possibly change a value in the record, before performing the actual list query.
CallbackScript : The script that will be executed, once the list query returns the results. The variables ‘item’ and ‘result’ are available. The item represents the current record and the result represents the list query results, based on the current record. Available properies : result.ErrorMessage, result.ListItems

 

 

It is worth noting that you can opt to cancel a RecordListQuery from executing, by using the InitScript and setting e.Cancel = true; For example, using the InitScript:

Code
if (form.GetControl(“c_SomeRandomControl”).GetValue() == “0”)

 

{
     e.Cancel = true;
}

 

In controls like Datagrid and DataEntryGrid there are also some extra methods that can help you handle fields and rows in the grids.They use the InputControl property of these controls.The available methods are:

GetSelectedItem

This method returns an object item with the selected row of the grid.

Code

var item =  form.GetControl(“c_GridName”).InputControl.GetSelectedItem();

RefreshGrid

This method refreshes the grid’s content.

Code

form.GetControl(“c_GridName”).InputControl.RefreshGrid();

 

GetAllItems

This method returns a array of all object items that are bound to the grid

 

Code

var items =  form.GetControl(“c_GridName”).InputControl.GetAllItems();

 

 

Some examples:

How to get selected item from a grid called ‘c_GridName’ and set Title value to a label called ‘c_Label’.

Code

var item = form.GetControl(“c_GridName”).InputControl.GetSelectedItem();

form.GetControl(“c_Label”).SetValue(item.Title);

 

How to loop through all items and find the sum of a field called ‘Quantity’

 

Code

var sum = 0;

var items = form.GetControl(“c_GridName”).InputControl.GetAllItems();

 

for (var i=0; i < items.length; i++)
{
  sum = sum + items[i].Quantity;
}

 

 

 

ExportToExcel

As the name suggests, this function will export the data to an Excel file, ready to be saved or opened. The function accepts three parameters, described below:

ExportToExcel(array of columns, string file name, string sheet name) 

 

Columns should be an array of objects with “field” and “title” properties. If no columns are provided, all of the grid’s visible columns will be exported.

Example:

Code
var grid = form.GetControl(“c_Cases”).InputControl;

 

var cols = [];
cols.push({field:”CaseNumber”, title:”Case Number”});
cols.push({field:”Title”, title:”Title”});
cols.push({field:”CaseType”, title:”Case Type”});
cols.push({field:”CType”, title:”Case Category”});

 

grid.ExportToExcel(cols, “Cases.xls”, “Cases”);

 

SelectRowByIndex

As the name suggests, this function will select a row on the DataGrid. The function accepts one parameter, as described below:

SelectRowByIndex(integer theIndex) 

 

Example:

Code
form.GetControl(“c_DataGrid”).InputControl.SelectRowByIndex(1);

 

RunListQueries(all, callback)

This function runs all the list queries that have been declared at the property of the DataGrid named RecordListQueries. The function accepts the parameter ‘all’ that describes the case that the user wishes to run the list queries for all the items of the DataGrid or only the visible ones, as described below:

Example:

Code
form.GetControl(“c_DataGrid”).InputControl.RunListQueries(true, function(){  });
/0 Comments/by
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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