Data Operations

The DealCloud object provides methods to Create, Read, Update and Delete data in bulk. As its default behaviour, when working with site data, the library uses DataFrames with the Pandas (opens in a new tab) library, however the option is available for it to work with just python standard data types.

List Configured Views

DealCloud.list_configured_views() returns a Rows object, containing all configured views in the site. The argument is_private can be passed as a boolean, where if true, only private views to (including views shared with) the authenticated user will be returned.

Example:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
views = dc.list_configured_views()

views will contain a summary of configured views.

Read Data

DealCloud.read_data() allows users to read data from a DealCloud object into a pandas.DataFrame or a list of python dictionaries.

ℹ️

Only one of object_id or view_id can be populated

Reading Data from an Object

Example:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data("Company")

data will be a pandas.DataFrame as below:

EntryIdCompanyNameCompanyType
12345Company1[{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}]
12346Company2[{'seqNumber': 1, 'isAutoPdf': False, 'id': 3197782, 'name': 'Operating Company', 'entryListId': -6}]
12347Company3[{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}]

To return a python list instead of a pandas.DataFrame:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data("Company", output="list")

data will be a list of dicts as below:

[
    {
        'EntryId': 12345,
        'CompanyName': 'Company1',
        'CompanyType': [
             {
                'seqNumber': 3,
                'isAutoPdf': False,
                'id': 3197780,
                'name': 'Limited Partner',
                'entryListId': -6
             }
       ],
    },
    {
      'EntryId': 12346,
      'CompanyName': 'Company2',
      'CompanyType': [
         {
            'seqNumber': 1,
            'isAutoPdf': False,
            'id': 3197782,
            'name': 'Operating Company',
            'entryListId': -6
         }
      ]
    },
    {
       'EntryId': 12347,
        'CompanyName': 'Company3',
        'CompanyType': [
           {
                'seqNumber': 4,
                'isAutoPdf': False,
                'id': 3197779,
                'name': 'Service Provider',
                'entryListId': -6
           }
        ]
    },
 ]
Resolving to Name or to ID
ℹ️

The following methods only apply to where a pandas.DataFrame is returned.

Choice, Reference and User fields appear as lists of dictionaries as seen in the above example by default.

To resolve these fields to a more readable name value:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data("Company", resolve="name")

data will be a pandas.DataFrame as below:

EntryIdCompanyNameCompanyType
12345Company1Limited Partner
12346Company2Operating Company
12347Company3Limited Partner

To resolve the fields to an id value:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data("Company", resolve="id")

data will be a pandas.DataFrame as below:

EntryIdCompanyNameCompanyType
12345Company13197780
12346Company23197782
12347Company33197780
Reading a Subset of Fields

To read a specific set of fields from an object, pass a list of DealCloud field API names to the argument fields:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data("Company", fields=["CompanyName"])

data will be a pandas.DataFrame as below:

EntryIdCompanyName
12345Company1
12346Company2
12347Company3
Using Queries to Read Data

A query can be passed to the DealCloud request for data to return more specific information, and reduce the volume of incoming data. To use a query, pass the query string to the query argument. The available query operations are below:

NameQuery Operation
Equals$eq
Contains$contains
Greater$gt
GreaterOrEqual$gte
Less$lt
LessOrEquals$lte
StartsWith$startswith
In$in
Between$between
NotIn$nin
NotEqualTo$not
EndsWith$endswith
Or$or
And$and
from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
dc.read_data("Company", query="{CompanyName: {$contains: \"1\"}}")

data will be a pandas.DataFrame as below:

EntryIdCompanyNameCompanyType
12345Company13197780

Some other example query strings are below: Filter on excluding records with a specific reference value:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
dc.read_data("Company", query="{CoveragePerson: {$nin: [5785]}")

Filter using Or:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
dc.read_data("Company", query="{$or: [{CompanyName: \"DealCloud\"},{CompanyName: \"API Entry\"}]}")

Reading Data from a View

