Customize column sorting
Find solutions on typical sorting issues: It is not possible to sort a column. The sorting of a column delivers wrong results.
Sorting is not possible
Reason
The column is configured as not sortable in the corresponding definition.
Typically, there are three reasons that the sorting of a column is deactivated:
The original reason to deactivate the sorting does not exist any more. But, the definition of the column has not been adapted.
The actual definition of the column delivers wrong sorting. The sorting has been deactivated to avoid confusion.
As of the actual definition of the column the sorting is not possible. Therefore, the sorting has been deactivated.
Analysis
Take a look at the entry of the column in the corresponding table definition in the censhare Admin Client.
For example, this is how to find the table definition for the main window of the censhare Client:
Go to the Module Clients/Client in the censhare Admin Client.
Open the module Client Dialogs Tables.
Click the Edit XML file to open the XML of the table definition.
Search for the table. For instance, the key for table of the main window of the client is the following: "table-columns-text-all-but-name"
This is how the table definition looks like:
<xe:table-columns key="KEY_TABLE">
<xe:column ... />
....
<xe:column ... />
</xe:table-columns>"<xe:table-columns key="KEY_TABLE"> ... </xe:table-columns> defines the table.
KEY_TABLE is the key of the table.
"<xe:column ... >" contains the definition of a column table.
The entry "sortable="false"" in the column description prevents the user from sorting the column with a click on the head of the column. Therefor, no arrow for the sorting direction in the column head shows up when clicking.
The value for sorting already exists within the asset data
If the data source for the sorting values points to a field within the asset data the sorting of the column is easy.
This, for instance, is true with the author. The author of an asset is directly saved within a field of the asset using the authors name. Therefor, it is possible to sort the values before they are written into a column.
If the definition of a column in the corresponding table in the Admin client contains the entry "sortable=false" the user cannot sort the column.
Example of a column that displays the author of an asset and that is not sortable:
<xe:column key="author" label="${ author} " sortable="false" width="150" source="@author"/>"source="@author" specifies the data source of the column. In this case the asset field "author" is used. The database uses this field for sorting. The client receives the sorted rows from the database and displays them.
The parameter "sortable=false" has to be removed only. After that, the column is sortable:
<xe:column key="author" label="${ author} " width="150" source="@author"/>No sorting possible: the column value displayed is calculated
The value shown in the column is not always directly saved in the asset. For instance, this is the case with the name of the workflow target. censhare does not save the name of the workflow target but only its ID. To show the name, the client has to look up the name for the ID with an internal table and formula. This formula is inserted into the description of the column in the according table definition in the Admin Client.
This is an example of a formula that reads the ID of the defined workflow target and uses the ID to get the name belonging to:
formula=":cs:cachelookup('party', '@id', asset/@wf_target)/@display_name"The client then shows the calculated values in the column. The problem with this is that the sorting of the values has to happen already at the database, before the client receives the values. The client itself cannot sort. But at the database, the values still does not exist because they are calculated later at the client. This is the reason why it is not possible to sort.
The following example for a definition of a column uses the formula that was just mentioned above:
< xe:column key = "workflow-target" label = "${ workflow-target} " sortable = "false" width = "150" formula = ":cs:cachelookup('party', '@id', asset/@wf_target)/@display_name" />As the sorting is not possible the column is defined as not sortable.
As a workaround, you can define another field for the sorting. If there is no such field, another possibility is to create an additional field in the database that is then used for sorting. One condition for that is that the formula does not use any values outside of the actual asset. For instance, this could be values in the Master data.
Sorting delivers wrong results
The sorting of a column can deliver wrong results if the values used for the sorting are different from the values shown. A column, for instance, shows the name of the defined workflow target of an asset. The definition of the column uses a formula for that. Therefor, a sorting after the shown values is not possible.
As of that, the definition of the column contains a parameter that defines another asset field to be used for sorting. So, the column shows the name of the workflow target but the sorting happens with the value of the workflow target itself. This is not the name of the workflow target but it's ID that is a number. The sorting is done with the ID not the name. But the latter value is shown. The user himself expects that the column is also sorted after the name. This is why he considers the sorting to be wrong.
Definition of a column that shows the name of the workflow target but uses the ID of the workflow target for sorting:
< xe:column key = "workflow-target" label = "${workflow-target} " width = "150" sorting-source = "@wf_target" formula = ":cs:cachelookup('party', '@id', asset/@wf_target)/@display_name" /> The formula ":cs:cachelookup('party', '@id', asset/@wf_target)/@display_name"" determines the name of the workflow target. "sorting-source="@wf_target"" defines that the column will be sorted after the ID of the workflow target.
In order to avoid a wrong sorting there is the possibility to disallow the sorting of the column. For this reason the parameter "sortable=false" is inserted and the parameter "sorting-source=@wf_target"" is removed from the column definition:
< xe:column key = "workflow-target" label = "${workflow-target} " width = "150" sortable = "false" formula = ":cs:cachelookup('party', '@id', asset/@wf_target)/@display_name" />As an alternative it is possible to insert a field into the database containing the formula that is then used for sorting.
Sorting using another field
If it is not possible to use the shown values for the sorting of the column, you can define an asset field that is used instead.
For this you have to add the parameter "sorting-source="@ASSET_FIELD" into the definition of the appropriate column. ASSET_FIELD is the name of the field that has to be used instead.
The following example uses a formula to detect the file length of the master file of an asset. This is converted into MB and formatted accordingly. If the asset has no master file the column is left empty. As it is not possible to sort after a formula the sorting for the column is deactivated:
< xe:column key = "filesize" label = "${ file-size} " sortable = "false" align = "right" width = "80" formula = ":if (asset/storage_item[@key= 'master']) then format-number((sum(asset/storage_item[@key= 'master']/@filelength) + 52429) div 1048576, '#,##0.0 MB') else ' ' " />To sort after the column in spite of that there is another asset field used for the sorting. In this particular case, the asset field for the length of the master file is used: "sorting-source="storage_item[key='master']@filelength"". The sorting is the same as if it has been done with the values of the formula. So, the column is sorted correctly.
The definition of the column is now the following:
< xe:column key = "filesize" label = "${ file-size} " sorting-source = "storage_item[key= 'master']@filelength" align = "right" width = "80" formula = ":if (asset/storage_item[@key= 'master']) then cs:format-number((sum(asset/storage_item[@key= 'master']/@filelength) + 52429) div 1048576, '#,##0.0 MB') else ' ' " />Create an additional field for the sorting using a formula
The column of table should contain the pagination of an asset after it has been placed on the flatplan if the pagination exists. A formula checks if the according asset field exists and shows the value where applicable. If the values of a column are calculated, they cannot be sorted by the database. They do not exist there because they are only calculated afterwards in the client. As a solution for that you can create a field in the database that is filled with values from the formula. Then you can use the database field for sorting.
For instance, the following formula checks if the actual asset has a pagination. If this is the case the pagination will be formatted and shown in the column. If not, the column will stay empty:
formula=":if (//@first_paging) then cs:format-number((//@first_paging), '#') else ()"To sort the column, a field with the name "CUSTOM:function.sorting" will be created in the cdb (censhare database). CUSTOM stands for the name of the company. This shows that the field is not part of the censhare standard. The value of the field CUSTOM:function.sorting is the formula mentioned above.
The new database field is now named as the source for the sorting. The parameter is the following:
sorting-source="CUSTOM:function.sorting"The complete definition of the column now looks like this:
< xe:column key = "page" type = "number" label = "${ page} " width = "50" align = "right" sortable = "true" sorting-source = "CUSTOM:function.sorting" formula = ":if (//@first_paging) then cs:format-number((//@first_paging), '#') else ()" />Use the Admin Client to create the new field in the master data in the section "Features". Select the value "Asset feature" for the field "Type" in the dialog for the new feature. The field "Storage" gets the value "Function". After that the additional field "Attribute mapping" shows up. Here, you enter the formula that you have also used to calculate the shown values of the column in the table. It is also important to mark the checkbox "Searchable". If you do not check this box, you can still sort the column. But the values will not be found when searching for them.
For example mentioned before you have to define among other parameters the following in the master data:
ID = CUSTOM:function.sorting Type = Asset feature Value type = Floating-point number (Double) Storage = Function Attribute mapping = :if (//@first_actual_paging) then cs:format-number((//@first_actual_paging), '#') else ()The value "CUSTOM:function.sorting" for the ID has to be entered now as the value for the parameter "sorting-source" in the column definition as it has been done above. The field "Attribute mapping" contains as a value the formula, that is used to calculate the values for the column.
If you use an additional field in the database to sort a column the formula must use only values from the asset that is actual used for the calculation of the result. For example, it is not allowed to use data from the master data. The sorting can be wrong. This is because it cannot be guaranteed that the actual calculated and shown values in the column are the same values as saved in the database. The latter are used for the sorting.
The calculated value in the database for an asset field is recalculated if the asset is saved. For example, a formula uses a value from the master date like the user name. If the administrator changes the name because the user has married, the calculated results in the database will not be updated automatically. This happens only for a field if the corresponding asset is opened and saved again.
When you have created a feature with a formula in the master data the cdb has to be created once afresh. The values for the field are calculated and written into the database. Only after that, the values exist and can be used for sorting.
