Converting Formulas

Developers can return Microsoft Excel formulas from views, stored procedures, and SQL queries.

For example:

SELECT
    id,
    name,
    qty,
    price,
    '=@qty*@price' AS amount
...

SaveToDB products convert these formulas into platform-specific calculated formulas (Excel, DataTable, or JavaScript).

The SaveToDB add-in supports all Excel formulas, while DBEdit, DBGate, and ODataDB support a subset.

SaveToDB products detect formulas by examining the first-row cell, analyzing the column formulas, and applying the formula to the entire column or each cell.

ConvertFormulas and DoNotConvertFormulas

Developers can enable or disable formula conversion using the ConvertFormulas and DoNotConvertFormulas handlers in the xls.handlers table.

For example:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
	s02	usp_cashbook5	balance	ConvertFormulas			ATTRIBUTE
	xls	handlers	HANDLER_CODE	DoNotConvertFormulas			ATTRIBUTE

Use ConvertFormulas to enforce formula conversion even if the first-row cell doesn't contain a formula, such as an initial balance.

Use DoNotConvertFormulas to prevent conversion when the first-row cell might contain a value, like in the HANDLER_CODE field.

Formula and FormulaValue

Developers can set formulas to calculate column values using the Formula event type and specifying formulas in the HANDLER_CODE field.

For example:

ID	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_SCHEMA	HANDLER_NAME	HANDLER_TYPE	HANDLER_CODE
	s04	usp_order_form	amount	Formula			ATTRIBUTE	=@qty*@price
	s22	cashbook	amount	DoNotChange			ATTRIBUTE

This case is similar to the one shown in the introduction. The query returns actual data, not formulas, while the formulas are added after loading. This method can also be used for tables.

Additionally, products save all rows when new calculated values differ from the loaded values. This feature allows for recalculating column values.

The sample configuration also includes the DoNotChange handler, which disables manual cell changes.

The second option is FormulaValue.

For example:

TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	EVENT_NAME	HANDLER_TYPE	HANDLER_CODE
s22	cashbook	modified_by	FormulaValue	ATTRIBUTE	=DomainUserName()
s22	cashbook	modified_on	FormulaValue	ATTRIBUTE	=NOW()
s22	cashbook	modified_by	DoNotChange	ATTRIBUTE
s22	cashbook	modified_on	DoNotChange	ATTRIBUTE

Unlike the Formula type, these formulas are calculated when a user modifies any row cell.

You can use standard Excel formulas or special built-in formulas like =DomainUserName() and =UserName().

Using DDE Formulas

You can use the SaveToDB add-in to easily create DDE dashboards.

For example, here’s a sample SQLite configuration for a Thinkorswim watch list using an SQL query in the xls.objects table:

ID	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	TABLE_CODE	INSERT_OBJECT	UPDATE_OBJECT	DELETE_OBJECT
		DDE WatchList	CODE	<SQL code>	WatchList	WatchList	WatchList

In the TABLE_CODE field, include the following SQL code:

SELECT
    Symbol,
    '=TOS|LAST!' + Symbol AS Last,
    '=TOS|NET_CHANGE!' + Symbol AS NetChange,
    '=TOS|PERCENT_CHANGE!' + Symbol AS Change,
    '=TOS|HIGH!' + Symbol AS High,
    '=TOS|LOW!' + Symbol AS Low,
    '=SUBSTITUTE(TOS|VOLUME!' + Symbol + '," ","")+0' AS Volume
FROM
    WatchList

Note that you can save data to SQLite databases using the free SaveToDB edition.