Whether you are launching a data cleanup project or simply want to verify that standards are being followed, there are times you want to be able to quickly and easily manage your data. In 5 (Often Forgotten) Productivity Tools I mentioned that List Views are a great way to review data. In the first part of this tutorial, we’ll design a List Views to help with a project to clean-up accessioning information.

SQL View

Firstly, we need to create a new view in the database for our List View to be based upon.

/* VIEW OF OBJECT ACCESSIONING DATA */
CREATE VIEW accessionListView AS

SELECT
	O.ObjectID AS ID,
	O.ObjectNumber,
	O.SortNumber,
	AltNumber = ( SELECT TOP 1 CASE
		WHEN AN.Description = 'Preaccession ID' THEN AN.AltNum 
		WHEN AN.Description = 'Previous Number' THEN AN.AltNum ELSE	NULL END	
		FROM AltNums AN WHERE AN.ID = O.ObjectID AND AN.TableID = 108 ),
	AltNumQty = ( SELECT Count(*) FROM AltNums AN WHERE AN.ID = O.ObjectID AND TableID = 108 ),
	OS.ObjectStatus,
	OA.ApprovalISODate1,
	OA.AccessionISODate,
	AM.AccessionMethod,
	OA.DeedofGiftSentISO,
	OA.DeedofGiftReceivedISO

FROM Objects O
LEFT OUTER JOIN ObjectStatuses OS ON O.ObjectStatusID = OS.ObjectStatusID
LEFT OUTER JOIN ObjAccession OA ON O.ObjectID = OA.ObjectID
LEFT OUTER JOIN AccessionMethods AM ON OA.AccessionMethodID = AM.AccessionMethodID

Once you have executed the script, remember to add the appropriate permissions for your usergroups, so that this can be accessed by The Museum System.

List View Designer

If you don’t mind querying the database in SQL Management Studio, then feel free to quit here. Otherwise, we’re going to create a new List View in List View Designer. This process is fairly straight-forward, and well documented in the manual, and the image below should give you a good idea of how to configure the new List View.

Since this project will have relatively few staff working on it, I would assign the List View to users associated with the cleanup, instead of making it Global. Once this is available, users will be able to examine selections from the collection, sort on different fields, and assess what requires a good scrubbing.

In the next installment, we’ll look at a sophisticated SQL script for efficiently scrubbing the data clean.