Download PDF

On this page…
Overview
Getting Started
How to Use the Batch Edit Tool
Insert
Update
Delete


Overview

The Batch Edit Tool can be used to update, add, and delete data from batches of Work Records.

Only a subset* of the fields in the work form can be edited using the Batch Edit Tool. The following table lists the fields and subfields in the Work Record that can be batch edited. Field names in bold under the “Field” column indicate nested fields within the associated Fields Group.

*The subset of fields available in the Batch Edit Tool are comprised of the fields in the Work Record Short Form (minus all display fields except Dates Display and Measurements Display and the fields in the local info tab).

Field Group Field SubField
titles title  
  type  
  languages language
    qualifier
    script
    transliteration
agents agent  
  attribution  
  role  
cultures culture  
artstorCountries country  
places place  
  type  
  address  
  latitude  
  longitude  
  elevation  
repositories repository  
  type  
  creditLine  
  refid id
    type
  location  
  address  
  latitude  
  longitude  
  elevation  
privateOwners name  
  type  
  location  
  address  
  latitude  
  longitude  
  elevation  
dates display  
  type  
  startDate  
  endDate  
workTypes workType  
classifications classification  
artstorClassifications artstorClassification  
stylePeriods stylePeriod  
materials material  
  type 
techniques technique  
measurements display  
  measurement type
    unit
    value
inscriptions position  
  author  
  inscription inscription
    language
    script
    type
stateEditions name  
  type  
  number  
  count  
subjects subject  
  type  
descriptions description  
textReferences textRef  
  type  
  refidType  
notes type  
  entry  
  content  
bibliographies bibliography  
  sourceName  
  sourceNameTypeLkup  
  sourceDetail sourceDetail
    type
rights content  
  type  
localInformation legacyID  
  originalCataloger  
  catalogStartDate  
  modifiedCatalogers  
  modifiedCatalogersDate  
  approver  
  approvedDate  
  catalogLevel  
  published  
  relationship  
  relationType  
  description  
  link   

Getting Started

To edit Work Records using the Batch Edit Tool, you’ll first need to make sure you are in the Work Record search page, where all Work Records are listed. To access the search page from Shared Shelf, select a project that links to Work Records, and click the upper right corner “gear” icon, where you’ll see the “Search Works” option in the dropdown.

Search Works example

A new Shared Shelf “Search Works” tab will open, where you can search for the work records you’d like to edit. Using the Control, Shift and Enter keys, select the records you wish to edit and click on the “Edit Works” button.

How to Use the Batch Edit Tool

The Batch Edit Tool includes a single text box for typing your edit command statement. Through an automated series of steps, the tool helps you formulate your command by supplying syntax-appropriate values in drop down lists as you type.  

To begin, type into the text box one of the following three commands:  INSERT, UPDATE, or DELETE.

  1. INSERT - insert a new row, or new nested row
  2. UPDATE - add, edit, or remove a value in an existing row or nested field row
  3. DELETE - delete an existing row, or nested row

From the dropdown list, select the appropriate command.  The command font will turn dark blue when properly formed or selected, indicating you can move to the next step.

3. UPDATE command

 

Type a space and the tool then supplies a list of available Fields Groups and nested fields.  

Important: Nested fields appear as concatenated values (field group name concatenated with the field name separated by a period, e.g. repositories.refid).

4. Select field to update

 

After choosing a field group, the tool prompts you to SET the field or subfield from the dropdown list that you wish to edit.

5. SET clause

 

After selecting the appropriate field, you’ll need to manually enter the “=” operator, and then enclose in double quotes the string or value you wish to insert, update, or delete, followed by a close paren.  

Example: UPDATE “titles” SET (“titles.title” = “La Gioconda”)

The tool will prompt you to include a WHERE clause, which is optional for UPDATE and INSERT commands.

6. WHERE clause

 

When including a WHERE clause, the tool will prompt you to select an appropriate field to filter on. After selecting, you’ll again, need to manually enter an “=” operator and a condition string consisting of the phrase or value to edit enclosed in double quotes and a close paren.

7. WHERE clause filtering

 

Before you can click OK, you must type a semicolon at the end of your command after the final close paren to finish your statement. Without the semicolon, the OK button will remain inactive and you will not be allowed to run your command.