To read data from a view with ID: 12345

Example:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data(view_id=12345)

Alternatively, if the view is named: "My Company View":

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data(view_id="My Company View")

data will be a pandas.DataFrame as below:

EntryIdCompanyNameCompanyType
12345Company1[{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}]
12346Company2[{'seqNumber': 1, 'isAutoPdf': False, 'id': 3197782, 'name': 'Operating Company', 'entryListId': -6}]
12347Company3[{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}]
Using a Filter When Reading View Data

When reading data from a view, which has "Supply Value Later" filters configured, the filter values can be provided by passing them to the view_filter argument in a list of dicts. For more information on their format, please see the API Documentation (opens in a new tab).

For Example, supplying a "CompanyType" filter value to filter for only "Limited Partner" companies:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
data = dc.read_data(
    view_id="My Company View",
    view_filter=[
        {
            "column": "CompanyType",
            "value": [3197780]
        }
    ]
)

data will be a pandas.DataFrame as below:

EntryIdCompanyNameCompanyType
12345Company1[{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}]
12347Company3[{'seqNumber': 3, 'isAutoPdf': False, 'id': 3197780, 'name': 'Limited Partner', 'entryListId': -6}]

Create, Update and Upsert Data

DealCloud.insert_data(), DealCloud.update_data(), DealCloud.upsert_data() are methods which provide the ability to create, update and upsert data respectively.

ℹ️

When not using use_dealcloud_ids, for Reference fields, reference by record EntryId. For choice fields, use the choice value ID. For user fields, use the user ID (from DealCloud.get_users()). When using use_dealcloud_ids, for Reference fields, reference by the lookup column on the referenced object. For choice fields, use the choice field display name. For user fields, use the user email address.

Field Mapping

All columns passed to the create, update and upsert methods must match by API Name. If a column passed to the method does not exist in the site by API name, then a KeyError will be raised:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {
        "CompanyName": "Test Company 1",
        "UnmappableColumn": "Foo",
    }
]
responses = dc.insert_data("Company", to_send)

Will Return

Traceback...
KeyError: "mapping error, could not map: ['UnmappableColumn']"

Create Data

Example, to insert data into the "Company" object from a list:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {
        "CompanyName": "Test Company 1",
        "CompanyType": 12345,
        "BusinessDescription": "Here is a business description",
        "Sector": 14321,
    }
]
responses = dc.insert_data("Company", to_send)

If successful, responses will contain the below - note that EntryId is now included:

[
    {
        "EntryId": 234567,
        "CompanyName": "Test Company 1",
        "CompanyType": 12345,
        "BusinessDescription": "Here is a business description",
        "Sector": 14321,
    }
]

Example, to insert data into the "Company" object from a CSV, using pandas:

CSV File (company.csv):

CompanyNameCompanyType
Company13197780
Company23197782
Company33197780
import pandas as pd
from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = pd.read_csv("company.csv")
responses = dc.insert_data("Company", to_send)

Example, to insert data into the "Company" object using "ExternalSystemID" as a lookup column:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {
        "CompanyName": "Test Company 1",
        "CompanyType": "Operating Company",
        "BusinessDescription": "Here is a business description",
        "Sector": "612E9FF4-1D8C-41B7-B8ED-2D1C15240434",
    }
]
 
responses = dc.insert_data(
    object_api_name="Company",
    data=to_send,
    use_dealcloud_ids=False,
    lookup_column="ExternalSystemID"
)

Update Data

To update existing site data, the process is similar, except objects must include EntryId, or a valid primary key by lookup_column.

Example, to update data in the "Company" object from a list:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {
        "EntryId": 234567,
        "BusinessDescription": "Here is an updated business description!",
    }
]
responses = dc.insert_data("Company", to_send)

If successful, responses will contain the below:

[
    {
        "EntryId": 234567,
        "CompanyName": "Test Company 1",
        "CompanyType": 12345,
        "BusinessDescription": "Here is an updated business description!",
        "Sector": 14321,
    }
]

