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
> <https://maps.google.com/?q=600+Museum+Way%0D+Bentonville,+AR+72712%0D+%C2%A0%0D+479&entry=gmail&source=g>
>
> Bentonville, AR 72712
> <https://maps.google.com/?q=600+Museum+Way%0D+Bentonville,+AR+72712%0D+%C2%A0%0D+479&entry=gmail&source=g>
>
>
>
> 479.418.5710 <(479)%20418-5710> (direct)
>
> 479.418.5700 <(479)%20418-5700> (main)
>
> CrystalBridges.org <http://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.
|