Example: UPDATE “titles” SET (“titles.title” = “La Gioconda”) WHERE (“titles.title” = “Mona Lisa”);

8. Semicolon

 

Note:

  • You can use any combination of mouse clicks, arrow keys, and ENTER to select commands, field groups, fields, and subfields while forming your statement.
  • Command syntax for DELETE (only), must be entered in all UPPERCASE letters for the tool to function correctly.
  • You may only select a single field or subfield at a time to insert, edit, or delete.
  • You cannot string together multiple clauses or statements using the AND operator.
  • NOT LIKE and NOT EQUAL TO are not available when filtering in the WHERE clause.
  • All strings, including dates and other numeric values, must be enclosed in double quotes.
  • Controlled list term IDs must never be enclosed in quotes.
  • Local list terms must be added to the appropriate controlled list and assigned a unique ID before you can use them in your statement.
  • When INSERT or UPDATE values contain quotes, they must be entered as single quotes within the mandatory double quotes.
  • The “=” operator must always be padded with spaces on either side.
  • Wildcards are not functional in the WHERE clause.
  • Phrases and values in the WHERE clause are not case sensitive.
  • The CLEAR operator can be used in the WHERE clause to filter on NULL or blank fields.
  • The CLEAR command can be used in the SET clause to delete existing values.
  • All clauses beginning with an open paren must end with a close paren.
  • You must end your statement with a semicolon before you can run it.
  • The OK button will remain inactive until your statement is properly formed.

 

Insert

The INSERT command is used to add a new row into a specified field group. Once you’ve successfully inserted a row, you may use UPDATE to add, edit, or delete any additional information in that row. The exception for INSERT is adding a phrase or value into a new row of nested Field Groups.

NESTED FIELD GROUPS

If you are adding a value into an unpopulated, nested field row (i.e. titles.languages), you’d use the INSERT command to add the row, and then use the UPDATE command to add, edit, and delete information in the nested row.  You would use the DELETE command to delete an entire nested row.

SET CLAUSE

The SET clause in a statement is where one indicates a specific field to be processed, and also the value or phrase to add, edit, or delete from it. When used without a WHERE clause, the statement will be processed on all Work Records selected for editing.

WHERE CLAUSE

The WHERE clause in a statement is where one can optionally indicate a specific field and a conditional value or phrase to be searched before a command can be processed. This will result in only certain Work Records to be edited among the batch selection. The WHERE clause is not mandatory in INSERT or UPDATE statements, but must always be included when using the DELETE command.

CONTROLLED TERMS

When using controlled terms in your syntax, the tool automatically replaces the typed term with its unique ID when the term is selected from a controlled list. In the following conditional insert statements, the title language term ID 1595590 = “English” and term ID 1595280 = “Italian”.  To function properly, term IDs should not be enclosed in quotes. In addition, all local terms must be added to the appropriate controlled list and assigned a unique ID before you can use them in your Edit Work Record statements.

INSERT (unconditional insert):

INSERT “titles” SET (“titles.title” = “La Gioconda”);

INSERT “titles” SET (“titles.title” = “La Joconde”);

INSERT (conditional insert / nested fields):

INSERT “titles.languages” SET (“titles.languages.language” = 1595590) WHERE (“titles.title” = “Mona Lisa”);

INSERT “titles.languages” SET (“titles.languages.language” = 1595280) WHERE (“titles.title” = “La Gioconda”);

The above four INSERT statements (conditional and unconditional) will result in 2 new rows and 2 new nested fields being inserted as follows into the Titles field group in all selected Work Records.

  Title Language
[existing title] Mona Lisa English
[new row] La Gioconda Italian
[new row] La Joconde   

Update

The UPDATE command is used to add, edit, or delete data from an existing row.  

UPDATE (Add / Edit)

You can add or edit an existing value in a field in a specific row by including a WHERE clause.

UPDATE single row:

UPDATE “titles” SET (“titles.type” = 1599187) WHERE (“titles.title” = “La Gioconda”);

In this example, titles.type term ID 1599187 = “Alternate” and will result in the following update in all selected Work Records which meet this condition:

Title Type Language
Mona Lisa   English
La Gioconda Alternate Italian
La Joconde     


CLEAR (used as an operator in the WHERE clause)