Example, to update data in the "Company" object from a list, using the lookup_column, ExternalSystemId:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {
        "ExternalSystemId": "F628AFC8-532C-406C-BDE6-A782F03508D5",
        "BusinessDescription": "Here is an updated business description!",
    }
]
responses = dc.insert_data(
    object_api_name="Company",
    data=to_send,
    use_dealcloud_ids=False,
    lookup_column="ExternalSystemId"
)

Upsert Data

Upserting data is a method that combines insert and update. If a record exists and can be found by EntryId or a lookup_column then it will be updated, if not, it will be created new.

Example, to upsert data in the "Company" object from a list:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {
        "EntryId": 234567,
        "CompanyName": "Test Company 1",
        "CompanyType": 12345,
        "BusinessDescription": "Here is an updated business description",
        "Sector": 14321,
    },
    {
        "CompanyName": "Test Company 2",
        "CompanyType": 12345,
        "BusinessDescription": "Here is a business description for a new company",
        "Sector": 14321,
    },
]
responses = dc.insert_data("Company", to_send)

If successful, responses will contain the below, note that the "Test Company 2" now has an EntryId as it has been created:

[
   {
    "EntryId": 234567,
    "CompanyName": "Test Company 1",
    "CompanyType": 12345,
    "BusinessDescription": "Here is an updated business description",
    "Sector": 14321,
   },
   {
    "EntryId": 234568,
    "CompanyName": "Test Company 2",
    "CompanyType": 12345,
    "BusinessDescription": "Here is a business description for a new company",
    "Sector": 14321,
   },
]

Understanding Errors

The data operation methods raise errors in a number of ways, when use_dealcloud_ids is False, errors are fed back from the API into the data returned from the function. Example, if the sector 12345 does not exist:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
to_send = [
    {'CompanyName': 'TEST_UPDATED', "Sector": 12345}
]
responses = dc.insert_data("WJ_Company", to_send)

responses will contain.

[{'EntryId': -1,
  'CompanyName': 'TEST_UPDATED',
  'Sector': None,
  'Errors': [{'field': 'Sector',
    'code': 5006,
    'description': 'One or more referenced entries are not valid for this field.'}]}]

!!! info In this case, the record has not been created in the site. When multiple records are trying to be made and any one fails in this way, none of the records in that group will be created. When a large volume of records are being created, DealCloud handles breaking the large volume of data into multiple pages. In this case, where there is an error on a record in a page, all of the records in that page will not be created/updated.

When use_dealcloud_ids is True, and lookup values are used, the create/update/upsert methods are less prone to "failing" errors. This is because, when a value is being resolved through a lookup_column, a value that cannot be found will result in an error being logged to the configured logger. However, as the record progresses, the un-resolvable value will simply be blank. The logs appear as below:

For choice fields:

ERROR: Choice mapping error on: {object}, {field}, could not find value: {value}

For reference fields:

ERROR: Reference mapping error on: {object}, {field}, could not find value: {value}

For user fields:

ERROR: User mapping error on: {object}, {field}, could not find value: {value}

When using DealCloud.update_data(), if an EntryId cannot be located, you will see the following error:

ERROR: Primary Key error on object: {object}, record found without 'EntryId' field.

Delete Data

DealCloud.delete_data() provides the ability to delete data from an object by the EntryId. To use it, simply pass the object api name to the object_api_name argument and a list of EntryIds to the records argument.

Example to delete two records with the EntryIds 123451 and 12346:

from dealcloud_sdk import DealCloud
 
dc = DealCloud.from_yaml("path/to/yaml_config_file.json")
 
response = dc.delete_data(
    object_api_name="Company",
    records=[12345, 12346]
)

response will contain:

[
   {'entryId': 12345, 'fieldId': 0, 'rowId': 4935650, 'isNoData': False},
   {'entryId': 12346, 'fieldId': 0, 'rowId': 4935650, 'isNoData': False},
]