Get all the databases that belong to an organization.
Get information about a specific database.
Get data rows from a database.
Get the number of rows in a result.
Get images and PPT asset for selected rows.
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:
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.
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.
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.
{
"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.
Variable
Type
Description
filter
object
Narrows rows by specifying filter criteria
sort
object
Sorts rows in ascending or descending order by field ID, in the order specified
limit
number
The maximum number of rows to return (default = 100)
skip
number
Skips the first N number of rows in the result set (useful for pagination)
in
array
Limit resulting rows to the row IDs specified in this array
fields
array
Only return the fields in each row specified in this array of field IDs
removedAt
boolean
Only returns rows in the trash
search
string
Specifies a global search string where , is interpreted as and and | is interpreted as an or
modifiedSince
string
Filters rows to only those created or updated since the date specified. Must be in the following format:
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.
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:
{
"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.
Variable
Type
Description
filter
object
Narrows rows by specifying filter criteria
sort
object
Sorts rows in ascending or descending order by field ID, in the order specified
limit
number
The maximum number of rows to return (default = 100)
skip
number
Skips the first N number of rows in the result set (useful for pagination)
in
array
Limit resulting rows to the row IDs specified in this array
search
string
Specifies a global search string where , is interpreted as and and | is interpreted as an or
templatePaths
array of string
Specifies one or more templatePaths as returned by the database or databases endpoints. The returned rows will exactly match only these paths. Omitting this variable or supplying an empty array will return each row with all available templates.
modifiedSince
string
Filters rows to only those created or updated since the date specified. Must be in the following format:
{
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.
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.
{ "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.
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.
Field property
Return type
Description
_id
string
The ID of the field
name
string
The name of the field
type
string
primary
boolean | null
True if this field is the primary field in the database
required
boolean | null
True if this field is required
restrict
array | null
If field type is enum or enumTags, an array of all possible values
database
string | null
If field type is ref or refMultiple, the ID of the database this field refers to
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.
Field type
User-facing name
Description & example
string
Single-line text
Short single-line text
{ val: "foo" }
textBlock
Multi-line text
Long multi-line text
{ val: "foo\nbar" }
number
Number
Number w/ possible decimal
{ val: 12.5 }
boolean
Yes/No
Binary true/false value
{ val: false }
date
Date
Date in ISO 8601 Extended format (with zeroed UTC time)
{ val: "2020-01-23T00:00:00.000Z" }
currency
Currency
{ val: 50, currency: "USD" }
attachment
Attachment
File attachment of any type (size given in bytes)
{ val: "<URL>", size: 78358, type: "image/jpeg" }
enum
Dropdown
A single value selected from a dropdown of predefined values
{ val: "Las Vegas" }
enumTags
Dropdown multiple
Multiple values selected from a dropdown of predefined values
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.
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.
