Hi,
Here is a similar SQL script that inserts flex field for objects in an Object Package.
insert into userfieldxrefs
(userfieldid, ID, COntextID, FieldValue, LoginID, entereddate, userfieldgroupID)
select 78, ObjectID, [contextID], [field value], [LoginID], GetDate(), -1
from Objects O Inner join PackageList PL on O.ObjectID = PL.ID and PL.TableID = 108
Inner join Packages P on PL.PackageID = P.PackageID
Where P.Name =[your package name]
Elizabeth
Elizabeth Concha, TMS Administrator
National Gallery of Art
(202) 842-6939
[log in to unmask]
From: The Museum System (TMS) Users <[log in to unmask]> On Behalf Of Mahdi, Fatima
Sent: Monday, July 01, 2019 3:43 PM
To: [log in to unmask]
Subject: Re: SQL for batch adding flex fields?
Wow! This is fabulous, Richard! It gets me really close to where I'm going!
Thanks so much!
Fatima
On Mon, Jul 1, 2019 at 3:23 PM Richard Grant <[log in to unmask]<mailto:[log in to unmask]>> wrote:
Here is a SQL statement that will insert a FlexField.
INSERT INTO UserFieldXrefs (UserFieldID, ID, ContextID, FieldValue, ValueRemarks, LoginID, UserFieldGroupID) SELECT [YOUR USER FIELD ID], Objects.ObjectID ,1, '[YOUR FIELD VALUE]', '[YOUR VALUE REMARKS]', '[YOUR LOGIN ID]' ,-1 FROM Objects WHERE ObjectNumber = 'YOUR OBJECT NUMBER'
In order to create inserts for a large group of Flex Fields, the best approach is to put the data into an EXCEL spreadsheet and use a CONCATENATE statement to create the SQL lines. Attached is an example of how to do it. Click on cell F1 to have the CONCATENATE statement show up in the data entry box. Call if you need help.
Richard Grant
Executive Director
3214 College Avenue
Berkeley, CA 94705
[log in to unmask]<mailto:[log in to unmask]>
510.428.1400 ext 4
510.459.4556 mobile
Instagram<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.instagram.com%2Fdiebenkornfoundation%2F&data=02%7C01%7CTMSUSERS%40SI-LISTSERV.SI.EDU%7Cedf5d1064b9844d6c45f08d6fe69abe7%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C636976127153209195&sdata=%2Fel7GAAOCYUdigDAtxLa%2FdhjATEQkAzmnhLipbxuFLQ%3D&reserved=0> | Twitter<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2Fdiebenkorn_fnd&data=02%7C01%7CTMSUSERS%40SI-LISTSERV.SI.EDU%7Cedf5d1064b9844d6c45f08d6fe69abe7%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C636976127153209195&sdata=5tsjl1CrN8ufL1egX36F1DGBoYlAp6c%2FTWXY9ClTzW4%3D&reserved=0>
[Richard Diebenkorn Fountation]<https://nam02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.diebenkorn.org%2F&data=02%7C01%7CTMSUSERS%40SI-LISTSERV.SI.EDU%7Cedf5d1064b9844d6c45f08d6fe69abe7%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C636976127153209195&sdata=21V5lpO70Bed4XrVhIc6liIZZAqGLNib7TPsAdGyVbU%3D&reserved=0>
On Mon, Jul 1, 2019 at 11:47 AM Mahdi, Fatima <[log in to unmask]<mailto:[log in to unmask]>> wrote:
Hello folks! Does anyone just happen to have a SQL statement for batch adding flex fields to a group of object records, say for example an object package?
Thanks!
--
Fatima Mahdi
Collections Information Specialist/TMS
Virginia Museum of Fine Arts
200 N. Boulevard
Richmond, VA 23220-4007
T 804.340.1443
[log in to unmask]<mailto:[log in to unmask]>
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.
--
Fatima Mahdi
Collections Information Specialist/TMS
Virginia Museum of Fine Arts
200 N. Boulevard
Richmond, VA 23220-4007
T 804.340.1443
[log in to unmask]<mailto:[log in to unmask]>
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.
|
