What would you like to know more about?

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