Bulk Update API

Introduction

Imagine you have a selection of contacts where the company category is set to “Customer”, and for some reason you need to uncheck the Stop checkbox on each one. Assuming there are hundreds of companies that need to be changed, this could potentially take a user some hours to complete.

Thankfully with SuperOffice 8.1, the Windows client ( & Web Client v8.3) has a new feature that allows users with the proper functional right to select one or more rows in an archive control and update one or more of their fields in a single bulk update. 

In Figure One a company’s list of contacts archive is shown, with several people selected and the context menu displayed. The menu shows the new Bulk Update option near the bottom.

Sticking with the original idea to update a list of companes Stop checkbox, Figure Two displays the bulk update dialog which allows a user to select the Company Stop property and Set it to No. When the Update button is clicked, a background job is created that iterates over each selected company and to Set the Stop property to No, leaving it unchecked in the SuperOffice UI.

Figure One: Selection Results

Figure Two: Bulk update dialog.

For Windows clients, there is a switch to enable a more advanced view of blulk update operation results. Add this setting to your souser.ini:

[BulkUpdate]
ResultsArchive=1

This will open the dialog with a resultsview giving you running feedback while the job is running.

While this is very interesting for SuperOffice Windows client users, how do we perform the same action in code?

NetServer contains a new namespace called SuperOffice.CRM.BulkUpdate, which is where all the new bulk update functionality resides.

In this article, I will discuss Bulk Update, explain the new types and how to work with them, and show in code how your applications can also begin to leverage this powerful functionality.

First, there are a few practical details to know before we will move on to the code.

Practical Details

To use Bulk Update, a user be assigned a role that contains the functional right “Can bulk update”, or allow-bulk-edit. By default, only the roles User level 0 and User level 1 are assigned this functional right.

This is a powerful tool that can cause a lot of damage if used improperly, and therefore should only be giving to advanced users.

When a role is assigned the allow-bulk-update functional right, any member of that role will see the Bulk Update functionality in the SuperOffice client. The same is true to use the API, specifically for Adding interests which requires the additional allow-bulk-interest functional right. If the principal executing the code is not a member of that role, the field will not be available.

When it comes to using the bulk update API, there are three aspects to its orchestration:

1. Define one or more entity identities to update.
2. Define one or more fields of that entity to update.
3. Define the action performed on each field.

Figure Three: Bulk update orchestration.

Once the orchestration is defined, and the update is executed, it’s up to the API consumer to determine how the action results are monitors and or recorded.

There is a preference that determines whether to log the update or not.

In the Windows client, a user can click a button to view the results displayed in Figure Four.

Figure Four: Bulk Update Log.

The preference that enables the Bulk Update log is in the BulkUpdate section, has a “LogResultOfJob” prefkey, and has a prefvalue that is set to either true or false.

Practical Code Example

Below is a basic example that searches for all persons with the title property equal to ‘Admin’ and changes each to ‘Administrator’.

The preference that enables the Bulk Update log is in the BulkUpdate section, has a “LogResultOfJob” prefkey, and has a prefvalue that is set to either true or false.

// search for all persons with title ‘Admin’
var selectPeople = S.NewSelect<PersonTableInfo>(pti => pti.Title.Equal("Admin"));

// get all personIds from the search results 
var personIds = selectPeople.Records(selectPeople.Table.PersonId).Select(
                     r => new { PersonId = r.Table.PersonId[r] });

if (personIds != null)
{

    // use BulkUpdateSystem.GetAvailablePersonFields to get the Title FieldValueInfo

    var personTitleFieldValueInfo = BulkUpdateSystem.GetAvailablePersonFields()
            .Where(v => v.Key == BulkUpdateSystem.PersonFieldValueKeys.Title)
            .FirstOrDefault();

    // use a set operation to update the field
    personTitleFieldValueInfo.CurrentOperationType = BulkUpdateSystem.OperationTypes.Set;

    // define the new value
    personTitleFieldValueInfo.Values = new[] { "Administrator" };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.PersonTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { personTitleFieldValueInfo },

    // set the person Ids that will be updated
    personIds.Select(p => p.PersonId).ToArray());

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }
}

