OG data type - DataRowFilter

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