Jeremy, With help from Kate Blanch at the Walters, [log in to unmask], we arrived at this TSQL that we run nightly to build a table for ResourceSpace to pull our metadata from when we embed the TMS objectID into the EXIF data for the images. We created a view and then used that view to build a table because the view performs much more slowly in the lookup process than a real table indexed by TMS objectID performs. 

 ----------------------------------------------------------------------------
----- Build reporting table tbl_wms_rsFeed for ResourceSpace TMS integration 
----- JFA 1/13/17 
----------------------------------------------------------------------------

BEGIN TRAN;
-------------------- create new view ----
drop view v_wms_rsFeed;
go

create view v_wms_rsFeed as   (
Select distinct vwc.id as [ObjectID]
,vwc.ObjectName
,vwc.AATSubject
,vwc.Title as [Titles]
,vwc.Medium
,vwc.CreditLine
,vwc.[Copyright Holder] as [Copyright]
,'Please call the Williams College Museum of Art (413) 597-2429' as [Restrictions]
,vwc.ObjectNumber
,vwc.Dated
,vwc.[Date Begin] as [ObjBeginDate]
,vwc.[Date End] as [ObjEndDate]
,vwc.Dimensions
,vwc.[Artist/Maker/Made by] as [Creators]
,checksum(*) as RowChecksum
from dbo.vWCObjectInfoListView vwc);
go

---------- make table tbl_wms_rsFeed ----------------
drop table tbl_wms_rsFeed;
go

select * into tbl_wms_rsFeed from v_wms_rsFeed;

-----------------------------------------------------
COMMIT TRAN;
----------------------------------------------------- 

I think you might want to modify the SQL something like this if you want to use the copyright field from the dbo.ObjRights table.

create view v_rsFeed4Briges as   (
Select distinct vwc.id as [ObjectID]
,vwc.ObjectName
,vwc.AATSubject
,vwc.Title as [Titles]
,vwc.Medium
,vwc.CreditLine
,obr.Copyright as [Copyright from ObjRights]
,'Please call the Williams College Museum of Art (413) 597-2429' as [Restrictions]
,vwc.ObjectNumber
,vwc.Dated
,vwc.[Date Begin] as [ObjBeginDate]
,vwc.[Date End] as [ObjEndDate]
,vwc.Dimensions
,vwc.[Artist/Maker/Made by] as [Creators]
,checksum(*) as RowChecksum
from dbo.vWCObjectInfoListView vwc
LEFT OUTER JOIN dbo.ObjRights obr on  vwc.id = obr.objectID);

I hope this helps and please let me know if you have any questions. 

Jim Allison




On Mon, Nov 20, 2017 at 12:03 PM, Jeremy Munro <[log in to unmask]> wrote:

Hi all,

 

We recently migrated to ResourceSpace for our DAMS which has a TMS plugin in order to populate metadata using ObjectID. However, it can only function on a singular table or a data view. The fields I want populated upon upload live on two tables – Objects and ObjRights. However, I also want the artist as well. I’m hoping to get around having to include the constituent tables by creating a new view off vgsrpObjTombstoneD_RO and vgsrpObjRightsS_RO, is this possible?

 

Regardless, I don’t have much experience with creating new views database side I was wondering if anyone could help me out with the syntax for adding the following fields to a new database view?

 

Artist

Title

Date

Medium

Dimensions

Object Number

Department

Credit Line

Copyright

Restrictions

Credit Line Repro

Thanks!

 

Jeremy Munro

Collections Information Coordinator

 

Crystal Bridges Museum of American Art

600 Museum Way

Bentonville, AR 72712

 

479.418.5710 (direct)

479.418.5700 (main)

CrystalBridges.org

 

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.