The first two lines of code execute a NetServer OSQL query that gets all personIds where the person Title is equal to ‘Admin’. Next, it Checks to make sure the result is not null, indicating at least one person was returned by the query.

BulkUpdateSystem is a class that contains several useful methods for fetching all relevant FieldValueInfos supported by an entity.

The example above uses BulkUpdateSystem.GetAvailablePersonFields to obtain the field where the key equals ‘title’. This is somewhat foolish as there are constant values available for each entity field. I should of used BulkUpdateSystem.PersonFieldValueKeys.Title, and so I recommend that you use the constants in your code. There are constants for each entity and are defined in the following:

• BulkUpdateSystem.AppointmentFieldValueKeys
• BulkUpdateSystem.ContactFieldValueKeys
• BulkUpdateSystem.DocumentFieldValueKeys
• BulkUpdateSystem.PersonFieldValueKeys
• BulkUpdateSystem.ProjectFieldValueKeys
• BulkUpdateSystem.SaleFieldValueKeys
• BulkUpdateSystem.SelectionFieldValueKeys

As seen in the code, FieldValueInfo is a type used to refer to one entity field where the action and required parameter are specified.  The action is specified by setting the FieldValueInfo.Operation property. In this example, it is assigned the FieldValueInfo.Operation.Set constant, which is an operation that accepts one parameter ‘Administrator’. When executed, this operation will change the current ‘Admin’ value to ‘Administrator’.

An instance of the BulkUpdateSystem.BackgroundJob class is where all the magic happens. It is responsible for executing the bulk update operation, and requires the following three things:

1. the table name of the target entity
2. One or more FieldValueInfo’s that correspond to the target entity
3. the entity ID values to update

The table name will always be one of the following constants:

• SuperOffice.CRM.Data.AppointmentTableInfo.DictionaryTableName
• SuperOffice.CRM.Data.ContactTableInfo.DictionaryTableName
• SuperOffice.CRM.Data.DocumentTableInfo.DictionaryTableName
• SuperOffice.CRM.Data.PersonTableInfo.DictionaryTableName
• SuperOffice.CRM.Data.SaleTableInfo.DictionaryTableName
• SuperOffice.CRM.Data.SelectionTableInfo.DictionaryTableName

As mentioned in #2, the BackgroundJob constructor accepts an array of FieldValueInfos, and therefore it is possible to process more than one field in a single job. The only restriction is that all FieldValueInfo’s must be a field of the same table. You cannot, for example, pass in FieldValueInfos from both the Appointment and Contact table. They must all belong to one or the other.

Finally, the job is performed for each entity id by calling BackgroundJob.UpdateFieldsAsync.

BulkUpdateSystem FieldValueInfo

Whether you choose to get a list of entity identities using OSQL, CustomSearch or leveraging archive providers, the remainder of this article assumes that you know how to execute a query that will result in one or more entity identities to update.

With a list of entity identities to iterate over, bulk update operations are performed against the fields of that entity, i.e. Person.Title and Person.Interest. The way bulk update understands which fields need to be updated is by using a FieldValueInfo instance, containing the following information:

1. Key: the field name.
2. Operation: the action to be performed.
3. Values: the parameters that correspond to the operation.

FieldValueInfo.Key refers to an entity field name. While most of the entity field names are the same as seen in the database or API, some are not. The next section contains a listing of all field names and their affected API properties in the Entities and Field Types section.

FieldValueInfo.CurrentOperationType is the action performed by the bulk update system. Use the BulkUpdateSystem.OperationTypes static class, containing all available operation values to set the intended action. Each operation has a predetermined set of parameters needed to perform successfully, and is discussed more in the Operations and Values section.

