User-defined fields

The ability to add user-defined fields is added in the version 2.13. The users can add new fields to database objects and specify custom SQL expressions for them. These can be fields intended to perform some manipulations on real database object fields (concatenation, "if - then" expressions, arithmetic calculations, etc.) They can also be used to get values from lookup tables using correlated sub-queries. Of course, the end-user must have the necessary knowledge of SQL language, so we recommend to turn this feature on only if you know that if will be valuable for your end-users, or you can let manage such fields only by administrators.

Setting the QueryBuilderControl.EnableUserFields property to true enables addition and removal of user-defined fields by the end-user.

The programmer has to define the following events to display an editor to end-user: QB.Web.Canvas.Events.CanvasOnAddUserField, CanvasOnEditUserField.
The sample handlers are below.

  function(e, obj) {
    var datasource = obj.datasource; // link to the datasource object
    obj.addUserField(fieldName, expression); //method to add new User Field
  function(e, obj) {
    var datasource = obj.datasource; // link to the datasource object
    var fieldName = obj.fieldName; // current name of the edited field
    var expression = obj.expression; // current SQL expression
    obj.editUserField(fieldName, expression); //method to edit User Field

The new MetadataContainer.ExportUserFieldsToXML and ImportUserFieldsFromXML methods let save and restore user-defined fields to a file or a stream. They should be used to keep the user's work with user-defined fields between work sessions.

Custom expression editor

The UseCustomExpressionBuilder property prescribes to display the Edit button next to the Expression and/or Condition columns of the Grid control. Acceptable values are "None", "ExpressionColumn", "ConditionColumns", and "AllColumns". You should handle the GridBeforeCustomEditCell event to display the editor.
The sample is below.

  QB.Web.Application.Grid.on( QB.Web.Grid.Events.GridBeforeCustomEditCell, 
  function (e, obj) {
      var columnType = obj.columnType; // type of column: MetaData.FieldParamType enum
      var cell = obj.cell; // link to the edited grid cell
      var row = obj.row; // link to the edited grid row
      var value = obj.value; // current cell value
      // ...
      // set the new value by calling the cell.updateValue( newValue ) method

The validateCondition and validateExpression methods have let validate values for the Expression and Condition grid cells.
The code samples are below.

  function(isValid, error) {
    isValid - boolean,
    error - error description if conditionValue is not valid
  function(isValid, error) {
    isValid - boolean,
    error - error description if expressionValue is not valid

Passing custom data from client to server and vice versa

The QB.Web.Application.setUserData(data) method instructs the component to send the data to the server within the next "sync" event. Call the QB.Web.Application.update() method to fire this event. Handle the OnUserDataReceived event to receive this data on the server. The handler sample is below.

  protected void QueryBuilderControl1_OnUserDataReceived(QueryBuilder qb, 
    SessionStoreItem.UserDataReceivedHandlerEventArgs e)
    var receivedData = e.InputData; // data received from the client
    e.OutputData = "Foo"; // data to be sent back to the client

Handle the QB.Web.Core.Events.UserDataReceived event to receive that data on the client. The handler sample is below.

  QB.Web.Core.bind(QB.Web.Core.Events.UserDataReceived, function (e, data) {

See also: Active Query Builder ASP.NET Edition JavaScript API.

This article was helpful for 1 person. Is this article helpful for you?