SQL properties

In LISA it's possible to have small snippets of SQL to run either on New, Update or Load. It is adviced to have all logic during the Update sequence, to avoid decreasing performance.

The SQL properties in LISA takes normal MSSQL syntax. But all queries are subqueries. Meaning that you are already within an active select statement on the record you are working on.

SELECT
-- What you write here is what is run witin the outer select.
FROM [<sometable>] WHERE [<sometable<].[id<sometable<] = <the id of the record saved>

To multiple two fields on the same record, you can simply write

[<sometable>].[<somefield>]*[<sometable>].[<someotherfield>]

If you need to get something from a related table, you will have to format your query as you would a subquery, so similar to this:

(SELECT a.[<somefield>] FROM [<someothertable>] a WHERE a.[id<someothertable>] = [<sometable>].[<relatedfield>])

The code that is actually run then is:

SELECT
(SELECT a.[<somefield>] FROM [<someothertable>] a WHERE a.[id<someothertable>] = [<sometable>].[<relatedfield>])
FROM [<sometable>] WHERE [<sometable>].[id<sometable>] = <the id of the saved record>
  • Last modified: 4 years ago
  • by Simon Jehrbo