Value Lists with OData

Value Lists with OData

This topic discusses configuring OData services to supply value lists for the SaveToDB add-in, version 10 or higher.

OData standards have no features like value lists.

There are two ways to configure missing features via OData:

  1. Using annotations
  2. Using OData objects to select the configuration

ODataDB uses value list annotations to configure the built-in JavaScript client. The SaveToDB add-in also reads such annotations.

For the third-party OData services, developers can publish the xls.handlers table as EntitySet.

The SaveToDB add-in detects such EntitySets by field signatures and loads the configuration after loading the metadata document.

Configuring value lists are similar to the following topics:

However, you have to supply OData objects instead of database ones.

For example, using the tables looks like:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s02usp_cashbookcompany_idValidationLists02companiesTABLEid, +name
 s02usp_cashbookcompany_idParameterValuess02companiesTABLEid, +name

Suppose your OData service publishes the s02.usp_cashbook in the s02 schema with the usp_cashbook name.

In this case, you can leave the left part the same as it uses the type schema and name.

However, the handler part must contain an EntitySet instead of the table.

Suppose your OData service publishes the table in the container of the default schema with the s02_companies name.

Accordingly, the configuration will look like

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODE
 s02usp_cashbookcompany_idValidationListdefaults02_companiesENTITYSETid, +name
 s02usp_cashbookcompany_idParameterValuesdefaults02_companiesENTITYSETid, +name

The SaveToDB add-in supports field list extensions in the HANDER_CODE field like for tables and views.

However, the best way is to create special endpoints to define the value list columns.

Pay attention that the configurations have no conflicts. Users can connect to a database and consume the first configuration or the OData service and consume the second one.

Here is a sample configuration to load value lists using stored procedures:

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
 s02usp_cashbookcompany_idValidationLists02xl_list_company_idPROCEDURE
 s02usp_cashbookcompany_idParameterValuess02xl_list_company_idPROCEDURE

The OData configuration can look like

IDTABLE_SCHEMATABLE_NAMECOLUMN_NAMEEVENT_NAMEHANDLER_SCHEMAHANDLER_NAMEHANDLER_TYPEHANDLER_CODETARGET_WORKSHEETMENU_ORDEREDIT_PARAMETERS
 s02usp_cashbookcompany_idValidationListdefaults02_xl_list_company_idFUNCTIONIMPORT
 s02usp_cashbookcompany_idParameterValuesdefaults02_xl_list_company_idFUNCTIONIMPORT

This configuration contains FunctionImport objects instead of the procedures.