This View example displays Contact records that are associated with any Household records in a Selection of Households.
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.
The Selection must be a selection on Contacts created by the logged in User, and be the Current/Unsaved Selection.
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')
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
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)
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