Queries (pull)

For when you want to pull data from Pitchly

Endpoints

databases

Gets all the databases that belong to an organization. If docDbOnly is true, then only databases that have the Documents app installed will be returned.

You can get the organization ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:

https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>

query databases($secretKey: String!, $organizationId: ID!, $docDbOnly: Boolean) {
  databases(secretKey: $secretKey, organizationId: $organizationId, docDbOnly: $docDbOnly) {
    _id
    name
    fields {
      _id
      name
      type
      primary
      required
      restrict
      database
    }
    filters {
      _id
      name
      filter
    }
    templates {
      path
      name
    }
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "organizationId": <ORGANIZATION_ID>,
  "docDbOnly": Boolean
}

In the Body of the request:

{
  "operationName": "databases",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "organizationId": <ORGANIZATION_ID>,
    "docDbOnly": Boolean
  },
  "query": "query databases($secretKey: String!, $organizationId: ID!, $docDbOnly: Boolean) {\n  databases(secretKey: $secretKey, organizationId: $organizationId, docDbOnly: $docDbOnly) {\n    _id\n    name\n    fields {\n      _id\n      name\n      type\n      primary\n      required\n      restrict\n      database\n    }\n  }\n}\n"
}

With GraphQL, you can narrow down the properties you wish to receive in the result, such as only the fields in the tables, or even just the name and _id. filters and templates, however, may require some more explanation.

filters is an array of "Saved Filters" that have been saved in your Pitchly account in the left-hand pane to a database. It returns the name of the filter and the filter object that you can pass directly to the data query to load results matching that filter.

templates is an array of templates that live in the Documents app for this database. The path of the template is a string value containing the stringified path to the template in the directory structure in the Documents app, where each folder is separated by " > ". For example, a template called "Foo" in the folder "Bar" would read Bar > Foo. If the template is at the root directory, it would just read, Foo. The name property simply contains the name of the template, the equivalent of the last name shown in the path.

Example result

Returns an array of databases in the specified organization. Each database has an _id, name, and an array of fields. See the meaning of each field's properties here, and more information about field types.

