This feature can be used anywhere where you use SQL Queries. With SQL variables, you can write cleaner code and easily implement custom dropdown boxes or advanced security.
Note: SQL variables are case-insensitive.
A list of SQL variables
:field
the current field value on an Add, Edit or Register page, for instance :make
:master.field
any field from the master record.
:session.key
any session variable.
:user.field
any field from the login table (from the record that belongs to the current user).
:old.field
an old field value (before the changes were applied).
:new.field
a new field value.
Where to use the SQL variables
1. In regular SQL queries that you enter on the SQL Query screen.
2. In WHERE Tabs on the SQL Query screen.
3. In a Lookup Wizard WHERE clause. It can be used in dropdowns that are dependent on any type of field, or a master dropdown. Alternatively, it can be used when dependent dropdowns follow a more complex rule than equality (i.e., age is more than the selected). Note, that you need to add single quotes around text values.
CustomerID = :UserID
CustomerID = ':user.CustomerID'
CustomerID = ':session.UserID'
You can also use session variables that are arrays. For instance, you have a session variable named CustomerIDs that is an array and stores a list of Customer IDs like: 3, 5, 17. You can now use this array in WHERE clause this way:
customer_id IN ( :session.CustomerIDs )
which will produce the the following WHERE clause:
customer_id IN ( 3, 5, 17 )
And the same idea with text values. If you have a session variable named Postcodes that is an array of the following values: 'ABC', 'CBD', DEC'. Now in your WHERE clause you can use the following.
postcode IN ( ':session.Postcodes' )
which will produce the the following WHERE clause:
postcode IN ( 'ABC', 'CBD', DEC' )
4. In events by using the PrepareSQL function. For example, in the After Add event you can use:
sql = DB_prepareSQL("insert into log set lastid=:new.id")
DB_Exec( sql );
In the Server code of a Custom Button or in Field events for View/Edit pages:
sql = DB_prepareSQL("insert into log set lastname=':name'")
DB_Exec( sql );
See also:
•Lookup wizard: WHERE expression