FieldValueInfo.Values is a string array that is expected to contain the parameters required by the field operation. Each operation has a different number of values is required for each operation type, and is detailed below in the Entity and Field Types section.

Operations and Values

Except for Toggle, every operation has a predefined list of parameter dependencies. For example, this means that when the ‘SearchAndReplace’ operation is set, the FieldValueInfo.Values property is expected to contain at least three items, but can optionally contain up to 7 entries.

Each operation type has a different number of required parameters and is covered below in the Entities and Field Types section.

One of the simplest operations BulkUpdateSystem.OperationTypes.Toggle, simply flips the current value from true to false, or 0 to 1, and vice versa. Therefore, it’s not necessary to add any values to the FieldValueInfo.Values property to successfully perform the operation.

Table: List of Operations.

Most updates of existing values will only need one or two values.

The trickiest thing about bulk update is that, for different operations, which not only expect different numbers of parameters, the parameters must be passed in specific indexes within the Values array.

Using the Set operation as an example, only the first index of the array is expected to exist with a value to populate the field property. However, the SearchAndReplace operation expects the Values array to contain at least three but up to four indexes. As seen in Figure Five, fields that accept the “Set, Clear, SearchAndReplace, RegEx” operation types, and perform a SeachAndReplace operation only use the second ([1]) and third ([2]) index for execution.  

Figure Five: Values array for: Set, Clear, SearchAndReplace, RegEx.

As seen in Figure Six, when used with a field that accepts the “Add, Remove, ReplaceWith, Clear, SearchAndReplace, RegEx” operation types, SearchAndReplace expects parameters to exist in third ([2]) and fourth ([3]) index.

Figure Six: Values array for: Add, Remove, ReplaceWith, Clear, SearchAndReplace, RegEx:

There will be cases when some index values are populated but not used. Take the following code example, where a RegEx operation is performed. Notice that the first four indexes in the Values property are not used. For performing a RegEx operation, only the last three indexes are required. 

// search for all companies where name begins with 'super'
var select = S.NewSelect<ContactTableInfo>(cti => cti.Name.Like("super%"));

// get all company id's from the search results 
var companyIds = select.Records(select.Table.ContactId).Select(
                     r => r.Table.ContactId[r]).ToArray();

if (companyIds != null)
{
    // use BulkUpdateSystem.GetAvailableContactFields to get the Name FieldValueInfo
    var contactNameFieldValueInfo = BulkUpdateSystem.GetAvailableContactFields().Where(
            v => v.Key == BulkUpdateSystem.ContactFieldValueKeys.Name).FirstOrDefault();

    // use a set operation to update the field
    contactNameFieldValueInfo.CurrentOperationType = BulkUpdateSystem.OperationTypes.RegEx;
    
    // define the new value
    contactNameFieldValueInfo.Values =
    new[]
    {
            "", // 0 Not Used
            "", // 1 Not Used
            "", // 2 Not Used
            "", // 3 Not Used
            @"super\s?office", // 4 RegEx (Locate)
            "SuperOffice",     // 5 Replace
            System.Text.RegularExpressions.RegexOptions.IgnoreCase.ToString(), // 6 RegexOptions
    };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.ContactTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { contactNameFieldValueInfo },

    // set the company Ids that will be updated
    companyIds);

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }
}

In the next section, each field details what parameters are expected at which index in the “Expected Field Values” column.

Entities and Field Types

With a general understanding of how BulkUpdate works, the following table lists all available properties for each entity, details what operations are allowed one each property, and details what the expected values are for each operation.

Operations and User-Defined and Extra Fields

All user-defined and extra fields (UDF’s) are accessible and updatable using BulkUpdate. The user performing the update must have the data right to write to these fields, or possess the AllowReadonlyOverride functional right.

When updating a UDF, the FieldValueInfo.Key property must be the UDF progid. Extra field keys are the field name.

Bulk Update Examples using NetServer Core API:

Here are a few more examples how to use bulk update. 

Contact Change Category:

