Hi Carly,
Take great care making changes to the TMS locations data in SQL. There are lots of tables involved and data in places you might not expect that serves various bits of functionality. If those pieces get out of line, you risk introducing problems with that functionality now and/or in upgrades. I highly recommend working with our Support team ([log in to unmask]) on any changes to locations data via SQL. For example, when you give an object a new Home location, there is not only an entry in Object Locations, but also an entry inserted to the Object Components table, which TMS then uses for “Return” transactions. When a location entry also involves a crate, there’s another level of issues to be investigated. In a locations scrubbing project, we also consider intellectual child relationships and co-located objects.
Each database is different and no script is one size fits all – especially in this area of functionality.
Now that I know you’ll be checking in with support before scrubbing, here’s a list of ID’s helpful when building reports on locations data.
TransCodeID:
1 Move temp
2 Move home
3 New Home
4 Inventory
5 Spot check
6 Random check
7 Historical entry
8 Scheduled move temp
9 Scheduled move home
10 Schedule move to a new home
TransStatusesID:
0 Pending
1 Completed
2 Not found
3 Found elsewhere
4 Cancelled
Looking forward to seeing you at the Collective Imagination conference!
All best,
Jeri
---
Jeri Moxley | Client Services Specialist
[log in to unmask] | 646.733.2239 x 235
GallerySystems | www.gallerysystems.com
Join us in NYC for
Collective Imagination 2016
The International TMS Users Conference
Conference: April 18 – 19, 2016
Workshops: April 20 – 21, 2016
Get more details here.
From: The Museum System (TMS) Users [mailto:[log in to unmask]] On Behalf Of Wickell, Carly
Sent: Thursday, February 25, 2016 9:35 PM
To: [log in to unmask]
Subject: Re: Un-setting home locations
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?
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
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.
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.
