This View example displays Contact records that are associated with any Household records in a Selection of Households.

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

On the Contacts page, create a new View, and paste the code into the Filter Clause. Leave Field List empty.

How to Use

1. Create a Selection of Households on the Households page.
2. Navigate to Contacts.
3. Select this View from the list.

Limits

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

Variations

Named Selections

For use with other Selections, including Selections created with the XFer 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

Add this to the Filter Clause to limit the list to show only Contacts that have a Minor Child Household Position:

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