What are where_column_query valid standard column values?

Ugis

Hello,

For GET /v2/businessworkspaces REST API there is a parameter "where_column_query". I understand about custom column usage here, but can we use this parameter also for standard (system default) columns such as creation date, creator, modify date, etc.?

I've tried multiple values, but none of them seems to work for me. I always get error, for example: "Custom column CREATEDATE not found". At the same time, it works with "name" value.

Thanks,

Uģis

Andrea_AC

Hi Uģis,
If you have Builder and are familiar with OScript, you can examine the implementation of the API GET v2/businessworkspaces in the OTSAPXECM module, specifically within the OTSAPREST object located in RestAPI2/Businessworkspaces, method GetBusinessWorkspaces. By following the code, you’ll reach the point where the query is constructed in the method OTSAPWKSP:ECMWorkspace Root/API/WorkspaceAPI._CreateQueryFilters, where, among other things, an SQL statement is created containing the string FROM WebNodesFilter wnf INNER JOIN DTree dt ON dt.DataID = wnf.DataID. Subsequently, the condition expressed in the where_column_query parameter is applied (within the _CreateQueryFilter function), revealing that all fields are prefixed with wnf., which corresponds to the WebNodesFilter table and not to DTREE. The query construction looks like this:
 
queryWorkspaces += "wnf." +
filter.columnName +
" " + filter.operator +
" :A" +
  Str.ValueToString(Length(queryParams)) + ' '
 
This explains why the fields you're searching for (which are all in DTree) do not exist. You must restrict your search to the fields within WebNodesFilter.

An alternative solution could be to implement your own API layer. If you have the Module Suite, this is both straightforward and quick to do. This way, you won't be constrained by the API you mentioned and can implement your own logic to retrieve the information you need. You can handle the business logic for searching the BWS in various ways according to your preference. You can either make direct database calls or utilize the services offered by the Module Suite, such as "docman" or "search."
What you need to do is create a content script in the content script volume: CSServices (accessible from the administration page) and define your API.

Example:

Additionally, by implementing this solution, you will benefit from increased resilience. It will also provide better visibility and decoupling, effectively hiding low-level details from the caller through the use of a facade. This approach enhances expressiveness and decouples systems, giving you more flexibility and control.

I'll send you the reference to the Module Suite in case you want to explore it further: Material for MkDocs - Module Suite 3.7.0 User Manual (answermodules.com)

Best regards,

Andrea