Create a new formula column under a specified sub-module

Open in ChatGPT Open in ChatGPT to ask questions about this page
Open in Claude Open in Claude to ask questions about this page
Copy as MarkdownCopy this page as markdown to use with AI assistants
View as Markdown Open this page as markdown in a new tab

Creates a new formula column under a specified sub-module. A formula column is a computed/derived column that works like a CASE-WHEN expression — it evaluates conditions (criteria) against existing report columns and returns a configured value for each match, with a default fallback value. The response includes the newly created column's ID, resolved data type, groupability, and available aggregation options.

Request URL

https://{serverurl}/dcapi/reports/customReports/formulaColumn

Scope

DesktopCentralCloud.CustomReport.CREATECopied!

Header

Authorization: Zoho-oauthtoken d92d4xxxxxxxxxxxxx15f52

Request Parameters

- Request Headers

Content-TypestringMandatory
application/formulaColumn.v1+jsonapplication/formulaColumn.v1+jsonCopied!
AcceptstringMandatory
application/columnCreationStatus.v1+jsonapplication/columnCreationStatus.v1+jsonCopied!

- Request Body

application/json
JSON Object
Hide Sub-Attributes
subModuleNamestringMandatory

Sub-module name the formula column belongs to. Must be one of: Computer, Hardware, Software (case-insensitive)

columnNamestringMandatory

Unique display name for the formula column. Alphanumeric only (A-Z, a-z, 0-9). Max 20 characters

defaultValuestringMandatory

Default value returned when no condition matches (the ELSE branch). Alphanumeric only. Max 100 characters

columnDetailsJSON ArrayMandatory

Array of condition objects (1-5). Each defines a CASE-WHEN branch with criteria and result value

Show Sub-Attributes
JSON Object
Show Sub-Attributes
valuestringMandatory

Result value when this condition's criteria matches. Alphanumeric only. Max 100 characters

criteriaJSON ArrayMandatory

Array of criteria filter objects for this condition (1-30 per condition)

Show Sub-Attributes
JSON Object
Show Sub-Attributes
columnIDstringMandatory

Column ID to evaluate the criteria against. Fetch from Get Available Columns

comparatorstringMandatory

Comparison operator (e.g., equal, not equal, contains, greater than)

searchValuearrayMandatory

Array of string values to compare against

logicalOperatorstringMandatory

Logical operator (AND or OR) for combining with previous criteria

criteriaPatternstringMandatory

Logical expression combining criteria (e.g., 1, 1 AND 2, (1 OR 2) AND 3)

Sample Request

Curl
Java
Python
Deluge
PowerShell
Copied!
curl --request POST \
  --url https://appdomains/dcapi/reports/customReports/formulaColumn \
  --header 'Accept: application/columnCreationStatus.v1+json' \
  --header 'Authorization: Zoho-oauthtoken  d92d4xxxxxxxxxxxxx15f52' \
  --header 'Content-Type: application/formulaColumn.v1+json' \
  --data '{"defaultValue":"NotEligible","columnDetails":[{"criteria":[{"comparator":"equal","logicalOperator":"AND","columnID":"42","searchValue":["Windows 11"]}],"value":"Eligible","criteriaPattern":"1"}],"subModuleName":"Computer","columnName":"Windows11Eligibility"}'

Sample Request Body

Create a CHAR-type formula column with one condition

Copied!
  {
    "defaultValue": "NotEligible",
    "columnDetails": [
      {
        "criteria": [
          {
            "comparator": "equal",
            "logicalOperator": "AND",
            "columnID": "42",
            "searchValue": [
              "Windows 11"
            ]
          }
        ],
        "value": "Eligible",
        "criteriaPattern": "1"
      }
    ],
    "subModuleName": "Computer",
    "columnName": "Windows11Eligibility"
  }
                
Show full

Create a formula column with multiple CASE-WHEN conditions

Copied!
  {
    "defaultValue": "Other",
    "columnDetails": [
      {
        "criteria": [
          {
            "comparator": "contains",
            "logicalOperator": "AND",
            "columnID": "103",
            "searchValue": [
              "Windows"
            ]
          }
        ],
        "value": "Windows",
        "criteriaPattern": "1"
      },
      {
        "criteria": [
          {
            "comparator": "contains",
            "logicalOperator": "AND",
            "columnID": "103",
            "searchValue": [
              "Linux"
            ]
          },
          {
            "comparator": "contains",
            "logicalOperator": "OR",
            "columnID": "103",
            "searchValue": [
              "Ubuntu"
            ]
          }
        ],
        "value": "Linux",
        "criteriaPattern": "1 OR 2"
      }
    ],
    "subModuleName": "Computer",
    "columnName": "OSCategory"
  }
                
Show full

Response Parameters

- HTTP code 200

Response Body - application/json
JSON Object
Hide Sub-Attributes
columnIDlong

Unique identifier of the newly created formula column

displayNamestring

Display name of the formula column (echoed from columnName in the request)

dataTypestring

Resolved data type of the column based on all condition values and the default value. Possible values: CHAR, BIGINT, BOOLEAN

groupableboolean

