Omnis Mysql defined list

Doug Easterbrook doug at
Thu Dec 15 18:43:42 EST 2016

hi Stephen:

we do something like this all the time — and do sql tricks to make it happen with the table class.   The general philosophy is that you NEVER NEVER (ok, hardly ever) want to change lines in a list through calculations because it really affects performance.

so, how can you do trick omnis to do this. ..

Step 1:  add variables to your query class that don’t exist in the database

eg, query has   firstName and LastName.   both are in the database.    you add variable called    CombinedName — which will be a calculation

Step 2:

Override the methods in the table class that fetch your data.      What you want to do is be able to get ahold of $SELECTNAMES just before it is sent to mysql.

from the query class above, your $selectNames will have    firstName,LastName,CombinedName      Again combinedName is NOT in the database, so this will generate an SQL error — we know this..

Step 3:

write some code like this to make a calculation on any columns you want.   We do it in postgres, so we’ve become adept and using stored procedures or any postgres function, calculations, date conversions or whatever we want to use

replacement = ‘ lastName || firstName || length(firstName)  as combinedName’
and then

calculate SQLSTRING as $replace(combinedname  with  replacement    in $selectNames )

now your SQL String (which was formerly $selectNames) is

firstName,lastName ,  lastName || firstName || length(firstName) as combinedName

and when you send that to the dam, you’ll get three columns back, one of which is calculated.

that gives you all sorts of performance … and can also be used in background workers to get data without manipulating the list.

hope that helps with the concept.         like I said, we use it all the time.

Doug Easterbrook
Arts Management Systems Ltd.
mailto:doug at
Phone (403) 650-1978

> On Dec 15, 2016, at 3:39 PM, Stephen Baugh <stephenbaugh at> wrote:
> Hi Everyone
> Is there a way to add columns to a .$definefromsqlclass list definition, so that a calculation is processed as the list is built?
> This might be a pick lookup or math function etc
> I want to add for example, the following calculations to this definition, after of course adding the new columns separately if required.
> Do $cinst.iWindowList.$definefromsqlclass($cinst.iRowTableName) Returns lvStatus
> Calculate Month as ovControl.$doDivide(C_SUPP_BAL_MTH,REG_EXCHANGE_RATE)
> Calculate Current as ovControl.$doDivide(C_SUPP_BAL_CUR,REG_EXCHANGE_RATE)
> Calculate 30days as ovControl.$doDivide(C_SUPP_BAL_30,REG_EXCHANGE_RATE)
> Calculate 60days as ovControl.$doDivide(C_SUPP_BAL_60,REG_EXCHANGE_RATE)
> Calculate 90days as ovControl.$doDivide(C_SUPP_BAL_90,REG_EXCHANGE_RATE)
> Both C_SUPP_BAL_MTH and REG_EXCHANGE_RATE are defined in the list definition.
> I hope this makes sense, and as always thanks for the help.
> Cheers
> Stephen
> _____________________________________________________________
> Manage your list subscriptions at

More information about the omnisdev-en mailing list