Using the CLEAR operator as a condition in the WHERE clause instructs the tool to update only those records where the specified field is NULL or blank. Be aware when using the CLEAR operator, all selected Work Records where the specified field is NULL will be updated. You should be very thoughtful and careful when using this syntax. At this time, the CLEAR condition cannot be used on nested field groups (i.e. titles.languages, etc.). It can only be used on unnested fields.

UPDATE single row where null or no value exists using CLEAR:

UPDATE “titles” SET (“titles.type” = 1599167) WHERE (“titles.type” = CLEAR);

In this example, titles.type list ID 1599167 = “Undetermined” and will result in the following updates to all selected Work Records which meet this condition:

Title Type Language
Mona Lisa Undetermined English
La Gioconda Alternate Italian
La Joconde Undetermined   

You can use the CLEAR operator in the WHERE clause to update all rows with a uniform value where a specified field is NULL or blank.

UPDATE multiple rows where null or no value exists using CLEAR:

UPDATE “titles” SET (“titles.type” = 1599172) WHERE (“titles.type” = CLEAR);

You can also use UPDATE without setting a WHERE condition. This will result in all fields, null and not null, in all selected Work Records to be updated to the value in the SET clause.

UPDATE field in all records with SET value:

UPDATE “titles” SET (“titles.type” = 1599172);

In this example, titles.type list ID 1599172 = “Constructed” and will result in the following updates to all selected Work Records:

Title Type Language
Mona Lisa Constructed English
La Gioconda Constructed Italian
La Joconde Constructed   


UPDATE (Delete)

To delete or “clear” a value from a specific field in a row using the UPDATE command, the CLEAR command is used within the SET clause.

Important: To delete an entire row or nested row, the DELETE command is used. See DELETE for more information.

CLEAR (used as a command in the SET clause)

Using the CLEAR command in the SET clause instructs the tool to delete a value in a specific field. CLEAR can be used in the SET clause to delete values in specific fields from all selected Work Records, or can be used in conjunction with a WHERE clause to delete values from only specific Work Records which meet the criteria. Again, you should be very thoughtful and careful when using this syntax.

UPDATE (conditional delete) to delete a value from a specific field or row:

UPDATE “titles” SET (“titles.type” = CLEAR) WHERE (“titles.title” = “La Gioconda”);

In this example, the title.type will only be deleted where the title in the selected Work Records = “La Gioconda”:

Title Type Language
Mona Lisa Constructed English
La Gioconda   Italian
La Joconde Constructed   

You can also use UPDATE to delete a value from a field across all selected Work Records without setting a WHERE condition.

UPDATE (unconditional delete) to delete all values in all rows:

UPDATE “titles” SET (“titles.type” = CLEAR);

In this example, the title.type value in all rows in all selected Work Records will be deleted:

Title Type Language
Mona Lisa   English
La Gioconda   Italian
La Joconde     


BOOLEAN FIELD TYPES

Boolean fields generally exist in the form of checkboxes or radio buttons, which are set to TRUE or FALSE (true = yes/checked, false = no/unchecked). In the Work Record Short Form, only a single Boolean field (“Published” on the Local Info tab) is available for updating. Though this field has no impact on the actual publishing status of a record, it can be used for internal workflows. When updating this field using the Batch Edit Tool, you must set the value to “true” or “false” as follows:

UPDATE “localIinformation” SET (“localIinformation.published” = “true”);

UPDATE “localIinformation” SET (“localIinformation.published” = “false”);

Delete

The DELETE command is used to delete an entire row from a specified field group, or to delete a nested field row. DELETE statements always include a WHERE clause to filter out Work Records that should not be affected. For security reasons, it is not possible to create a DELETE statement without including a WHERE clause.

Delete a specific row:

DELETE “titles” WHERE (“titles.title” = “La Joconde“);

In this example, each title row where “La Joconde” was entered will be deleted from all selected Work Records:

Title Type Language
Mona Lisa   English
La Gioconda   Italian


Delete a nested row:

DELETE “titles.languages” WHERE (“titles.languages.language” = 1595590);

In this example, title.language term ID 1595590 = “English”, and will result in the following nested row to be deleted from all selected Work Records:

Title Type Language
Mona Lisa    
La Gioconda   Italian