Contacts in Household Page Default Selection

To create a View that lists all the Contacts in the Current/Unsaved Selection on the Households page, the following SQL may be used for the criteria:

Contacts.Household_ID IN 
(SELECT Record_ID FROM dp_Selections S JOIN dp_Selected_Records SR      
	ON S.Selection_ID = SR.Selection_ID      
	WHERE S.User_ID = dp_UserID AND S.Selection_Name = 'dp_DEFAULT' AND S.Page_ID =           
		(SELECT TOP 1 P.Page_ID FROM dp_Pages P            
			WHERE P.Table_Name = 'Households' AND P.Filter_Clause IS NULL))

Quick Creation

Open Advanced Search in Contacts and paste the code into the Filter Clause. Leave the Field List empty.

Limits

The Selection must be a selection on Contacts created by the logged in User, and be the Current/Unsaved Selection.

Named Selections

For use with other selections, including selections created with the Transfer Selection button or Transfer Selection Tool, you can change dp_Default to the name of the selection. Make sure it is in single quotes:

Selection_Name = 'Name 1'

To use a list of named selections (the View will show all records matching all the Selections), use the IN comparison:

Selection_Name IN ('Name 1', 'Name 2', 'Name 3')

Other User's Selections

If a User other than the logged in User will generate the Selection, you can change dp_UserID to the ID of the User who will maintain the Selection:

User_ID = 108

Minor Children in Selected Households

AND Contacts.Household_Position_ID = 2

To use a list of Minor Household Positions (the View will show all records matching all the Selections), use the IN comparison:

AND Contacts.Household_Position_ID IN (2,5)

Selection Based on Page ID

The initial example looks up the Households Page based on the name of the Page. Alternatively, you can specify exactly what Page your selection is on (this could be used if you have a Households Filtered page, for example). Note that the S.Page_ID = 327 refers to the Page ID of the Households Page on your system. This may very between systems. Confirm your Household Page's ID and modify the code as necessary.

Contacts.Household_ID IN 
	(SELECT Record_ID FROM dp_Selections S JOIN dp_Selected_Records SR      
		ON S.Selection_ID = SR.Selection_ID      
		WHERE S.User_ID = dp_UserID AND S.Page_ID = 327             
			AND Sub_Page_ID IS NULL AND S.Selection_Name = 'dp_DEFAULT' )

Techniques

• ISNULL SQL Function
• Subqueries
• ID IN SELECT