If you are interested in watching a video tutorial on batch editing, please see the HelpCast on Specify 6 Batch Editing!
Specify offers the ability to edit a 'batch' of data using an interface very similar to the Specify WorkBench. Batch Editing is a powerful method to improve data quality -- it can potentially change thousands of data records with just a few mouse clicks. Once edits are made and confirmed, changes will be permanent and they cannot be rolled back. We enthusiastically recommend you take the following precautions before a Batch Edit session:
- Make a fresh, full backup of your database.
- Ensure that you understand the relationships represented by the fields and tables you wish to change before using the Batch Editor.
- When creating queries to choose the data that will be displayed in the Batch Editor, create the query from the primary table, and include fields and tables related to the specific data you wish to edit.
- Once a batch edit has been completed, it is a good idea to view updated results of your query to confirm that the changes made to the data did not have unintended consequences in related fields and tables.
- It is best, when possible, not to edit multiple fields from multiple tables during a single Batch Editing session, but to limit the edits to fields within in a single table.
Additional notes regarding the Batch Editor:
- Only 7000 records that can be edited at one time. If you wish to edit more data you will need to split your data into batches of 7000 and edit each batch seperately. It may be useful to first split your data into saved Record Sets prior to starting the Batch Edit process.
- Batch editing is only available to users with an Admin, Manager, or Full Access user status who also have access to both the Query Builder and WorkBench tools.
- In this release, Taxonomy, Stratigraphy and Lithostratigraphy tree-structured information can only be viewed, and not edited in the Batch Editor tool.
- Fields that are read-only in the schema, such as unique identifiers (such as GUID or Catalog Number), formatted or aggregated can only be viewed, and not edited in the Batch Editor tool.
- When deleting values in Geography and Locality tree fields only fields without child data can be deleted, one can delete children in tree fields as long as there are no child records.
- When you bring data into the Batch Editor, you are essentially 'checking it out' from the database so that you may make changes that will later be validated and applied. If, by chance, other users are also accessing and editing the same data records, this can result in a different version of data in the database when your Batch Editing session is attempting to validate them. In this instance Specify will not commit the change and will alert you to this action in a dialog window.
- When more than one collection resides in a single database changes made in any of the shared tables will affect other collections. For instance, Locality information is shared at the Discipline level; therefore, changes made to Locality records used in more than one collection and changed in a single collection will reflected in all other collections within the same Discipline in the database.
The following documentation for the Batch Editor is intended as a basic overview. To better explain the changes that will be made to records in related tables when using the Batch Editing tool the Project has created a video which demonstrates these changes using real workflows. That video can be found on the Specify Collections Consortium website: sustain.specifysoftware.org/support/helpcasts
Choosing the Data to Edit
Open the Batch Editing tool by clicking the (Batch Edit) button on the Task Bar (at the top of the Specify screen).
To use the Batch Editor you must first choose the records you wish to edit. The side bar offers helpful options to get started:
- Create a new query by clicking the appropriate table in the Create Query section of the side bar.
The Create Query section of the side bar lists the tables that are available to batch edit.
Start by clicking the table that contains the data you wish to edit.
- Open a saved query by clicking the appropriate query in the Saved Queries section of the side bar.
If there is an existing query that will include the data you wish to batch edit choose it from the side bar.
- Choose a record set in the Record Set section of the side bar.
If there is a saved Record Set that you wish to batch edit from the query used to create it, or another saved query, right-mouse click on the record set and choose View with Saved Query from the pop-up dialog.
Note: When doing Batch Edits from a Collection Object query, Specify basically 'flattens' the data when bringing it into the batch editor. Therefore, changes made to fields in the batch editor will only affect that single Collection Object record. However, when creating queries from one of the other tables, the changes made will be reflected in all related records. The Batch Editing video explains this in more detail: sustain.specifysoftware.org/support/helpcasts
If you chose to create a query or open a saved query, you will notice that the Query Builder interface is displayed. Choose the tables and fields you wish to edit. Also, include related data that will allow you to determine the uniqueness of the records you are editing. For help using the Query Builder please Click Here.
Once you have completed your query you may wish to save it by clicking the Save button at the bottom of the Workspace and naming your query. This is useful when editing large sets of data that require multiple Batch Editing instances.
Otherwise, click the Search button at the bottom of the Workspace to display your query results. The data you see in the Query Results screen is the exact data that will be displayed in the Batch Editor.
Once you are satisfied that you have successfully chosen the data you wish to edit you can either bring all the data or a subset of the data into the Batch Editor.
- Click the (Batch Edit) button located at the top-right corner of the Search Results bar to display all the data into the Batch Editor.
- Limit the data by either clicking each record while holding down the Ctrl key, or clicking on a record and dragging your mouse to select multiple records. Then, click the (Batch Edit) button located at the top-right corner of the Search Results bar.
Your data are now available to manipulate in the Batch Editor tool. The Batch Editor interface is similar to the WorkBench, with the same features for manipulating data, such as; copy, paste, sort, fill up, fill down, etc. For more information about the tools available please Click Here.
Note: When deleting terms in the Batch Editor ensure that there are no spaces in the field.
Data Validation Panel
The Batch Editor also includes a Data Validation panel at the lower-left corner of the Workspace. Fields containing invalid data are highlighted in red and fields containing edited data are highlighted in green. The panel also displays a count of the fields affected in both categories. The adjacent triangles can be used to find the next affected data field.
Note: Changes made in the Batch Editor cannot be applied when invalid data are entered.
Note: Not all fields are available for editing in the Batch Editor. Columns of non-editable fields are grayed out.
If at any time during editing you wish to revert back to the original data, click the Undo All button located at the lower-right corner of the Workspace screen.
Once you have finished editing your data and are ready to commit your changes, click the Apply button located at the bottom-right of the Workspace screen.
Validate and Apply Your Edits
Specify will now validate your data, and display statistics about the changes you are about to make, but the Batch Editor has not committed those changes to the database. If necessary, you can click the Cancel button without losing your edits.
The Batch Progress dialog will display both alerts regarding the progress in validating the edits and results of the changes:
- Applying your edits is a progress statement.
- Validating changes to the database is a progress statement.
- Number of edits successfully applied and validated will display once the process is complete.
Typically the status will state that all edits were successfully applied; however, if a concurrent user edited one of the records while you were batch editing, Specify will recognize this and skip those records. If you wish to verify this with a co-worker you can click the Cancel button at the bottom of the dialog to cancel the process. The Batch Editor will still be displayed with all your previous changes and you can apply the changes again when you are ready.
- Number of Collection Objects affected by your changes
This message displays the total number of records that will be updated in each table that is affected by the batch edit. It is meant to help you understand the relationships to tables that are affected by your changes. If these numbers do not reflect the changes you believe you made we recommend that you cancel the update to discover the reason. It may be useful to select only a few records to load into the batch editor, make your edits, and then apply them in the Batch Editor. Once that is complete you can create queries on those changed records that allow you to better understand how the edits you made have affected related fields and tables, and whether you wish to proceed in making those changes to all your records.
- Whether your changes were saved or canceled.
A timer also appears on the Batch Process dialog, allowing you 180 seconds to either commit or cancel your changes. When you bring data into the Batch Editor, you are essentially 'checking it out' from the database so that you may make changes that will later be validated and applied. If other users are also accessing and editing the same data, it can result in a different version of data in the database than when the data was originally checked out. In this instance Specify will not commit the change and will alert you to this action in the status dialog.
Click OK to close the dialog.
Batch Edited Record Sets
Upon completion of a Batch Edit the records are saved in a new Record Set. If you saved the original query used to define the data you edited, then the resulting Record Set will use the same name as the query. If you did not save the query the Record Set will be saved with the name of the table used to create the query followed by a number that increments by 1 each time that the query was used for a Batch Edit. For example, if you created a Collecting Event query but did not save it, the ensuing Record Set would be named: New Collecting Event Query 1. You do not need to save these Record Sets, they are only meant to give you an opportunity to see the edited records at a later time. You may find it convenient to rename the record sets with the name or objective of the Batch or perhaps the date.
- View the changed records in the Record Set in Specify's data forms by left-clicking on the Record Set name in the sidebar.
- View the records in the Record Set in a spreadsheet format by right-clicking the Record Set name in the side bar and then choosing View with saved Query in the pop-up dialog.