Suppose you have a user who has some additional data outside their database, such as in a Microsoft Excel spreadsheet, and they want to report on that data as well. Because Stonefield Query can only report on tables defined in the data dictionary, the "database" (the Excel spreadsheet containing the data), the "table" (the worksheet within the spreadsheet), and the "fields" (the columns in the worksheet) have to be added to the data dictionary, as well as any relations between the table and the other tables in the database.

If you can do this in Stonefield Query Studio, it's obviously a lot easier than the following technique. However, if a one-off thing one user wants to do, they'll need to do it programmatically at runtime. Here's some sample code that would go in a text file named SETUP.SQS (which Stonefield Query automatically executes at startup if it exists). Note that this code must use Visual FoxPro syntax.

local loDatabase as Database, loDataSource as ODBCDataSource, ;
    loTable as Table, loField as Field, loJoin as Join
 
* Add the database and data source.
 
loDatabase = SQApplication.DataEngine.Databases.AddItem('MySpreadsheet')
loDataSource = loDatabase.DataSources.AddItem('ODBC', 'MySpreadsheet')
loDataSource.ConnectionString = 'Driver=Microsoft Excel ' + ;
    'Driver (*.xls);dbq=C:\MySpreadsheet.xls'
loDatabase.OpenDataSource('MySpreadsheet')

* Add the table.
 
loTable = SQApplication.DataEngine.Tables.AddItem('[Sheet1$]')
loTable.Database = 'MySpreadsheet'
loTable.Caption = 'Customer Details'
 
* Add the fields in the table.
 
loField = SQApplication.DataEngine.Fields.AddItem('[Sheet1$].Date')
loField.Caption = 'Date'
loField.Type = 'D'
loField = SQApplication.DataEngine.Fields.AddItem('[Sheet1$].Customer')
loField.Caption = 'Customer'
loField.Type = 'C'
loField.Length = 5
loField = SQApplication.DataEngine.Fields.AddItem('[Sheet1$].Time')
loField.Caption = 'Time'
loField.Type = 'N'
loField.Length = 8
loField.Decimals = 2
 
* Add a relationship to the Customers table.
 
loJoin = SQApplication.DataEngine.Joins.AddItem('[Sheet1$],Customers')
loJoin.ParentTable = 'Customers'
loJoin.ParentExpression = 'Customers.CustomerID'
loJoin.ChildTable = '[Sheet1$]'
loJoin.ChildExpression = '[Sheet1$].Customer'

Excel treats each worksheet in a spreadsheet as a separate table and names it with the sheet name followed by a , so that's why the name is in square brackets. Also, if any columns have spaces or other non-SQL characters in their names, put those names in square brackets as well; for example, "[Sheet1].[Order ID]."