Contacts with Files Attached
The following View shows Contacts that have Files attached to them.
Difficulty: Advanced
Purpose: Uses the dp_Files system table to display Contacts with files attached.
Page: Contacts
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]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)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