Batch Edit Works

Overview

This tool can only be used for batch editing work records and not item records. Catalog Items includes information on how to batch edit items. 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 field 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).

Getting Started

To edit works using the Batch Edit Tool, you’ll first need to make sure you are in the works search page, where all works are listed. To access the search page, select a work-enabled project, and select the Options menu then Search Works. A new 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.

Type a space and the tool then supplies a list of available field groups and nested fields.

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

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

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.

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.

Before you can select 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”);

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.

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:

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:

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:

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”:

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:

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:

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: