Adding Fields and Relationships

As many Stonefield Query users already know, you can use a setup.sqs file to do some customization of the data dictionary when Stonefield Query starts up. For example, lets say you had an "Orders" table in your database, but the table didn't have a field for the total amount of the order. We could add such a field by placing the following in a setup.sqs file:


loField = SQApplication.DataEngine.Fields.AddItem('ORDERS.AMOUNT')
loField.Caption = 'Order Amount'
loField.Type = 'N'
loField.Format = '$'
loField.Length = 18
loField.Decimals = 2
loField.Expression = "RUNSQL('select SUM(UnitPrice*Quantity) from"+;
" [Order Details] where OrderID = ?OrderID')"
loField.Calculated = .T.
lofield.FieldList = 'Orders.OrderID'


Above, we are first adding a new field to the orders table called amount. We set the caption to Order Amount, which is how we will see this field being displayed in Stonefield Query. The secret to making this work is what we set the Expression to. For our new Amount field, the expression is a call to the Stonefield Query function RunSQL, which sends whatever SQL we like to directly to the database. So for this example, we send the SQL necessary to calculate the total that we are after.

Not many Stonefield Query users know that you can also add new joins with the setup.sqs file as well. Suppose that Stonefield Query didn't have a relationship defined between Customers and Orders in the data dictionary. We could add one with the following setup.sqs:


loJoin = SQApplication.DataEngine.Joins.AddItem('Customers,Orders')
loJoin.ChildTable = 'Orders'
loJoin.ChildExpression = 'Orders.CustomerID'
loJoin.Jointype = 0
loJoin.ParentTable = 'Customers'
loJoin.ParentExpression = 'Customers.CustomerID'


So, the above creates a new relationship, with Customers as the parent table, and Orders as the child table. The ChildExpression/ParentExpression fields contain the field from that table to join on, and a JoinType of '0' means an inner join.

If we instead wanted to have a more complex relationship, we could do something like the following:


loJoin = SQApplication.DataEngine.Joins.AddItem('Customers,Orders')
loJoin.ChildTable = 'Orders'
loJoin.JoinExpression = 'Customers.CustomerID = Orders.CustomerID"+;
" and Customers.Country = Orders.Country'
loJoin.Jointype = 0loJoin.ParentTable = 'Customers'


The above creates a relationship between Customers and Orders on two fields, CustomerID and Country (pretend this makes sense for me please). In addition to being able to add relationships with this file, we can also make changes to existing ones. For example, say we wanted to change an existing relationship between Customers and Orders to a left outer join instead (so we get all Customers, even if they don't have Orders). We could do this with:


loJoin = SQApplication.DataEngine.Joins.Item('CUSTOMERS,ORDERS')
loJoin.JoinType = 1


A JoinType of 1 means left outer join, while 2 is right outer, and 3 is a full join.