{
  "data": {
    "databases": [
      {
        "_id": "uJL9XrebupC7eWvM3",
        "name": "Clients",
        "fields": [
          {
            "_id": "xEfek48BRpD3kAksh",
            "name": "Name",
            "type": "string",
            "primary": true,
            "required": null,
            "restrict": null,
            "database": null
          }
        ],
        "filters": [],
        "templates": [
          {
            "name": "(0) CloseDate Logo Counselto Description DealValue",
            "path": "Tombstones > (0) CloseDate Logo Counselto Description DealValue"
          }
        ]
      },
      {
        "_id": "LkfjQ9pq9f48K2vD8",
        "name": "Products",
        "fields": [
          {
            "_id": "ELHqkJAFiyRfzdMsP",
            "name": "Name",
            "type": "string",
            "primary": true,
            "required": null,
            "restrict": null,
            "database": null
          },
          {
            "_id": "MBadxhiHvf78yDpPS",
            "name": "In Stock",
            "type": "boolean",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          }
        ],
        "filters": [],
        "templates": []
      },
      {
        "_id": "Ccx2Fzva28RshjFpr",
        "name": "Orders",
        "fields": [
          {
            "_id": "Nb6uqwKdxh3BFX3De",
            "name": "ID",
            "type": "string",
            "primary": true,
            "required": true,
            "restrict": null,
            "database": null
          },
          {
            "_id": "RyM8SyrYrceRsrLJd",
            "name": "Client",
            "type": "ref",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": "uJL9XrebupC7eWvM3"
          },
          {
            "_id": "g5WPqJugq9o3fQPXH",
            "name": "Products",
            "type": "refMultiple",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": "LkfjQ9pq9f48K2vD8"
          },
          {
            "_id": "Jex4vcwa9HZ4GipXg",
            "name": "Photo",
            "type": "attachment",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          },
          {
            "_id": "pnR2Mnqfjdj9vQG8e",
            "name": "Amount",
            "type": "currency",
            "primary": null,
            "required": true,
            "restrict": null,
            "database": null
          },
          {
            "_id": "2ybyZfu6H6KNfGbBt",
            "name": "Location",
            "type": "enum",
            "primary": null,
            "required": null,
            "restrict": [
              "Houston",
              "Atlanta",
              "Las Vegas",
              "Minneapolis",
              "New York"
            ],
            "database": null
          },
          {
            "_id": "q6iD8LCHSjhJusLSd",
            "name": "Date",
            "type": "date",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          },
          {
            "_id": "JYszfW2jpLeTck8mW",
            "name": "Department",
            "type": "enumTags",
            "primary": null,
            "required": null,
            "restrict": [
              "Electronics",
              "Cosmetics",
              "Cookware",
              "Hardware",
              "Home"
            ],
            "database": null
          },
          {
            "_id": "hMQKzyxRBxK4sGN5M",
            "name": "Quantity",
            "type": "number",
            "primary": null,
            "required": null,
            "restrict": null,
            "database": null
          }
        ],
        "filters": [],
        "templates": []
      }
    ]
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

database

Gets information about a specific database, including its fields.

You can get the database ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:

https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>

query database($secretKey: String!, $id: ID!) {
  database(secretKey: $secretKey, id: $id) {
    _id
    name
    fields {
      _id
      name
      type
      primary
      required
      restrict
      database
    }
    filters {
      _id
      name
      filter
    }
    templates {
      path
      name
    }
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "id": <DATABASE_ID>
}

In the Body of the request:

{
  "operationName": "database",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "id": <DATABASE_ID>
  },
  "query": "query database($secretKey: String!, $id: ID!) {\n  database(secretKey: $secretKey, id: $id) {\n    _id\n    name\n    fields {\n      _id\n      name\n      type\n      primary\n      required\n      restrict\n      database\n    }\n  }\n}\n"
}

Example result

Returns the specified database. The database has an _id, name, and an array of fields. See the meaning of each field's properties here, and more information about field types.

{
  "data": {
    "database": {
      "_id": "Ccx2Fzva28RshjFpr",
      "name": "Orders",
      "fields": [
        {
          "_id": "Nb6uqwKdxh3BFX3De",
          "name": "ID",
          "type": "string",
          "primary": true,
          "required": true,
          "restrict": null,
          "database": null
        },
        {
          "_id": "RyM8SyrYrceRsrLJd",
          "name": "Client",
          "type": "ref",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": "uJL9XrebupC7eWvM3"
        },
        {
          "_id": "g5WPqJugq9o3fQPXH",
          "name": "Products",
          "type": "refMultiple",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": "LkfjQ9pq9f48K2vD8"
        },
        {
          "_id": "Jex4vcwa9HZ4GipXg",
          "name": "Photo",
          "type": "attachment",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": null
        },
        {
          "_id": "pnR2Mnqfjdj9vQG8e",
          "name": "Amount",
          "type": "currency",
          "primary": null,
          "required": true,
          "restrict": null,
          "database": null
        },
        {
          "_id": "2ybyZfu6H6KNfGbBt",
          "name": "Location",
          "type": "enum",
          "primary": null,
          "required": null,
          "restrict": [
            "Houston",
            "Atlanta",
            "Las Vegas",
            "Minneapolis",
            "New York"
          ],
          "database": null
        },
        {
          "_id": "q6iD8LCHSjhJusLSd",
          "name": "Date",
          "type": "date",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": null
        },
        {
          "_id": "JYszfW2jpLeTck8mW",
          "name": "Department",
          "type": "enumTags",
          "primary": null,
          "required": null,
          "restrict": [
            "Electronics",
            "Cosmetics",
            "Cookware",
            "Hardware",
            "Home"
          ],
          "database": null
        },
        {
          "_id": "hMQKzyxRBxK4sGN5M",
          "name": "Quantity",
          "type": "number",
          "primary": null,
          "required": null,
          "restrict": null,
          "database": null
        }
      ],
      "filters": [],
      "templates": []
    }
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

data

Gets data from a database.

You can get the database ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:

https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>

query data($secretKey: String!, $databaseId: ID!, $filter: JSON, $sort: JSON, $limit: Int, $skip: Int, $in: [String], $fields: [String], $removedAt: Boolean, $search: String, $modifiedSince: String) {
  data(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, sort: $sort, limit: $limit, skip: $skip, in: $in, fields: $fields, removedAt: $removedAt, search: $search, modifiedSince: $modifiedSince) {
    _id
    cols {
      fieldId
      value
      rows {
        _id
        cols {
          fieldId
          value
        }
      }      
    }
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>
}

In the Body of the request:

{
  "operationName": "data",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>
  },
  "query": "query data($secretKey: String!, $databaseId: ID!, $filter: JSON, $sort: JSON, $limit: Int, $skip: Int, $in: [String], $fields: [String], $removedAt: Boolean, $search: String, $modifiedSince: String) {\n  data(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, sort: $sort, limit: $limit, skip: $skip, in: $in, fields: $fields, removedAt: $removedAt, search: $search, modifiedSince: $modifiedSince) {\n    _id\n    cols {\n      fieldId\n      value\n    }\n  }\n}\n"
}

Example result

Returns an array of rows. Each row has an _id and cols for each column/field of data in the row. The data in each column can differ based on field type, but generally those variations will take place within the value object. When value is an object, it is guaranteed to at least always have a val property.

When a value is empty, value may either be null (indicates "not set"), or value may be an object where val itself is null (indicates "intentionally empty"). Read more about empty values.

You can also find all of the expected value object formats for each data type here.

{
  "data": {
    "data": [
      {
        "_id": "a5hzm9xChLDKrxneG",
        "cols": [
          {
            "fieldId": "Nb6uqwKdxh3BFX3De",
            "value": {
              "val": "order-2883"
            }
          },
          {
            "fieldId": "RyM8SyrYrceRsrLJd",
            "value": {
              "val": "aobSkpbmx5mrpKc8Y",
              "_label": "Sam Arthur"
            }
          },
          {
            "fieldId": "g5WPqJugq9o3fQPXH",
            "value": {
              "val": [
                "cj659qeYY9LuXAJNB",
                "fHLNMGYyj46cRbFyg",
                "xRfACYg7QmBvTykqW"
              ],
              "_label": [
                "Ceramic Bowl",
                "Coffee Maker",
                "Coffee Mug"
              ]
            }
          },
          {
            "fieldId": "Jex4vcwa9HZ4GipXg",
            "value": {
              "val": "https://ply-files.s3.amazonaws.com/T2qgNJQBz6EZgxGc3i/d1a4189e-9389-4aaf-a62a-c475c278b77c.jpeg",
              "type": "image/jpeg",
              "size": 28813
            }
          },
          {
            "fieldId": "pnR2Mnqfjdj9vQG8e",
            "value": {
              "val": 57,
              "currency": "USD"
            }
          },
          {
            "fieldId": "2ybyZfu6H6KNfGbBt",
            "value": {
              "val": "New York"
            }
          },
          {
            "fieldId": "q6iD8LCHSjhJusLSd",
            "value": {
              "val": "2019-06-27T00:00:00.000Z"
            }
          },
          {
            "fieldId": "JYszfW2jpLeTck8mW",
            "value": {
              "val": [
                "Cookware",
                "Home"
              ]
            }
          },
          {
            "fieldId": "hMQKzyxRBxK4sGN5M",
            "value": {
              "val": 2
            }
          }
        ]
      }
    ]
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

Advanced data queries

The sample query above may work fine for very simple queries, but the data endpoint allows us to pass many more options to query very specific information. Below is a list of optional variables the data endpoint additionally accepts, beside a secretKey and databaseId.

Examples

Example 1 (simple)

Get rows where ID equals "order-2883"

{
  fields: {
    "Nb6uqwKdxh3BFX3De": { // ID field
      filters: [
        {
          by: "is",
          value: "order-2883"
        }
      ],
      conjunction: "and"
    }
  },
  conjunction: "and"
}

Example 2 (relational)

Get rows in Orders where the Client's Name either contains "Michael" or "John". Note that the Client is a reference to the "Clients" database, which contains a field called "Name". Pitchly is unique in that you can filter data relationally, not unlike a traditional SQL database. The below pattern is also recursive, so you can filter across more than one table or relationship.

{
  fields: {
    "RyM8SyrYrceRsrLJd": { // Client field in "Orders"
      fields: {
        "xEfek48BRpD3kAksh": { // Name field in "Clients"
          filters: [
            {
              by: "contains",
              value: "Michael"
            },
            {
              by: "contains",
              value: "John"
            }
          ],
          conjunction: "or"
        }
      },
      conjunction: "and",
      include: {
        // Switch these to true to include rows where the Client
        // field itself is either empty, not set, or has any value.
        // This is different from filtering by a specific field in
        // the "Clients" database.
        "is-empty": false,
        "is-not-set": false,
        "has-any-value": false
      }
    }
  },
  conjunction: "and"
}

Since different field types accept different filter parameters, here is a list of all the possible filter parameters for each field type. They can be used within the filters array.

Conjunctions can be used to perform AND or OR operations at each step of a filter. Different logical conjunctions can be used on a per-field level and on a per-filter level. Each field can contain multiple filters to perform different logic within the same field.

Sort by Order Amount in descending order, and then by ID in ascending order, yielding the highest priced orders first, and for orders of the same amount, sorted by the order ID alphabetically.

{ "pnR2Mnqfjdj9vQG8e": -1, "Nb6uqwKdxh3BFX3De": 1 }

1 = ascending; -1 = descending; order of fields determines sort priority

Only return at most the two rows identified by the following row IDs.

["a5hzm9xChLDKrxneG", "Q42ggoNpiWzhJ9GDZ"]

This can be used in combination with filter, limit, etc. to further isolate the desired rows. If a row is specified using in but it doesn't pass a given filter, or it exceeds the max number of rows in the result set (specified by limit), the row will be excluded from the result.

If one of the row IDs does not exist in the database, an error will not be thrown. The result will not include any rows that are not actually in the database.

For each row in Orders, only return the ID and Client fields.

["Nb6uqwKdxh3BFX3De", "RyM8SyrYrceRsrLJd"]

{ "search": "deal|money" }

No more than 100 rows can be returned at a time. To return more, you should use skip and limit to paginate through results.

By default, all data in the database will be returned (up to the first 100 rows) in the default sort order, which is currently the time the row was created in descending order (most recent first).

content

Gets images and PPT assets for each row in a database.

You can get the database ID from the URL address bar while logged into Pitchly. When inside the account, the URL will follow the pattern:

https://v2.pitchly.net/t/<ORGANIZATION_ID>/d/<DATABASE_ID>/a/<APP_ID>

query content($databaseId: ID!, $secretKey: String!, $filter: JSON, $limit: Int, $skip: Int, $in: [String], $sort: JSON, $search: String, $templatePaths: [String], $modifiedSince: String) {
  content (databaseId: $databaseId, secretKey: $secretKey, filter: $filter, limit: $limit, skip: $skip, in: $in, sort: $sort, search: $search, templatePaths: $templatePaths, modifiedSince: $modifiedSince) {
    _id
    templates {
      name
      path
      pptxUrl
      imageUrl  
      default          
    }
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>
}

In the Body of the request:

{
  "operationName": "content",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>
  },
  "query": "query content($databaseId: ID!, $secretKey: String!, $filter: JSON, $limit: Int, $skip: Int, $in: [String], $sort: JSON, $search: String, $templatePaths: [String], $modifiedSince: String) {\n  content(\n    databaseId: $databaseId\n    secretKey: $secretKey\n    filter: $filter\n    limit: $limit\n    skip: $skip\n    in: $in\n    sort: $sort\n    search: $search\n    templatePaths: $templatePaths\n    modifiedSince: $modifiedSince\n  ) {\n    _id\n    templates {\n      name\n      path\n      pptxUrl\n      imageUrl\n      default\n    }\n  }\n}\n"
}

Example result

Returns an array of rows. Each row has an _id and an array oftemplates for each row of data.

{
    "data": {
        "content": [
            {
                "_id": "6MMrY9Du9ShrFYwhb",
                "templates": [
                    {
                        "name": "test",
                        "path": "Folder 1 > Folder 1-1 > Folder 1-1 > Folder 1-1-2 > test",
                        "pptxUrl": "https://tombstones.pitchly.net/content/ppt?token=wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "imageUrl": "https://v2.pitchly.net/content/wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "default": false
                    },
                    {
                        "name": "really nested",
                        "path": "Folder 1 > Folder 1-1 > Folder 1-1 > really nested",
                        "pptxUrl": "https://tombstones.pitchly.net/content/ppt?token=wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "imageUrl": "https://v2.pitchly.net/content/wmCYMQLs0cs_bx01rl2UkUrYTkG1R10y7WdmqYSUHOdR-K0PcpPJG9ZHPuhpkERlw89NH9Jfqob2Au82dJwC4eknDD_sc4znPNvw3_0Y-UqNCt2cFX65Crugp0vGbex5ICVXLvdgsAGK11vL7optqPK6HOtgb54NGcUoVz2iBJGQ9AlotyawaXMji2xylVh1m4OPgxsdXVzOE4eCnXdXUmNS5kEKa-gp7qSl5bqMHWM",
                        "default": false
                    }
                ]
            }
        ]
    }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

Advanced content queries

The sample query above may work fine for very simple queries, but the content endpoint allows us to pass many more options to query very specific information. Below is a list of optional variables the content endpoint additionally accepts, beside a secretKey and databaseId.

Examples

Example 1 (simple)

Get rows where ID equals "order-2883"

{
  fields: {
    "Nb6uqwKdxh3BFX3De": { // ID field
      filters: [
        {
          by: "is",
          value: "order-2883"
        }
      ],
      conjunction: "and"
    }
  },
  conjunction: "and"
}

Example 2 (relational)

{
  fields: {
    "RyM8SyrYrceRsrLJd": { // Client field in "Orders"
      fields: {
        "xEfek48BRpD3kAksh": { // Name field in "Clients"
          filters: [
            {
              by: "contains",
              value: "Michael"
            },
            {
              by: "contains",
              value: "John"
            }
          ],
          conjunction: "or"
        }
      },
      conjunction: "and",
      include: {
        // Switch these to true to include rows where the Client
        // field itself is either empty, not set, or has any value.
        // This is different from filtering by a specific field in
        // the "Clients" database.
        "is-empty": false,
        "is-not-set": false,
        "has-any-value": false
      }
    }
  },
  conjunction: "and"
}

Since different field types accept different filter parameters, here is a list of all the possible filter parameters for each field type. They can be used within the filters array.

Sort by Order Amount in descending order, and then by ID in ascending order, yielding the highest priced orders first, and for orders of the same amount, sorted by the order ID alphabetically.

{ "pnR2Mnqfjdj9vQG8e": -1, "Nb6uqwKdxh3BFX3De": 1 }

1 = ascending; -1 = descending; order of fields determines sort priority

Only return at most the two rows identified by the following row IDs.

["a5hzm9xChLDKrxneG", "Q42ggoNpiWzhJ9GDZ"]

If one of the row IDs does not exist in the database, an error will not be thrown. The result will not include any rows that are not actually in the database.

{ "search": "deal|money" }

No more than 100 rows can be returned at a time. To return more, you should use skip and limit to paginate through results.

By default, all data in the database will be returned (up to the first 100 rows) in the default sort order, which is currently the time the row was created in descending order (m

dataCount

Gets the total number of rows in a database, optionally filtered.

The optional filter and in parameters function the same as they do with the data endpoint.

query dataCount($secretKey: String!, $databaseId: ID!, $filter: JSON, $in: [String], $removedAt: Boolean, $search: String, $modifiedSince: String) {
  dataCount(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, in: $in, removedAt: $removedAt, search: $search, modifiedSince: $modifiedSince) {
    count
  }
}

Variables:

{
  "secretKey": <SECRET_KEY>,
  "databaseId": <DATABASE_ID>
}

In the Body of the request:

{
  "operationName": "dataCount",
  "variables": {
    "secretKey": <SECRET_KEY>,
    "databaseId": <DATABASE_ID>
  },
  "query": "query dataCount($secretKey: String!, $databaseId: ID!, $filter: JSON, $in: [String], $removedAt: Boolean, $search: String, $modifiedSince: String) {\n  dataCount(secretKey: $secretKey, databaseId: $databaseId, filter: $filter, in: $in, removedAt: $removedAt, search: $search, modifiedSince: $modifiedSince) {\n    count\n  }\n}\n"
}

Example result

Returns the number of rows in the database, or if filter or in are specified, the number of rows in the result, not subject to a limit.

{
  "data": {
    "dataCount": {
      "count": 1011
    }
  }
}

{
  "errors": [
    {
      "message": "An error has occurred",
      "name": "GQLError",
      "time_thrown": "2019-11-03T20:26:12.651Z",
      "data": {
        "error": "not-authorized",
        "reason": "This app does not have permission to perform this action."
      }
    }
  ],
  "data": null
}

Reference

Field properties

When getting information about the fields in a database using either the database or databases endpoints, the following properties will be returned for each field. A description of each property can be found below.

Data types

Each field in a database can be one of the following types. Note that the programmatic name is different than the user-facing name for each type. The programmatic name is used throughout Pitchly's APIs, while the user-facing name is what the user sees through Pitchly's interface. Examples are representative of the value object returned for each field in each row when getting data via the data endpoint.

Note on reference fields

The row IDs that are saved in reference fields refer to Pitchly's internal and unchangeable row ID for every row. This ID is globally unique to Pitchly, automatically generated, and is not readily exposed to users or defined by users of Pitchly.

Remember that these row IDs are present in the database being referenced by the ref or refMultiple field containing this value, specified by the database property of the ref field.

_label is a convenient display-friendly version of each row being referenced based on the value of the primary field in the referenced row, returned as a string. If no primary field is set in the referenced database, _label will become the row ID itself.

Note on empty values

Empty values can be represented one of two ways:

{ fieldId: "...", value: null }
{ fieldId: "...", value: { val: null } }

The first is when the value is considered "not set," which implies the value was empty when inserted into Pitchly and has never been changed. The second is when the value is "intentionally empty," which means it was set to empty intentionally. Once a value is made "intentionally empty," it cannot become "not set" again.

Note that in the second scenario, val will always be null regardless of the data type. If no values are selected in a Dropdown Multiple, for example, null will be returned instead of an empty array. Empty strings will also be automatically converted to null. This way, it is not necessary to know the data type of every field to check whether a value is empty.

Every field type can also potentially be empty, including boolean fields. Required fields may also contain empty values under certain circumstances.

Filter parameters

Depending on the data type of a field, different filter options are available. At a filter's core is an object usually consisting of a by property, and usually a value or a slight varation thereof. Below is a list of possible filters for each data type.

string / textBlock

{ by: "is", value: "foo" }
{ by: "is-not", value: "foo" }
{ by: "starts-with", value: "foo" }
{ by: "ends-with", value: "foo" }
{ by: "contains", value: "foo" }
{ by: "does-not-contain", value: "foo" }
{ by: "has-any-value" }
{ by: "is-empty" }
{ by: "is-not-set" }

Currently, starts-with, ends-with, contains, and does-not-contain are case insensitive, while is and is-not are case sensitive.

number

{ by: "is", value: 5 }
{ by: "is-not", value: 5 }
{ by: "is-more-than", value: 5 }
{ by: "is-less-than", value: 5 }
{ by: "is-between", value: { val1: 5, val2: 10 } }
{ by: "has-any-value" }
{ by: "is-empty" }
{ by: "is-not-set" }

boolean

{ by: "is-true" }
{ by: "is-false" }
{ by: "has-any-value" }
{ by: "is-empty" }
{ by: "is-not-set" }

date

{ by: "on", value: "2020-01-23T00:00:00.000Z" }
{ by: "after", value: "2020-01-23T00:00:00.000Z" }
{ by: "before", value: "2020-01-23T00:00:00.000Z" }
{ by: "between", value: { val1: "2020-01-23T00:00:00.000Z", val2: "2020-02-23T00:00:00.000Z" } }
{ by: "has-any-value" }
{ by: "is-empty" }
{ by: "is-not-set" }

currency

{ by: "is", value: 5, currency: "USD" }
{ by: "is-not", value: 5, currency: "USD" }
{ by: "is-more-than", value: 5, currency: "USD" }
{ by: "is-less-than", value: 5, currency: "USD" }
{ by: "is-between", value: { val1: 5, val2: 10 }, currency: "USD" }
{ by: "has-any-value" }
{ by: "is-empty" }
{ by: "is-not-set" }

See all currency types

attachment

{ by: "has-any-value" }
{ by: "is-empty" }
{ by: "is-not-set" }

enum / enumTags

{ values: ["Houston", "Atlanta"], otherValues: ["is-empty", "is-not-set"] }

The above example will return results where Location contains either Houston, Atlanta, the field is empty, or it's not set.

Because Pitchly allows you to filter data relationally across tables, there is no need to filter directly against a ref or refMultiple field. That's why you won't find those here.

PreviousEndpoints NextMutations (push)

Last updated 5 months ago