Also, make sure to run the insert script in a test database first!

 

From: The Museum System (TMS) Users <[log in to unmask]> On Behalf Of Morgan, Rob
Sent: Monday, September 13, 2021 9:38 AM
To: ListServ tmsusers <[log in to unmask]>
Subject: Re: Inserting Flex Field Groups using SQL

 

Hello Alison,

 

Below is a script I used to insert a flex field that is in a flex field group.

 

For your database, you’ll need to find out your UserFieldID, or UserFieldIDs, and ContextID for Events.

 

In addition, the ValueDate, FieldValue, ValueRemarks, LoginID, and UserFieldGroupID will be different for your insert statement, too.

 

One thing you could do, is manually enter the flex field group data into one test record, then query select * from userfieldxrefs order by entereddate desc in SSMS (SQL) to see how the data is entered into the UserFieldXrefs table (this script will return the most recently entered data, first).  And then base your insert statement off the most recently entered data into UserFieldXrefs.

 

Let me know if you have any questions.

 

IT Specialist, Smithsonian

 

P.S.  This script inserts data into a group of objects that are in a object package (in this case, PackageID 127288.  Our TMS databases have a view entitled ObjPkgList.  Let me know if you need the create script for this view).

 

Insert into UserfieldXrefs

(

UserfieldID

, ID

, ContextID

, ValueDate

, FieldValue

, ValueRemarks

, LoginID

, EnteredDate

, UserfieldGroupID

)

Select 96, objects.ObjectID, 1, '2020-10-23', 'B.03. Sacred and/or Ceremonial Objects', NULL, 'MorganR', GetDate (), -1

from objects

inner join objpkglist

on objects.objectid = objpkglist.objectid

where objpkglist.objectpackageid = 127288 

 

 

From: The Museum System (TMS) Users <[log in to unmask]> On Behalf Of Anderson, Alison M
Sent: Monday, September 13, 2021 9:24 AM
To: ListServ tmsusers <[log in to unmask]>
Subject: Inserting Flex Field Groups using SQL

 

External Email - Exercise Caution

Hello,

 

Has anyone inserted Flex Field Groups into TMS for a large set of records using SQL? I am looking to insert 4 FF groups (each containing between 5 and 17 fields, for a total of 32 individual fields) into roughly 2500 Events records and am having difficulty with it. If you have done this successfully, would you mind sharing the SQL insert script?

 

TIA!

 

Sincerely,

Alison

 

__________________________________

Alison Anderson, MSLIS, DAS, CA (she/her)

Senior Processing Archivist

Harvard Planning Office

Property Information Resource Center

1350 Massachusetts Avenue, Suite 585

Cambridge, MA 02138

T 617-495-4353  F 617-495-0559

www.planningoffice.harvard.edu/pirc

 

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.