// search for all companies with categoryId = 10
var select = S.NewSelect<ContactTableInfo>(cti => cti.CategoryIdx.Equal(10));

// get all company id's from the search results 
var companyIds = select.Records(select.Table.ContactId).Select(
                     r => r.Table.ContactId[r]).ToArray();

if (companyIds != null)
{

    // use GetAvailableContactFields to get the Category FieldValueInfo
    var contactUdefFieldValueInfo = BulkUpdateSystem.GetAvailableContactFields()
            .Where(v => v.Key == BulkUpdateSystem.ContactFieldValueKeys.Category)
            .FirstOrDefault();

    // use a SelectOne operation to update the field
    contactUdefFieldValueInfo.CurrentOperationType =
                                BulkUpdateSystem.OperationTypes.SelectOne;

    // define the new value
    contactUdefFieldValueInfo.Values =
        new[]
        {
            "15", // CategoryId
        };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.ContactTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { contactUdefFieldValueInfo },

    // set the company Ids that will be updated
    companyIds);

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }
}

Contact No Mailings Checkbox:

// search for all companies where NoMailing is Checked
var select = S.NewSelect<ContactTableInfo>(cti => cti.Nomailing.Equal(1));

// get all company id's from the search results 
var companyIds = select.Records(select.Table.ContactId).Select(
                     r => r.Table.ContactId[r]).ToArray();

if (companyIds != null)
{

    // use BulkUpdateSystem.GetAvailableContactFields to get the Name FieldValueInfo
    var contactUdefFieldValueInfo = BulkUpdateSystem.GetAvailableContactFields()
            .Where(v => v.Key == BulkUpdateSystem.ContactFieldValueKeys.ContactNoMail)
            .FirstOrDefault();

    // use a set operation to update the field
    contactUdefFieldValueInfo.CurrentOperationType =
             BulkUpdateSystem.OperationTypes.Set;

    // define the new value
    contactUdefFieldValueInfo.Values =
    new[]
    {
            "0", // NoMailing = Unchecked.
    };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.ContactTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { contactUdefFieldValueInfo },

    // set the company Ids that will be updated
    companyIds);

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }
}

Contact Add Interests:

// search for all companies where name begins with 'super'
var select = S.NewSelect<ContactTableInfo>(cti => cti.Name.Like("super%"));

// get all company id's from the search results 
var companyIds = select.Records(select.Table.ContactId).Select(
                     r => r.Table.ContactId[r]).ToArray();

if (companyIds != null)
{

    // use BulkUpdateSystem.GetAvailableContactFields to get the Name FieldValueInfo
    var contactAddInterestsFieldValueInfo = BulkUpdateSystem.GetAvailableContactFields().Where(
            v => v.Key == BulkUpdateSystem.ContactFieldValueKeys.AddInterests).FirstOrDefault();

    // use a set operation to update the field
    contactAddInterestsFieldValueInfo.CurrentOperationType = BulkUpdateSystem.OperationTypes.AddItems;

    // define the new value
    contactAddInterestsFieldValueInfo.Values =
    new[]
    {
            "12", // 0 Interest ID 12
            "23", // 1 Interest ID 23
            "34", // 2 Interest ID 34
    };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.ContactTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { contactAddInterestsFieldValueInfo },

    // set the person Ids that will be updated
    companyIds);

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }

}

Set Contact User-Defined Field:

// search for all companies where name begins with 'super'
var select = S.NewSelect<ContactTableInfo>(cti => cti.Name.Like("super%"));

// get all company id's from the search results 
var companyIds = select.Records(select.Table.ContactId).Select(
                     r => r.Table.ContactId[r]).ToArray();

