The following View shows Contacts that have Files attached to them.
Leave this blank to use the default fields. Alternatively, you can add any of the following fields to show the file count, file names or descriptions. The highlighted portion shows the clause that is Page-specific for this type of view:
, (SELECT COUNT(*) FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID ) AS [File Count] , STUFF((SELECT ', ' + F.File_Name FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID FOR XML PATH('')), 1, 2, '') AS [File Names] , STUFF((SELECT ', ' + F.Summary FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID AND F.Summary IS NOT NULL AND F.Summary <> '' FOR XML PATH('')), 1, 2, '') AS [Descriptions] ,(SELECT TOP 1 UTC_Date_Added FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID ORDER BY UTC_Date_Added DESC) AS [Last File Added]
The following clause limits the list of Contacts to those with files attached. Contacts without any files attached will drop out of the View:
EXISTS (SELECT 1 FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID )
The following clause limits the list of Contacts to those with a default image:
EXISTS (SELECT 1 FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID AND F.Default_Image=1)
To use the same technique on Pages other than Contacts, replace the highlighted clauses with the Table Name and ID field:
EXISTS (SELECT 1 FROM dp_Files AS F WHERE F.Table_Name='Participants' AND F.Record_ID = Participants.Participant_ID )
To only show Contacts with Duplicate default files replace the View Clause with the following:
(1 < (SELECT COUNT(*) FROM dp_Files AS F WHERE F.Table_Name = 'Contacts' AND F.Record_ID = Contacts.Contact_ID AND F.Default_Image=1))