The following View shows Contacts that have Files attached to them.

Field List

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]

View Clauses

File Attached

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 )

Default Image Attached

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)

Variations

Other Pages

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 )

Multiple Default Files

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))

Techniques

• EXISTS SQL Function
• STUFF SQL Function
• COUNT SQL Function

Other Resources

• Files system table