Hi Jack.
>If they set up a query with data from both tables I would assume they will only get records where there is a join. Is my assumption correct? Is there any way to give them a list of all addresses that don't have a sewer permit?
We don't currently have a UI way to set an outer join (but we will in a future release). In the meantime, you need to set the nOuterJoin property of the SFRelation object that relates those two tables to 1 (left outer join) or 2 (right outer join), depending on which table is in cAlias1. If you want to do that just for this report (ie. other reports would do an inner join), you can script it by putting code similar to the following into the DOQUERY memo fields in the reports table record for the report:
with loDataEngine
for lnI = 1 to .oRelationCollection.Count
loRelation = .oRelationCollection.Item(lnI)
lnJoin = loRelation.nOuterJoin
do case
case loRelation.cAlias1 = 'CLIENTS' and ;
loRelation.cAlias2 = 'SEWERS')
loRelation.nOuterJoin = 1
exit
case loRelation.cAlias1 = 'SEWERS' and ;
loRelation.cAlias2 = 'CLIENTS')
loRelation.nOuterJoin = 2
exit
endcase
next lnI
.oQuery.cFilter = lcFilter
.oQuery.cCursorAlias = lcCursor
.oQuery.lDoSelect = .T.
.oQuery.DoQuery()
loRelation.nOuterJoin = lnJoin
endwith
This code isn't too complex: it finds the correct relation object in the collection, saves its nOuterJoin value and sets it to either 1 or 2, sets the properties of the query object and calls its DoQuery method to perform the SQL SELECT, then restores the nOuterJoin property of the relation object.
The ability to script cursor creation is, IMO, a pretty powerful feature of SFReports and one that I hope folks will use to create complex queries when they're needed.
Doug