Bulk Actions

Bulk Actions, in a nutshell, provide the functionality of Automation 'Set Field', 'Create' and 'Delete' actions for massive amounts of records as quickly as possible. See Automation for more details on these actions.

In other words, Bulk Actions allow administrators to change, add or delete large amounts of records in the shortest time possible. When dealing with hundreds of thousands of records, the time Bulk Actions take can be in seconds rather than hours.

This feature is the equivalent of running SQL queries that affect multiple records at once, as opposed to the much slower changes on one record at a time via normal Automations that also have to pass through several ClayCentral features and layers. However, this differs from running SQL queries directly as follows:

• Changing data in Digital-Clay databases directly is not allowed or supported due to the fact that it bypasses ClayCentral. Not only would this bypass ClayCentral caching, and enforcement of various rules and features, but it may also cause very serious problems with synchronization, data integrity, unexpected values causing incorrect query results, etc.
• By running such changes through Bulk Actions, ClayCentral ensures that all changes will be performed correctly, taking into account system fields and data integrity.
• Bulk Actions are built using a point-and-click interface, allowing any user to build very complex queries in seconds without worrying about syntax, complex operations or table joins. This is made so simple, that even nested SQL queries that would often be split over several stages or temporary tables due to complexity or maintenance issues, are optimized.

That said, there is a price when performing changes this way instead of via normal Automations:

• In order to maximize performance, the following features are bypassed:
  • Automations. No Automation Events will be triggered for changes performed using Bulk Actions. These changes that would normally be performed using Automations can be run using additional Bulk Actions or manual runs of Automation Scheduled Events.
  • Due to the above, Automation Validation rules will also not be checked, so company-dependent data integrity issues must be handled within the Bulk Action itself using filters and appropriate Set Field actions.
  • Field Properties such as required fields or default values will also not be enforced. These must be handled by the Bulk Actions as well.
  • Security permissions will not be checked, but since Bulk Actions can only be run by an administrator, this should not be an issue.
  • Histories will not be saved. All values changed or added via Bulk Actions will not appear in the History tables.

• Due to synchronization issues with offline databases, Bulk Actions can only be run in one of the following scenarios:
  • On tables that are configured with “Disable offline use”.
  • They can be run manually by an administrator during which all users are disconnected from ClayCentral until the Bulk Actions are complete.
  • Offline after an import using ClayIntegrator. In this scenario, Bulk Actions are performed on the local offline tables and then synchronized with ClayCentral as usual when done.

• A one-time administrative change on existing records run manually by an administrator. For example, calculating default values for a new field just added to an existing table, or clearing all badly formatted telephone field values in thousands of records.

• A complex billing system that creates bills for thousands of customers based on hundreds of thousands of transactions once a month. These tables can be disabled for offline use and then a series of Bulk Actions can be called automatically from a scheduled Automation once a month.

• Mass transformations of new records. Thousands of records imported via ClayIntegrator can be normalized, and values can be transformed or new values calculated using complex functions before saving them at ClayCentral.

• Correcting a bad import by mass deleting all the records in a table quickly.

• Making partial copies of tables using Bulk Add. A Bulk Delete action can clear the table, before synchronizing the two tables using a Bulk Add action. The copy can include a subset of the records using filters, or only some of the fields for security or sharing purposes.

• Pre-aggregated values for analysis: The results of an analysis query can be saved into a table for comparison, warehousing, or faster analysis queries. This can sometimes serve as a simplified equivalent of an OLAP database for faster querying on millions of records.

• Bulk Change: This changes field values using almost any of the Set Field Automation actions including setting a field using a custom function, a linked value, or using the results of a query. Note that using nested queries (all 'Set Field Query' actions) in Bulk Actions is not supported when the ClayCentral database is MDB.
  
  Note that a Bulk Change can have multiple Set Field actions inside it, each one run as a separate SQL query. Also, the Set Field actions can change fields in other, linked tables just like with Automation actions, in which case the Bulk Change filters would apply to the base table of the Bulk Action but the Set Field would change a field in a linked table. For example, set the Customer:Region to USA where Contact:Country=USA.

• Bulk Sequential Change: This special action is the same as a Bulk Change, except it performs changes on one record at a time for all records that match the filters. This is still much faster than running normal Automations however since other ClayCentral features are still bypassed. This is useful when changes must be performed in a sequential order. Example: Assign leads based on rules, but every time a lead is assigned, a counter is increased for an employee, so the next lead will be assigned to a different user based on current counter values.

• Bulk Delete: Delete all records in the base table matching filter criteria.

• Bulk Add: Add multiple records at once based on all of the results of a browser query. Columns in the query results are mapped to fields in the base table. Using this action, records from one table can be copied to another, using a selected list of fields (as well as calculated custom function values), and a subset (filtered) list of records.

• Bulk Add (Analysis): The same as the above except the query performed is an aggregated analysis query. Results of an analysis query on current values may thus be stored in another table for future reference or faster querying.

Bulk Actions can be run via any of the following features:

• Manually: In the ClayStudio/Administration/Bulk Actions menu item, choose one or more Bulk Actions to run and click on the Run button. If any of the Bulk Actions run on tables that are not disabled for offline use, you will be warned that users will be disconnected.

• Via Automations using the 'Run Bulk Actions' action: This includes any Automation Events including Item Events like Item Change, or Scheduled Events. If the Bulk Actions run on tables that are not disabled for offline use, this will result in an automation error in the Automation Log. In order to link Bulk Actions to the Automation that called them, the following options are available:
  • A filter in the Bulk Action can be mapped to the current Automation object being handled. For example, an Automation Event Handler that handles changes to the Customer table can call a Bulk Action that runs on the Contacts table. The Bulk Action could then have a filter on the Customer table, and this Customer filter would then be selected as the 'Relation to subject' when selecting the Bulk Action in the 'Run Bulk Action' form.
  • Using a 'Set Linked Field' action, the records being changed could be linked to the item currently being handled by the Automation. This is done by selecting '<System Event Item>' in the drop down. For example, all 'Form' records could be temporarily linked to the current 'Employee' record that caused the Automation Event Handler to run.

• After an import using ClayIntegrator: As part of an import session, Bulk Actions may be selected to run as soon as the import is finished but before the records are synchronized with ClayCentral. This is very useful for performing many or complex transformations on imported records. By default, the date range of the import is added as a filter to all Bulk Actions in order to restrict them to the records that were imported during that session only, but this is optional.

Note that Bulk Actions are always run in the order of their priority values, and that they only run if their status is set to 'Active', allowing administrators to temporarily disable Bulk Actions even if they are configured to run automatically in Automations or import sessions.

Using ClayIntegrator, massive amounts of records can be imported in a shorter amount of time (hundreds, or thousands of records per second depending on several factors) using the following procedure:

1. Make sure you run the import as an administrator.
2. Configure the tables you are importing into as “disabled for offline use” using the Custom Tables tool.
3. Map the source table to the online-only Digital-Clay table as usual and import as usual. You should get a message box informing you that this will be a bulk import.

Note that importing data this way can only be used for adding records, not updating/merging records. Also note that the records will bypass all automations, validation rules, field properties and history auditing for performance reasons. This method of importing is designed to get massive amounts of new data into the system as fast as possible.