How to use variables in the Database Query Builder?

[Paul D]

I would like to use a variable inside of a "HAVING name = " in my SQL statment using the Database Query Builder... anyway to do this?

[Janschenkel]

Hi Paul,

You can't use variables in the database query builder, but there is an undocumented command 'revSetSQLOfQuery' that you can use for this:

Code: Select all

on mouseUp
  revSetSQLOfQuery "orderdetails", "SELECT * FROM orderdetails WHERE orderid =" && (field "OrderID")
end mouseUp

where "orderdetails" is the name of the query that you created using the database query builder.

To learn more about the database query system, you can review the 'revDatabase' frontscript:
- open the Message Box
- use the third button from the right to switch to the Front Scripts panel
- tick the checkbox 'Show Revolution UI Front Scripts'
- doubleclick the line 'revDatabase' to review its script
Watch and learn, but make sure not to modify anything

With some clever use of the undocumented database query commands and functions, you can actually get a pretty complete master-detail form working in very little time.

Hope this helped,

Jan Schenkel.