SQL Object
 
Syntax
 
SQL.RunDataMultiList QueryArray [, Params][, AlternativeTitle][, LayoutString]
 
Description
RunDataMultiList opens a window displaying the results of multiple SQL queries (VIEW, TABLE, or SELECT), where the data from these queries can be browsed but not modified. This method is ideal for situations where you want to view up to three tables or queries in a single window and establish relationships between them for synchronized viewing.
 
Each query is provided as an element of the QueryArray. Optionally, the Params array allows you to define how the tables are related by specifying the links (or joins) between them. It is essential to include a numeric prefix (1., 2., 3.) in the Params definitions to reference the corresponding tables from QueryArray. This ensures that the join conditions apply to the correct queries and that the relationships between the datasets are correctly established.
 
The method is similar to the “Data list” output option in the SQL Designer  but allows for viewing multiple tables simultaneously, something that can only be achieved via scripting.
 
Parameter
Description
QueryArray
A zero-based array with up to three elements. Each element must reference a View, Table, or a valid SQL SELECT query. The first element (QueryArray(0)) will be the primary table, with the following queries acting as linked tables.
Params
Optional. A zero-based array that defines the relationships (joins) between the tables in QueryArray. Each element of Params specifies the synchronization or join condition between two consecutive tables. Each condition must begin with a prefix number (1., 2., 3., etc.), corresponding to the tables in QueryArray.
For example:
  • Params(0) defines the link between QueryArray(0) (referenced as 1.) and QueryArray(1) (referenced as 2.).
  • Params(1) defines the link between QueryArray(1) (referenced as 2.) and QueryArray(2) (referenced as 3.).
Failing to use these numeric prefixes will result in incorrect or failed joins.
AlternativeTitle
Optional. A string that specifies an alternative title for the window. If not provided, a default title is used. If LayoutString is provided, you should also specify this parameter.
LayoutString
Optional. A string used to store the personalized layout settings, such as column widths and order, for the resulting data view. This is particularly useful for ensuring that users can keep their preferred view settings across sessions.
 
See Also
other SQL.Runxxxxxxxx methodes, Datalijst.
Example
Option Explicit
 
' Example of Multiple Data lists, related to each other
' -------------------------------------------------------
'Query(0) = "SELECT * FROM Leveranciers"
'Query(1) = "SELECT * FROM InstalBase"
'Query(2) = "SELECT * FROM ArtikelLink"
'Params(0) = "1.Admin = 2.Admin AND 1.InstalBaseCode = 2.LevKode"
'Params(1) = "2.LevKode = 3.LevKode AND 2.Nummer = 3.LevNummer"
'Params(2) = {empty}  because not applicable
 
Sub Main()
 
    Dim Query(0 To 2) As String
    Dim Params(0 To 1) As String
 
    Query(0) = "SELECT * FROM Leveranciers"
    Query(1) = "SELECT * FROM InstalBase"
    Query(2) = "SELECT * FROM ArtikelLink"
 
    ' Define the relationships (joins) between the tables
    Params(0) = "2.LevKode = 1.InstalBaseCode"    ' Link between Query 1 and Query 2
    Params(1) = "3.LevKode = 2.LevKode AND 3.LevNummer = 2.Nummer"    ' Link between Query 2 and Query 3
 
    ' Run the multi-table query and present the results
    SQL.RunDataMultiList Query, Params, "Data Related", "My Data Related Test"
 
End Sub