Looking for What Isn’t There

My coworkers seem to have an “infinite capacity to not know what can’t be done,” manifest in the myriad of queries they want to perform within TMS. Not satisfied with searching for records that meet a criteria, they’d rather look for records that don’t.

While you can use the Advanced Query to search for fields that are empty (Field ≠ *), you cannot search across table joins for records that do not exist.

• Search for Objects without any Constituents.
• Search for Objects without any Media.

Invariably, it is these searches that are the most useful in data clean-up and digitization projects. This little hack can make such functionality possible.

Planning & Configuration

For this example, we’re going to create a boolean value for objects that have a media record attached. In our database, the ObjContext.Flag15 is not used for any other projects and will hold our value. If you already use ObjContext.Flag15, then select another flag and adjusted these directions accordingly. In the Database Config tool, we will rename “Flag 15” to “Media Attached”.

Stored Procedure

Our flag will be updated with a Stored Procedure. The script will start by resetting the flag for every record to 0, and then proceed to update those records which do have a media record attached.

/*
Name:  Media_Toggle
Description:  Automate 'Media Attached' Flag Toggle
Author:  Chad Petrovay
*/

CREATE PROCEDURE Media_Toggle AS

/*Unset Flag15 for all objects */
UPDATE ObjContext SET Flag15 = 0

/*Set Flag15 for objects with an associated media record */
UPDATE ObjContext SET Flag15 = 1 WHERE ObjectID IN 
	(SELECT ID AS ObjectID FROM MediaXrefs WHERE TableID = 108)

Now that we’ve created the Stored Procedure, we can execute it. This will populate our flag with the appropriate value. These values will remain static until the next time to execute the Stored Procedure. For this reason, you’ll want to schedule a job in the management studio, to refresh this data on a regular basis (Mine is set to update once every 24 hours).

Tell the world

Now that your flag is populated, use the Database Config tool to add ObjContext.Flag15 to query groups, so that it is available in Query Assistant and Advanced Query. It will become the fastest way to identify those object records that both have and do not have an associated media record.