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:

Using annotations
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:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
	s02	usp_cashbook	company_id	ValidationList	s02	companies	TABLE	id, +name
	s02	usp_cashbook	company_id	ParameterValues	s02	companies	TABLE	id, +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

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
	s02	usp_cashbook	company_id	ValidationList	default	s02_companies	ENTITYSET	id, +name
	s02	usp_cashbook	company_id	ParameterValues	default	s02_companies	ENTITYSET	id, +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:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE	TARGET_WORKSHEET	MENU_ORDER	EDIT_PARAMETERS
	s02	usp_cashbook	company_id	ValidationList	s02	xl_list_company_id	PROCEDURE
	s02	usp_cashbook	company_id	ParameterValues	s02	xl_list_company_id	PROCEDURE

The OData configuration can look like

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE	TARGET_WORKSHEET	MENU_ORDER	EDIT_PARAMETERS
	s02	usp_cashbook	company_id	ValidationList	default	s02_xl_list_company_id	FUNCTIONIMPORT
	s02	usp_cashbook	company_id	ParameterValues	default	s02_xl_list_company_id	FUNCTIONIMPORT

This configuration contains FunctionImport objects instead of the procedures.