Value Lists with OData

This topic covers how to configure OData services to provide value lists for the SaveToDB add-in, version 10 or higher.

OData standards do not include features like value lists. To work around this limitation, you can configure missing features in two ways:

Using annotations
Using OData objects to select the configuration

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

For third-party OData services, developers can publish the xls.handlers table as an EntitySet. The SaveToDB add-in detects these EntitySets by field signatures and loads the configuration after retrieving the metadata document.

Configuring value lists is similar to the following topics:

However, you'll need to supply OData objects instead of database objects.

For example, when using tables, the configuration looks like this:

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 s02.usp_cashbook in the s02 schema with the name usp_cashbook. In this case, you can keep the left part the same since it uses the type schema and name.

However, the handler part must specify an EntitySet instead of the table. If your OData service publishes the table in the default schema container with the name s02_companies, the configuration will look like this:

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 HANDLER_CODE field, similar to tables and views. However, the best practice is to create dedicated endpoints to define the value list columns.

Note that the configurations do not conflict. Users can connect to a database and consume the first configuration or connect to the OData service and consume the second one.

Here’s a sample configuration for loading 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 corresponding OData configuration would look like this:

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 uses FunctionImport objects instead of procedures.