Hi Scott! I am reviving this post in hopes that you could clarify something for me. I am looking for a SQL solution to update the Location Trans Type from 'new home' to 'move temp' in order to preserve the location history - is this what your SQL did? Or did it completely delete that location from the object history? [image: Inline image 1] I have a SQL to find all of the objects that have a home location assigned, but so far have only found ways to delete that location in its entirety versus just changing the type of location it is. I feel like this should be super easy, but I am hitting a couple roadblocks with finding the correct TransTypeID etc. I would love it if you (or anyone out in TMSLand) could offer ideas on this. Thanks in advance for any help! Best, Carly *CARLY WICKELL* *Senior Database Specialist* *WWW.LACMA.ORG* <http://WWW.LACMA.ORG> *T 3238576268* *E [log in to unmask] <[log in to unmask]>* On Thu, Jul 31, 2014 at 7:05 AM, Williams, Scott < [log in to unmask]> wrote: > Hi all — > > The issue of CrateType still showing in Home Location column in the GUI > after the HomeCrateID value has been zeroed out in ObjComponents table has > been confirmed by Gallery Systems as a bug affecting both 2010 and 2012. It > has been logged for a fix in a future release. > > Thanks, > Scott > > > > From: <Williams>, Scott Williams <[log in to unmask]> > Reply-To: "The Museum System (TMS) Users" <[log in to unmask]> > Date: Thursday, May 15, 2014 at 4:33 PM > To: "[log in to unmask]" <[log in to unmask]> > Subject: Un-setting home locations > > Hi all — > > We need to un-set the Home Locations for a group of objects without > changing their current locations. I have an idea about how to go about this > but was curious if other institutions have run into this problem or if > there might be another way to do it. The most obvious method AFAIK would be > to zero out the Home locations columns in ObjComponents. > > UPDATE oc > SET oc.HomeCrateID = NULL, > oc.HomeLevel = NULL, > oc.HomeLocationID = -1, > oc.HomeSublevel = NULL, > oc.SearchHomeContainer = NULL > FROM ObjComponents oc > WHERE /* whatever */ > > The alternative to this would be move the objects, via SQL, (I do not > believe you can update the Home Location without changing the Current > Location) to a “Virtual Home” location. This "Virtual Home” is simply a > place holder that should never be used as a Current Location which means we > would then have to write a notification job that would alert us if any > object that were accidentally “returned to home” to the Virtual Home. This > isn’t a huge problem but it is one more daily notification that would need > to be tracked and managed by collections staff. > > The major issue I have run into while testing the first option is the > Crate Type text not being cleared from the Home Location column after the > ObjComponents home location data was zeroed out. For example, if I run the > above SQL on an object whose Home Location includes a Crate then what we > see after the update is this: > > > And I cannot figure out why or how the Crate Type value is still being > populated into the Home Location column. Has anyone ever needed to unset > Home Locations? How did you go about doing it? > > We are running TMS 2010 and SQL Server 2008. > > Thanks, > Scott > > To unsubscribe, send an email to [log in to unmask] with the > following commands in the body of the email: > > signoff TMSUSERS > > // eoj > > You will receive a confirmation that your subscription has been removed. > To unsubscribe, send an email to [log in to unmask] with the > following commands in the body of the email: > > signoff TMSUSERS > > // eoj > > You will receive a confirmation that your subscription has been removed. > To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email: signoff TMSUSERS // eoj You will receive a confirmation that your subscription has been removed.