if (companyIds != null)
{

    // use BulkUpdateSystem.GetAvailableContactFields to get the Name FieldValueInfo
    var contactUdefFieldValueInfo = BulkUpdateSystem.GetAvailableContactFields()
            .Where(v => v.Key == "SuperOffice:1").FirstOrDefault();

    // use a set operation to update the field
    contactUdefFieldValueInfo.CurrentOperationType =
             BulkUpdateSystem.OperationTypes.SearchAndReplace;

    // define the new value
    contactUdefFieldValueInfo.Values =
    new[]
    {
            "",                     // Not Used
            "Stage One",            // Search Value
            "Stage Two",            // Replace With
            "IgnoreCase, WholeWord"  // Search Options
    };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.ContactTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { contactUdefFieldValueInfo },

    // set the person Ids that will be updated
    companyIds);

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }
}

Appointment: complete all project appointments:

// search for all appointments where projectId = 121
var select = S.NewSelect<AppointmentTableInfo>(ati => ati.ProjectId.Equal(121));

// get all appointment id's from the search results 
var appointIds = select.Records(select.Table.AppointmentId).Select(
                     r => r.Table.AppointmentId[r]).ToArray();

if (appointIds != null)
{

// use BulkUpdateSystem.GetAvailableAppointmentFields to get the Done FieldValueInfo
    var appntFieldValueInfo = BulkUpdateSystem.GetAvailableAppointmentFields()
            .Where(v => v.Key == BulkUpdateSystem.AppointmentFieldValueKeys.Done)
            .FirstOrDefault();

    // use a Check operation to update the field
    appntFieldValueInfo.CurrentOperationType =
             BulkUpdateSystem.OperationTypes.Check;

    // define the new value
    appntFieldValueInfo.Values =
    new[]
    {
            "1", // 0 = NotStarted, 1 = Completed
    };

    // instantiate a new BulkUpdate BackgroundJob
    BulkUpdateSystem.BackgroundJob updateJob = new BulkUpdateSystem.BackgroundJob(

    // define the target table name
    SuperOffice.CRM.Data.AppointmentTableInfo.DictionaryTableName,

    // set then FieldValueInfo
    new[] { appntFieldValueInfo },

    // set the company Ids that will be updated
    appointIds);

    // execute the job
    bool updateSuccess = updateJob.UpdateFieldsAsync();

    while (!updateJob.Done)
    {
        Thread.Sleep(500);
    }
}

Example using Web Services (courtesy of Matthijs Wagemakers):

using (var bulkAgent = new BulkUpdateAgent())
{
    FieldValueInfo[] fields = bulkAgent.GetAvailableFields("­appointment");
    FieldValueInfo filteredField = fields.Where(x => x.Key == SuperOffice.CRM.BulkUpdate.Bul­kUpdateSystem.AppointmentF­ieldValueKeys.Done)
        .FirstOrDefault();
    filteredField.IsActive = true;
    filteredField.CurrentOperation­Type = SuperOffice.CRM.BulkUpdate.Bul­kUpdateSystem.OperationTyp­es.Check;
    filteredField.Values = new[] { "1" };

    int bulkId = bulkAgent.ExecuteByEntityIds(
        new[] { filteredField },
        "appointment",
        Guid.NewGuid().ToString(),        // must be unique for concurrent jobs
        string.Join(",", new[] { 2, 10 }))
        ;
}

Bulk Update using NetServer Web Services API:

The web client version 8.3 introduced support for Bulkupdate via  Web Services. exist to facilitate this functionality in the web client or distributed architectures. It is, however, on the roadmap! When you see bulk update in the web client, then you know the web services are available to facilitate this functionality.

https://community.superoffice.com/documentation/sdk/SO.NetServer.Web.Services/html/Reference-WebAPI-Agents-BulkUpdate_Agent-BulkUpdate_Agent.htm

Conclusion

Prior to Bulk Update, users had to open each entity and update them manually. As shown in this article that is no longer the case, and third-party applications are also able to take advantage of this system using the API’s.

Bulk Update is not to be taken lightly. It’s a very powerful tool that can save users hours of manual data entry, but can also create havoc if used improperly. I recommend running all your tests on backups or test database to ensure you get the expected results.

I hope this article has helped in learning more about bulk update and potential benefits to your onsite integrations.