We’ve developed some resources to help you work effectively from home during COVID-19 Click to learn more

Bulk Update API

Easily Update Multiple Entities at Once
Ratings

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.

OperationTypes

Description

SelectOne       

One or two values expected for this operation. See tables below.

Clear

No values are used for this operation. Whatever value exists will be set to an empty string.

Set

One or two values expected for this operation. See tables below.

Check

One value is expected for this operation.

 

Check is used for mostly Boolean and short field types. Details of expected values is located in the entity tables below.

Toggle

No values are used for this operation. Boolean fields will be Set to this opposite Setting. Fields with number values are either 1 or 0 and will be swapped accordingly.

AddItems

Multiple values allowed. Values are added to the target.

RemoveItems

Multiple values allowed. Values are removed from the target.

Add

Two values expected.

Remove

One value expected.

ReplaceWith

Two values expected. Removes existing items and updates matching record.

SearchAndReplace

Finds all matches and replaces existing information.

RegEx

Uses in conjunction with SearchAndReplace, for advanced search scenarios.

 

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.

  • Appointment Table
    • Appointment Table

      Appointment fields. Key constants are in BulkUpdateSystem.AppointmentFieldValueKeys.

      Appointment

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      Activitylink

      Add, Remove, ReplaceWith, Clear

      Values Count = 1…n

      Values are encoded as tableName=recordId values with an optional comment:

      “appointment=78” or
      “appointment=78&comment=Some link comment”

      Allowed table names:
      (case-sensitive)
      appointment, document, project, sale or URL

      add:
      Creates and adds all values as RelationRows.

      remove:
      Deletes all similar relations.

      ReplaceWith:
      Deletes all similar relations, then adds back new ones. 

      Clear: deletes all existing relations.

      LinksHelper

      AlarmOffSet

      Set

      Values Count = 1

      Values[0] = (int) Alarm

      Alarm

      Description

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7

      Set: Values[0] = (string)

      Clear: Clears value

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      AppointmentText

      Done

      Check, Toggle

      Values Count = 1

      Check:
      Values[0] = (int) 0 or 1
      0 = Status.NotStarted
      1 = Status.Completed 

      Toggle: invert current

      Appointment.Status, Appointment.AssociateId, Appointment.GroupIdx

      FreeBusy

      Set

      Values Count = 1

      Values[0] = (int)
      0 = FreeBusy.Free
      1 = FreeBusy.Busy

      FreeBusy

      HasAlarm

      Check

      Values Count = 1 

      Values[0] = (int) HasAlarm
      0 = No, 1 = Yes

      HasAlarm

      Owner

      SelectOne

      Values Count = 1

      Values[0] = (int) AssociateId

      Owner

      ProjectId

      SelectOne

      Values Count = 1 

      Values[0] = (int) ProjectId

      ProjectId

      Priority

      SelectOne

      Values Count = 1 

      Values[0] = (int) PriorityIdx

      Priority

      Published

      Check

      Values Count = 1 

      Values[0] = (int) IsPublished
      0 = No, 1 = Yes

      PublishHelper

      SaleId

      SelectOne

      Values Count = 1 

      Values[0] = (int) SaleId

      SaleId

      Type

      SelectOne

      Values Count = 1 

      Values[0] = (int) TaskIdx

      Task

      StartDate

      Set

      Values Count = 1 

      Values[0] = (datetime) 
      Only changes Date. Times are changed via StartTime field.

      DoBy (Date Only)
      EndDate (Date Only)

      StartTime

      Set

      Values Count = 1 

      Values[0] = (int)
      minutes since midnight relative to current datetime

      DoBy
      EndDate

       

      EndTime

      Set

      StartTimeAndEndTime

      Set

      Values Count = 2

      Values[0] = (int) DoBy
      Values[1] = (int) EndDate

      minutes since midnight relative to current datetime

      DoBy
      EndTime

      VisibleFor

      SelectOne

      Values Count = 1

      Values[0] = (int) 
      Int.MinValue = All
      Neg: -10 = GroupId       (10)
      Pos:   10 = AssociateId (10)

      VisibleFor

      See User-Defined Fields below.

       

  • Contact Table
    • Contact Table

      Contact fields. Key constants are in BulkUpdateSystem.ContactFieldValueKeys.

      Contact

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      AddInterests

      addItems

      Values Count = 1…n

      Values[n] = (int) InteresItemtId

      Selects each interest.

      Interests

      RemoveInterest

      removeItems

      Values Count = 1…n 

      Values[n] = (int) InteresItemtId

      Deselects each interest.

      Interests

      AssociateId

      SelectOne

      Values Count = 1 

      Values[0] = (int) AssociateId

      Associate

      Business

      SelectOne

      Values Count = 1 

      Values[0] = (int) BusinessIdx

      Business

      Category

      SelectOne

      Values Count = 1 

      Values[0] = (int) CategoryIdx

      Category

      OrgNr

      Set

      Values Count = 1 

      Values[0] = (string) OrgNr

      OrgNr

      Number1

      Set

      Values Count = 1 

      Values[0] = (string) Number1

      Number1

      Number2

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7 

      Set: Values[0] = (string)

      Clear: Clears value

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      Number2

      Name

      Name

      Department

      Department

      StreetAddressAddress1

      StreetAddress.Address1

      StreetAddressAddress2

      StreetAddress.Address2

      StreetAddressAddress3

      StreetAddress.Address3

      StreetAddressZipCode

      StreetAddress.Zipcode

      StreetAddressZipCity

      StreetAddress.City

      StreetAddressCounty

      StreetAddress.County

      StreetAddressState

      StreetAddressState

      PostalAddressAddress1

      PostalAddress.Address1

      PostalAddressAddress2

      PostalAddress.Address2

      PostalAddressAddress3

      PostalAddress.Address3

      PostalAddressZipCode

      PostalAddress.Zipcode

      PostalAddressZipCity

      PostalAddress.City

      PostalAddressCounty

      PostalAddress.County

      PostalAddressState

      PostalAddress.State

      Stopped

      Set

      Values Count = 1

      Values[0] = (int) 0 or 1
      0 = unChecked
      1 = Checked

       

      Xstop

      ContactNoMail

      Set

      Nomailing

      ContactCountry

      SelectOne

      Values Count = 1

      Values[0] = (int) CountryId

      Country

      Url

      Add, Remove, ReplaceWith, Clear, SearchAndReplace, RegEx

       

       

       

      Values Count = 0 to 8 

      Add: if value doesn’t already exist

      Values[0] = (string) value
      Values[1] = (string) descr

      Remove: only one needs to match

      Values[0] = (string) value
      or...
      Values[1] = (string) descr

      ReplaceWith:
      locates row with Rank =1 and updates it, removes all others. Updates using:

      Values[0] = (string) value
      Values[1] = (string) descr

      Clear: Clears all existing rows

      SearchAndReplace:
      works on value only, not description.

      Values[2] = searchString
      Values[3] = replaceString
      Values[4] = Search Option

      RegEx:
      works on value only, not description.

      Values[5] = expression
      Values[6] = replace with
      Values[7] = search options 

      Urls

      Email

      Emails

      Phone

      Phones

      Fax

      Faxes

      See User-Defined Fields below.

       

  • Document Table
    • Document Table

      Document fields. Key constants are in BulkUpdateSystem.DocumentFieldValueKeys.

      Document

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      Done

      Check, Toggle

      Values Count = 1

      Check:
      Values[0] = (int)
      0 = Status.NotStarted
      1 = Status.Completed 

      Toggle: invert current

      DocumentAppointment.Status

      Owner

      SelectOne

      Values Count = 1 

      Values[0] = (int) AssociateId

      DocumentAppointment.AssociateId DocumentAppointment.GroupIdx

      ProjectId

      SelectOne

      Values Count = 1

      Values[0] = (int) ProjectId

      DocumentAppointment .ProjectId

      SaleId

      SelectOne

      Values Count = 1 

      Values[0] = (int) SaleId

      DocumentAppointment.SaleId

      Date

      Set, Clear

      Values Count = 1 

      Set:
      Values[0] = (Date) 
      Format: InvariantInfo 

      Clear = 1/1/0001 12:00:00 AM

      DocumentAppointment.ActiveDate
      DocumentAppointment.EndDate
      DocumentAppointment.DoBy
      DocumentAppointment.Done

      Subject

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7

      Set: Values[0] = (string)

      Clear: Clears value

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options 

      Header

      Description

      DocumentText

      OurRef

      OurRef

      YourRef

      YourRef

      Type

      SelectOne

      Values Count = 1

      Values[0] = (int) TaskIdx

      DocumentAppointment.TaskIdx

      VisibleFor

      SelectOne

      Values Count = 1 

      Values[0] = (int)

      Int.MinValue = All
      Neg: -10 = GroupId       (10)
      Pos:   10 = AssociateId (10)

      DocumentAppointment.VisibleFor

      Activitylink

      Add, Remove, ReplaceWith, Clear

      Values Count = 1…n 

      Values are encoded as tableName=recordId values with an optional comment:

      “sale=78”
      or
      “sale=78&comment=Some link comment” 

      Allowed table names:
      (case-sensitive)
      appointment, document, project, sale or URL 

      add:
      Creates and adds all values as RelationRows.

      remove:
      Deletes all similar relations. 

      ReplaceWith:
      Deletes all similar relations, then adds back new ones. 

      Clear:
      Deletes all existing relations.

      LinksHelper

      Published

      Check

      Values Count = 1

      Values[0] = (int) IsPublished

      0 = Not published
      1 = Published

      PublishHelper

      See User-Defined Fields below.

       

  • Person Table
    • Person Table

      Person fields. Key constants are in BulkUpdateSystem.PersonFieldValueKeys.

      Person

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      Position

      SelectOne, Clear

      Values Count = 0 or 1

      Values[0] = (int) PositionIdx

      Position

      Number

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7

      Set: Values[0] = (string)

      Clear: Clears value 

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      Number

      Title

      Title

      MrMrs

      MrMrs

      Salutation

      Salutation

      FirstName

      Firstname

      MiddleName

      MiddleName

      LastName

      Lastname

      AddressAddress1

      Address.Address1

      AddressAddress2

      Address.Address2

      AddressAddress3

      Address.Address3

      AddressZipCode

      Address.Zipcode

      AddressZipCity

      Address.City

      AddressCounty

      Address.County

      AddressState

      Address.State

      PersonNoMail

      Set

      Values Count = 1

      Values[0] = (int) 0 or 1
      0 = unChecked
      1 = Checked

      Nomailing

      AddInterests

      addItems

      Values Count = 1…n 

      Values[n] = (int) InteresItemtId

      Selects each interest.

      InterestHelper

      ActiveInterests

      RemoveInterest

      removeItems

      Values Count = 1…n 

      Values[n] = (int) InteresItemtId

      Deselects each interest.

      InterestHelper

      ActiveInterests

      AddSubscriptions

      addItems

      Values Count = 1…n

      Values[0] = (int) MailingTypeId

      Deletes a record in the MailingTypeReservation table.

      MailingTypeReservations

      RemoveSubscriptions

      removeItems

      Values Count = 1...n

      Values[0] = (int) MailingTypeId

      Adds a record to the MailingTypeReservation table.

      MailingTypeReservations

      LegalBasis

      Add, Remove

      Values Count = 0 to 5

      Add: 
      if value doesn’t already exist
      Values[0] = (int) consentPurposeId
      Values[1]  = (int) legalBaseId

      Optional:

      Values[2] = (int) consentSourceId
      Values[3] = (string) comment

      Remove: 

      Clears all existing rows 

      Values[0] = *not used
      Values[1]  = *not used
      Values[2] = *not used
      Values[3] = *not used
      Values[4] = (int) consentPurposeId

      ConsentPersons

      UseAsMailingAddress

      Check, Toggle

      Values Count = 1

      Check:
      Values[0] = (int)
      0 = Use company address
      1 = Use person address 

      Toggle: invert current

      Usepersonaddress

      Retired

      Set

      Values Count = 1

      Values[0] = (int)
      0 = not retired
      1 = retired

      Retired

      BirthDate

      Set, Clear

      Values Count = 1

      Set:
      Values[0] = (Date) 
      Format: InvariantInfo 

      Clear:
      Day = 0, Month = 0,  Year = 0

      DayOfBirth

      MonthOfBirth

      YearOfBirth

      PersonCountry

      SelectOne

      Values Count = 1 

      Values[0] = (int) CountryId

      Country

      Url

      Add, Remove, ReplaceWith, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 8

      Add:
      if value doesn’t already exist
      Values[0] = (string) value
      Values[1] = (string) description 

      Remove:
      only one needs to match
      Values[0] = (string) value,
      or...
      Values[1] = (string) description

      ReplaceWith:
      locates row with Rank =1 and updates it, removes all others. Updates using:
      Values[0] = (string) value
      Values[1] = (string) description 

      Clear:
      Clears all existing rows 

      SearchAndReplace:
      works on value only, not description.
      Values[2] = searchString
      Values[3] = replaceString
      Values[4] = Search Options 

      RegEx:
      works on value only, not description.
      Values[5] = expression
      Values[6] = replace with
      Values[7] = Search Options

      Urls

      value = UrlAddress

      Email

      Emails
      value = EmailAddress

      MobilePhone

      MobilePhones

      value = Phone

      DirectPhone

      OfficePhones

      value = Phone

      PrivatePhone

      PrivatePhones

      value = Phone

      OtherPhone

      Pagers

      value = Phone

      DirectFax

      Faxes

      value = Phone

      See User-Defined Fields below.

       

  • Project Table
    • Project Table

      Project fields. Key constants are in BulkUpdateSystem.ProjectFieldValueKeys.

      Project

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      Responsible 

      SelectOne

      Values Count = 1

      Values[0] = (int) AssociateId

      Associate

      TypeAndStatus 

      SelectOne

      Values Count = 2 

      Values[0] = (int) TypeIdx
      Values[1] = (int) StatusIdx

      Type

      Status

      Done        

      Check, Toggle

      Values Count = 1 

      Check:
      Values[0] = (int)
      0 = Not Done
      1 = Done 

      Toggle: invert current

      Done

      Number      

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7 

      Set: Values[0] = (string)

      Clear: Clears value 

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      ProjectNumber

      Name        

      Name

      Description 

      ProjectText

      EndDate     

      Set, Clear

      Values Count = 1

      Set:
      Values[0] = (Date) 
      Format: InvariantInfo 

      Clear: 1/1/0001 12:00:00 AM

      EndDate

      Activitylink

      Add, Remove, ReplaceWith, Clear

      Values Count = 1…n

      Values are encoded as tableName=recordId with an optional comment. Example:
      “appointment=78”
      or
      “appointment=78&comment=Some link comment” 

      Allowed table names:
      (case-sensitive)
      appointment, document, project, sale or URL 

      Add:
      Creates and adds all values as RelationRows. 

      Remove:
      Deletes all similar relations. 

      ReplaceWith:
      Deletes all similar relations, then adds back new ones. 

      Clear:
      Deletes all existing relations.

      LinksHelper

      Published   

      Check

      Values Count = 1

      Values[0] = (int) IsPublished
      0 = Not published
      1 = Published

      PublishHelper

      EventFromDate

      Set, Clear

      Values Count = 1

      Set: Values[0] = (Date)
      Format: InvariantInfo

      Clear: 1/1/0001 12:00:00 AM

      PublishHelper.PublishStart

      EventToDate 

      PublishHelper.PublishEnd

      Url         

      Add, Remove, ReplaceWith, Clear,  SearchAndReplace, RegEx

      Values Count = 0 to 8

      Add: if value doesn’t already exist
      Values[0] = (string) value
      Values[1] = (string) description 

      Remove:
      Only one needs to match
      Values[0] = (string) value
      or...
      Values[1] = (string) description 

      ReplaceWith:
      Locates row with Rank =1 and updates it, removes all others. Updates using:
      Values[0] = (string) value
      Values[1] = (string) description 

      Clear:
      Clears all existing rows 

      SearchAndReplace:
      Works on value only, not description.
      Values[2] = searchString
      Values[3] = replaceString
      Values[4] = Search Options 

      RegEx:
      Works on value only, not description.
      Values[5] = expression
      Values[6] = replace with
      Values[7] = Search Options

      Urls

      See User-Defined Fields below.

       

  • Sale Table
    • Sale Table

      Sale fields. Key constants are in BulkUpdateSystem.SaleFieldValueKeys.

      Sale

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      Owner        

      SelectOne

      Values Count = 1

      Values[0] = (int) AssociateId

      Associate

      TypeAndStage   

      SelectOne

      Values Count = 2

      Values[0] = (int) SaleTypeId
      Values[1] = (int) ProbabilityId

      SaleType

      Probability

      Done         

      Check, Toggle

      Values Count = 1

      Check:
      Values[0] = (int)
      0 = Do Not Know
      1 = No
      2 = Yes 

      Toggle:
      invert current value

      Done

      Date         

      Set, Clear

      Values Count = 1

      Values[0] = (Date)
      Format: InvariantInfo

      Clear: 1/1/0001 12:00:00 AM

      Saledate

      Heading      

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7 

      Set: Values[0] = (string) 

      Clear:
      Clears value 

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      Heading

      Description  

      SaleText

      Number1      

      Number1

      EarningAmount

      Set

      Values Count = 1

      Values[0] = (double) earning

      Earning
      EarningPercent
      Amount

      EarningPercent

      Set

      Values Count = 1 

      Values[0] = (double) percent

      EarningPercent
      Earning

      Amount       

      Set

      Values Count = 1 

      Values[0] = (double) amount

      Amount
      Earning
      EarningPercent

      Probability  

      Set

      Values Count = 1

      Values[0] = (short) probability

      Probability

      Source       

      SelectOne

      Values Count = 1 

      Values[0] = (int) SourceId

      Source

      Competitor   

      SelectOne

      Values Count = 1 

      Values[0] = (int) ComptrId

      ComptrId

      Credited     

      SelectOne

      Values Count = 1 

      Values[0] = (int) CreditedId

      Credited

      Currency     

      SelectOne

      Values Count = 1

      Values[0] = (int) CurrencyId

      Currency

      VisibleFor   

      SelectOne

      Values Count = 1

      Values[0] = (int)
      Int.MinValue = All
      Neg: -10 = GroupId       (10)
      Pos:   10 = AssociateId (10)

      VisibleFor

      Activitylink 

      Add, Remove, ReplaceWith, Clear

      Values are encoded as tableName=recordId values with an optional comment:

      “appointment=78”
      or
      “appointment=78&comment=Some link comment” 

      Allowed table names:
      (case-sensitive)
      appointment, document, project, sale or URL 

      Add:
      Creates and adds all values as RelationRows. 

      Remove:
      Deletes all similar relations. 

      ReplaceWith:
      Deletes all similar relations, then adds back new ones. 

      Clear:
      Deletes all existing relations.

      LinksHelper

      Published    

      Check

      Values Count = 1

      Values[0] = (int) IsPublished
      0 = Not published
      1 = Published

      PublishHelper

      See User-Defined Fields below.

       

  • Selection Table
    • Selection Table

      Selection fields. Key constants are in BulkUpdateSystem.SelectionFieldValueKeys.

      Selection

      FieldValueKeys

      OperationTypes

      Expected Field Values

      Affected Properties

      Done      

      Check, Toggle

      Values Count = 1 

      Check:
      Values[0] = (int)
      0 = false
      1 = True 

      Toggle:
      invert current value

      Row.Done

      Category  

      SelectOne

      Values Count = 1

      Values[0] = (int) SearchCatId

      Row.SearchCatId

      Owner     

      SelectOne

      Values Count = 1

      Values[0] = (int) AssociateId

      Row.AssociateId
      Row.GroupId

      Name      

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7

      Set: Values[0] = (string)

      Clear: Clears value

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      Name

      Description

      SelectionText

      VisibleFor

      SelectOne

      Values Count = 1

      Values[0] = (int)
      Int.MinValue = All
      Neg: -10 = GroupId       (10)
      Pos:   10 = AssociateId (10)

      VisibleFor

       

 

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.

  • User-Defined FIelds
    • User-Defined Tables

      User-Defined fields. Key constants are the user-defined field progId.

      User-Defined Fields

      Field Type

      OperationTypes

      Expected Field Values

      Number and

      Decimal

      Set

      Values Count = 1

      Values[0] = (int)

      ShortText and LongText

      Set, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7 

      Set: Values[0] = (string) 

      Clear: Clears value

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      Date and UnlimitedDate

      Set, Clear

      Values Count = 1

      Set:
      Values[0] = (Date)
      or
      Values[0] = (DateTime)

      Format: InvariantInfo 

      Clear:
      Sets date to min date: 1/1/0001 12:00:00 AM

      Checkbox

      Check and Toggle

      Values Count = 1

      Check:
      Values[0] = (int) or (bool) 

      Toggle:
      invert current value

      List

      SelectOne, Clear (?)

      Values Count = 1

      SelectOne:
      Values[0] = (int)

      Clear:
      Only on certain UDF’s.

       

  • Extra Fields
    • Extra Fields

      Customer service extra fields. Key constants use the extra field name.

      Extra Fields

      Field Type

      OperationTypes

      Expected Field Values

      Integer

      Set

      Values Count = 1

      Values[0] = (int)

      Clob and Varchar

      SelectOne, Clear, SearchAndReplace, RegEx

      Values Count = 0 to 7

      SelectOne: Values[0] = (string)

      Clear: Clears value

      SearchAndReplace:
      Values[1] = searchString
      Values[2] = replaceString
      Values[3] = options 

      RegEx:
      Values[4] = expression
      Values[5] = replace with
      Values[6] = options

      Date and DateTime

      Set, Clear

      Values Count = 1

      Set:
      Values[0] = (Date)
      or
      Values[0] = (DateTime)

      Clear:
      Sets date to min date: 1/1/0001 12:00:00 AM

      Time

      Set

      Values Count = 1

      Check:
      Values[0] = (int)

      Value:
      Number of minutes since midnight.

      TimeSpan Set, Clear

      Values Count = 1

      Set:
      Values[0] = (int, day)
      Values[1] = (int, hour)
      Values[2] = (int, minute)

      Clear:
      Sets TimeSpan to 0

      Bool Set

      Values Count = 1

      Check:

      Values[0] = (int) 0 or 1
      0 = False
      1 = True

      Float Set

      Values Count = 1

      Values[0] = (float)

      CustomerId,
      TicketId,
      UserId,
      AttachmentId,
      CategoryId,
      PriorityId,
      FaqEntryId,
      SaleId,
      ProjectId,
      AppointmentId,
      ContactCategoryId,
      BusinessId,
      ExtraTableId

      SelectOne, Clear

      Values Count = 1

      Set:
      Values[0] = (int)

      Clear:
      Sets value to 0

      Link: Not Supported  

       

       

 

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.

Post Comment To the top