Whether the column can be used as a group-by column in custom reports. True for CHAR and BIGINT types, false for BOOLEAN

aggOptionsJSON Array

List of aggregation functions available for this column. Varies by dataType: CHAR gets ACTUAL_VALUE, COUNT, STRING_AGG; BIGINT gets ACTUAL_VALUE, COUNT, SUM, MIN, MAX, AVG

Show Sub-Attributes
JSON Object
Show Sub-Attributes
displayValuestring

Human-readable display name of the aggregation function (e.g., Actual Value, Count, String Aggregate)

methodstring

Aggregation method identifier: ACTUAL_VALUE, COUNT, STRING_AGG, SUM, MIN, MAX, AVG

- HTTP code 400

Response Body - application/json
JSON Object
Hide Sub-Attributes
errorCodestring

Error code for validation failure: 400 = formula column name already exists, condition limit exceeded (max 5), criteria limit exceeded (max 30), or unknown/inaccessible column ID in criteria

errorMessagestring

Human-readable message describing the specific validation failure

- HTTP code 403

Response Body - application/json
JSON Object
Hide Sub-Attributes
errorCodelong

Error code indicating insufficient permissions

errorMsgstring

Message indicating insufficient privileges to access this resource

- HTTP code 412

Response Body - application/json
JSON Object
Hide Sub-Attributes
errorCodestring

Precondition failed error code: 412 = maximum formula column limit (100) reached across the organization

errorMessagestring

Message indicating the formula column creation limit has been reached

- HTTP code 417

Response Body - application/json
JSON Object
Hide Sub-Attributes
errorCodestring

Expectation failed error code: Custom Report V2 is not enabled

errorMessagestring

Message indicating Custom Report V2 feature is not active

- HTTP code 429

Response Body - application/json
JSON Object
Hide Sub-Attributes
errorCodelong

Rate limit error code returned when the API call reached threshold

errorMsgstring

Rate limit exceeded message with retry guidance

- HTTP code 500

Response Body - application/json
JSON Object
Hide Sub-Attributes
errorCodestring

Internal error code: unexpected failure during formula column creation (e.g., database exception)

errorMessagestring

Detailed error description for the internal server error during formula column creation

Possible Response Codes

200HTTP code
400HTTP code
403HTTP code
412HTTP code
417HTTP code
429HTTP code
500HTTP code

Sample Response: HTTP 200

Newly created CHAR-type formula column with string aggregation options

Copied!
  {
    "columnID": "1",
    "displayName": "Windows11Eligibility",
    "dataType": "CHAR",
    "groupable": true,
    "aggOptions": [
      {
        "displayValue": "Actual Value",
        "method": "ACTUAL_VALUE"
      },
      {
        "displayValue": "Count",
        "method": "COUNT"
      },
      {
        "displayValue": "String Aggregate",
        "method": "STRING_AGG"
      }
    ]
  }
                
Show full

Newly created BIGINT-type formula column with numeric aggregation options

Copied!
  {
    "columnID": "2",
    "displayName": "ScoreCategory",
    "dataType": "BIGINT",
    "groupable": true,
    "aggOptions": [
      {
        "displayValue": "Actual Value",
        "method": "ACTUAL_VALUE"
      },
      {
        "displayValue": "Count",
        "method": "COUNT"
      },
      {
        "displayValue": "Sum",
        "method": "SUM"
      },
      {
        "displayValue": "Minimum",
        "method": "MIN"
      },
      {
        "displayValue": "Maximum",
        "method": "MAX"
      },
      {
        "displayValue": "Average",
        "method": "AVG"
      }
    ]
  }
                
Show full

Sample Response: HTTP 400

Formula column name already exists

Copied!
  {
    "errorMessage": "Formula column name already exists",
    "errorCode": "400"
  }
                
Show full

Number of conditions exceeds the maximum limit

Copied!
  {
    "errorMessage": "Number of formula condition limit(5) exceeded",
    "errorCode": "400"
  }
                
Show full

Sample Response: HTTP 403

User does not have the required role

Copied!
  {
    "errorMessage": "User does not have the required role to access this resource",
    "errorCode": "403"
  }
                
Show full

Sample Response: HTTP 412

Maximum formula column limit (100) reached

Copied!
  {
    "errorMessage": "Maximum number of formula column limit(100) reached",
    "errorCode": "412"
  }
                
Show full

Sample Response: HTTP 417

Custom Report V2 is not enabled

Copied!
  {
    "errorMessage": "Custom Report V2 is not enabled",
    "errorCode": "417"
  }
                
Show full

Sample Response: HTTP 429

Rate limit exceeded

Copied!
  {
    "errorMessage": "Rate limit exceeded. Please retry after some time",
    "errorCode": "429"
  }
                
Show full

Sample Response: HTTP 500

Unexpected failure during formula column creation

Copied!
  {
    "errorMessage": "Internal server error while creating formula column",
    "errorCode": "500"
  }
                
Show full

Duration: 1 minute | Threshold: 30 | Lock period: 5 minutes

Duration - Time window for the threshold.
Threshold - Number of API calls allowed within the specified duration.
Lock Period - Wait time before consecutive API requests.