Custom where clauses are  a great way of filtering the results of all the top object collections (where the method exists). However there are some pitfalls. One such pitfall can be found when you try to write sub selects to link to other tables. For example, say you want to find all the individual relation records that have the surname “Zeidman” and are spouses. You would write a sub select in the custom where clause as shown below:

Dim inds As CIndividuals2 = Nothing
Dim ind As CIndividual2 = Nothing
Dim sql As String
Dim lastName As String = "Zeidman"

Dim list As New List(Of Integer)

Try

 sql = "IS_SPOUSE = - 1 AND  ID IN " & _
 "(SELECT     T_1.ID " & _
 "FROM          QUERY_IND_RELATIONSHIPS AS T_1 INNER JOIN " & _
 "RECORDS AS T_2 ON T_1.RELATION_ID = T_2.ID INNER JOIN " & _
 "CONSTIT_ADDRESS AS T_3 ON T_1.CONSTIT_ADDRESS_ID = T_3.ID INNER JOIN " & _
 "ADDRESS AS T_4 ON T_3.ADDRESS_ID = T_4.ID " & _
 "WHERE  (T_2.IS_CONSTITUENT = 0) AND  (T_2.LAST_NAME = '" & SQlEscape(lastName) & "') "

inds = New CIndividuals2
inds.Init(_ReInit.Session, lFilter:=TopViewFilter_Individual2.tvf_Ind2_CustomWhereClause, sCustomWhereClause:=sql, bReadOnly:=True)

For Each ind In inds

'Do something here
Next

'etc

The problem with this code is that when you run it you get the error:

Ambiguous column name ‘ID’

This is obvious when you think about it because this where clause is part of a bigger query. “id” being a primary key is used everywhere. So how do we know which aliases the bigger query uses? Well by using SQL Server Profiler we can see the full query. Quite simply you need to add “CONSTIT_RELATIONSHIPS” before that lone “ID”. What is interesting though is that the Records table is also aliased twice, once for the relationship target (i.e. the record of the constituent or non-constituent that is found in the “Surname” / “Last Name” field) – alias “C” – and once for the constituent where the relationship resides – alias “Records”. These are both very useful if you need to reference either of these tables in your where clause and saves a much larger subselect.