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 such formulas to platform-specific calculated formulas (Excel, DataTable, or JavaScript).

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

SaveToDB products detect such formulas by the first-row cell, analyze the column formulas, and apply the formula to the entire column or every cell.

ConverFormulas and DoNotConverFormulas

Developers can turn on or off converting formulas using the ConvertFormulas and DoNotConverFormulas 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 force converting formulas even if the first-row cell does not contain a formula like an initial balance in the example.

Use DoNotConvertFormulas to prevent converting formulas if the first-row cell can contain a value like a formula, for example, 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.

However, the query returns actual data but not the formulas while the formulas are added after loading. So, you can use this method for tables also.

Also, products save all rows when new calculated values are not equal to the loaded values.

This powerful feature allows recalculating column values.

The sample configuration also contains the DoNotChange handler. Use it to disable 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

Contrary to the Formula type, such formulas are calcucated when a user changes any row cell.

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

Using DDE Formulas

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

For example, here is 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

Where the TABLE_CODE field contains 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 use can save data to SQLite databases using the free SaveToDB edition.