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.
* Rob Morgan
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]<mailto:[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]<mailto:[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 <https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.planningoffice.harvard.edu%2Fpirc&data=04%7C01%7CMorganR%40si.edu%7Ccb5d0dfc22ca4a3ec84508d976bbc1fd%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637671371071680833%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=5KzuIb5KipyJneLKpfAyGmY7EA3ldyt%2FC%2B6yajcFEjA%3D&reserved=0>
To unsubscribe, send an email to [log in to unmask]<mailto:[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]<mailto:[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.
|
