1. OG data type - DataRowFilter

    Article: AN0001681Updated: 18.03.2020

    This object is used for search of records in classes and queries. After specifying required conditions ObjectGear returns the records.

    Object properties

    Name Description
    int? Id Record ID.
    List Ids List of enabled record ID.
    int? IdMin Min. record ID (including).
    int? IdMax Max. record ID (including).
    int? ArchiveId Archived record Id.
    int ParentId Id of the class or query from which we want to read the records.
    ClassDefEntityType ParentColumnType Type defining, if ParentId is class or query.
    bool Filter_BindOnlyShortDescription Indication for fast reading from the database. If you read record with links to reference tables and you need to read only Id and ShortDescription, than enter True. This setting causes that the referred entity will not be read with all the columns, which makes returning results faster.
    bool Filter_Archive Indication, wheather archive records shall be read. It is not possible to combine reading from operational and archive table in one object call.
    object GetColumnData(int columnId) Function returns value for input column ID. If nothing is input, NULL is returned.
    object GetColumnData(string columnCode) Function returns value for input column column. If nothing is input, NULL is returned.
    void SetColumnData(int columnId, object data) This method sets the condition for a particular column. Specify the value data in the correct data type in relation to the class type.
    void SetColumnData(string columnCode, object data) Method sets a condition for a particular column. Define the value data in the correct data type (according to the column type). Enter the code in form 'code' or 'parent_code.column_code' for columns from the parent class.
    object this[int columnId] Function sets or returns value for the defined column.
    object this[string columnCode] Function sets or returns value for the defined column.
    bool Filter_LoadFiles Indication, wheather information about column of the type File shall be read. If your class or query contains this column type but you do not need to read this information, than set False.
    ConditionBase Condition Property for a more complex definition of conditions for search.
    string Filter_Sort Specification of sorting of the returned records.
    int? Filter_StartRowIndex Order value of the first returned record. All the records are numbered and by this property you can set, which record will be returned as the first one. All the previous records will be removed from the result.
    int? Filter_MaximumRows Max. number of returned record. You can use this property together with Filter_StartRowIndex to page the records.
    bool Filter_OnlyActive Indication, wheather only records, that are active (not deleted), shall be returned. Column Deleted is null for active records.
    bool Filter_LoadSubEntity Indication, wheather also "subentities" shall be returned. Subentities are reference columns with M:N relation. To return the data faster you do not need to read these columns. However, if you need these columns set this property to True.
    bool WriteSqlToLog Indication, wheather the resulting SQL command shall be written into the event log.
    void AddAndCon(ConditionBase c) Method adds another condition to a condition and associates that with operator AND.
    void AddAndCon(ConditionBase c, bool addEnvelope) Method adds another condition to a condition and associates that with operator AND. If set to TRUE, parameter addEnvelope encloses conditions with a bracket.
    void AddAndCon(ConditionBase c1, ConditionBase c2, bool addEnvelope) Method adds another condition to a condition and associates that with operator AND. If set to TRUE, parameter addEnvelope encloses conditions with a bracket.
    void AddAndCon(ConditionBase c1, ConditionBase c2, ConditionBase c3, bool addEnvelope) Method adds another condition to a condition and associates that with operator AND. If set to TRUE, parameter addEnvelope encloses conditions with a bracket.
    void AddAndCon(ConditionBase c1, ConditionBase c2, ConditionBase c3, ConditionBase c4, bool addEnvelope) Method adds another condition to a condition and associates that with operator AND. If set to TRUE, parameter addEnvelope encloses conditions with a bracket.
    void AddOrCon(ConditionBase c) Method adds another condition to a condition and associates that with operator OR. If set to TRUE, parameter addEnvelope encloses conditions with a bracket.
    void AddOrCon(ConditionBase c, bool addEnvelope) Method adds another condition to a condition and associates that with operator OR. If set to TRUE, parameter addEnvelope encloses conditions with a bracket
    void AddOrCon(ConditionBase c1, ConditionBase c2, bool addEnvelope) Method adds another condition to a condition and associates that with operator OR. If set to TRUE, parameter addEnvelope encloses conditions with a bracket
    void AddOrCon(ConditionBase c1, ConditionBase c2, ConditionBase c3, bool addEnvelope) Method adds another condition to a condition and associates that with operator OR. If set to TRUE, parameter addEnvelope encloses conditions with a bracket
    void AddOrCon(ConditionBase c1, ConditionBase c2, ConditionBase c3, ConditionBase c4, bool addEnvelope) Method adds another condition to a condition and associates that with operator OR. If set to TRUE, parameter addEnvelope encloses conditions with a bracket

    All the conditions entered in the filter are mutual relation AND. Exception from this rule are conditions entered by means of property Condition. In this property you can set the conditions in whatever way.

    Use the property WriteSqlToLog, if you are creating a complex filter that does not work properly. When this property is set up, the resulting query passed to the database is saved into the event log, where you can check it to identify cause of the filter malfunctioning. Also parameters, which the command was executed with, are recorded in the log.

    Set up of values for columns

    Filtering for columns is set by method SetColumnData. The first parametr is ID of the column and the second one is the requested value. At this input exact match (apart from texts - see hereinafter).

    For each column you have to enter the data in the correct data type. Folowing table describes emabled data types for columns:

     

    Column type Enabled values
    Short text(50 characters) string
    Middle text (255 characters) string
    Long text string
    Integer int
    Decimal double
    Date and time datetime
    Indication Yes/No bool
    File Not supported
    Value from a referenced class int, List (regardless reference being simple of multiple)
    Reference to a user int, List (regardless reference being simple of multiple)
    Guid string
    Reference to a class Not supported
    Colour int
    Picture string

    Filtering texts

    When specifiing text filter for a text column several rules can be used depending on use of control characters. Following table shows all the possibilities of the input. The default way of search is in the first row of the table.

     

    Input Condition Description
    bike column like '%bike%' Text will be searched by the operator LIKE.
    "bike" column = 'bike' Exact match with the input is searched.
    "bike%" column like 'bike%' Text will be searched by the operator LIKE. The searched text has to start with "bike".
    "bi_ke" column like 'bi_ke' Text will be searched by the operator LIKE. The searched text has to start with "bi", than there is one character and the text ends with "ke".
    "bi%ke" column like 'bi%ke' Text will be searched by the operator LIKE. The searched text has to start with "bi" and and with "ke". There can be whatever number of further characters between those to texts.

    Meaning of control characters:

     

    Character Description
    " Quotation marks

    Quation marks at the beginning and at the end of the searched text indicate that you search for an exact match.

    However, if you enter character of percentage or underscore, than search is performed by the operator LIKE, but according to your "mask".

    % Percentage This character stands for whatever number of other characters.
    _ Underscore This character stands for one other characters.

    Record sorting

    Property Filter_Sort is used for records sorting. Sorting can be entered for more columns, particular columns shall be separated by comma. Each column can have ascending or descending order (in this case add text DESC after the column name).

    Names of columns shall be entered as they are defined in the object DataRow. E.g. Id, Created...

    To sort columns of a class or query use format colxxx, where xxx is ID of the sorted column.

    To sort acording to the short description use format sdxxx, where xxx is ID of the class or query.

    Example of sorting:

    sd86, col123 desc, created

     You can use directly column codes by means of command CODE to order the values. Then you do not need to create names like col123.

    CODE:name,title desc

     

    Data types for column filtering

    You can use various data types according to the column type to filter according to the column. The below table shows types that you can use:

     

    Column type Data type
    Text string
    Guid string
    Integer

    int?

    double

    EntityBase (EntityInfo, PersonInfo)

    Colour

    int?

    double

    EntityBase (EntityInfo, PersonInfo)

    Decimal double
    Date DateTime
    Indication Yes/No bool
    Classlink

    int

    double

    EntityBase (EntityInfo, PersonInfo)

    List<int>

    EntityInfoList

    XClasslink

    EntityInfo

    EntityInfoList

     

×