The Query Type

Overview

The Query type is the root for all types used when you do a query (read) operation. This type contains the following fields:

Accessing company tables requires specifying a company number. Similarly, accessing a system table requires specifying a customer number. These are the Visma.net identifiers for the company and the customer and can be either found in the Visma.net portal or can be retrieved with a query (see the links mentioned above).

The following images show (partially), side-by-side, the Query_UseCustomer type, containing the system table connections, and the Query_UseCompany type containing company table connections. These are the types for the fields useCustomer and useCompany, that are top level fields of the Query type.

System table	Company table

Understanding connection types

If you look at the types in these images, you will see they are named Query_UseCustomer_ServiceAccount_Connection, Query_UseCompany_Associate_Connection, Query_UseCompany_GeneralLedgedAccount_Connection, etc. These names have the following pattern: <parenttype>_<tablename>_Connection.

Here is how the Query_UseCompany_GeneralLedgedAccount_Connection type looks in the GraphiQL document explorer:

So, what is a connection? To understand this concept, let’s consider the following example. When you want to query for records in this table, you do the following in GraphQL:

  generalLedgerAccount 
  {
    items 
    {
      accountNo
      name
      # more columns
    }
  }

What you can see here is that the table columns are available under a node called items, that is an array of values (in the example above of the type GeneralLedgerAccount). This makes it possible to add add other relevant information for a query, appart from the table columns. This also protects the schema against possible future changes; adding a new field would not break existing queries.

Every type representing a table has two more fiels: totalCount (representing the total number of records in the table that match the filter but ignoring pagination) and pageInfo that provides information to aid in pagination. These topics are addressed later in this document. Therefore, the shape of a table type is the following:

  generalLedgerAccount 
  {
    totalCount
    pageInfo
    {
      hasNextPage
      hasPreviousPage
      startCursor
      endCursor
    }
    items 
    {
      accountNo
      name
      # more columns
    }
  }

A GraphQL type that has this particular shape, with the fields totalCount, pageInfo, and items, is called a connection. The following image shows how the type Query_UseCompany_GeneralLedgerAccount_Connection looks in the GraphiQL Explorer:

The name of this type is prefixed with Query_UseCompany because that is the name of the parent type, and is suffixed with _Connection to indicate this is a connection type.

Understanding column descriptions

When you look at the schema documentation in the Documentation Explorer in GraphiQL, for instance, you will see that each table field has a description. This description contains the SQL column name and the column identifier to help those already familiar with desktop Visma Business programming model that build integrations with the Business NXT API. The description can be preceeded by some tags, such as in [PK] AcNo/1480. These tags have the following meaning:

For insert/update operations, for limited string fields, if the value exceeds the defined maximum size, the value is truncated.

Accessing company tables

The general ledger account that we saw previously is a company table, therefore, when querying for it, you must specify the company first, which is done with the useCompany field. This field’s type is Query_UseCompany and this is the parent for all company table connection types.

In order to query a company table, you must specify its Visma.net identifier. Let’s look at an example:

query
{
  useCompany(no: 9112233)
  {
    generalLedgerAccount
    {
      totalCount
      pageInfo
      {
        hasNextPage
        hasPreviousPage
        startCursor
        endCursor
      }
      items
      {
        accountNo
        name
      }
    }
  }  
}

{
  "data": {
    "useCompany": {
      "generalLedgerAccount": {
        "totalCount": 340,
        "pageInfo": {
          "hasNextPage": false,
          "hasPreviousPage": false,
          "startCursor": "MA==",
          "endCursor": "MzQw"
        },
        "items": [
          {
            "accountNo": 1000,
            "name": "Forskning og utvikling"
          },
          {
            "accountNo": 1020,
            "name": "Konsesjoner"
          },
          {
            "accountNo": 1030,
            "name": "Patenter"
          },

          {
            "accountNo": 9999,
            "name": "Observasjonskonto"
          }
        ]
      }
    }
  }
}

Accessing system tables

Tables such as User and Company, which are system tables, are available from the useCustomer field, whose type is Query_UseCustomer. This is the parent type for all the system table connection types. The connection type name for the User table is Query_UseCustomer_User_Connection and for the Company table is Query_UseCustomer_Company_Connection. The format for all system tables type names is Query_UseCustomer_<tablename>_Connection.

In order to query a system table, you must specify a customer’s Visma.net identifier. Let’s look at an example:

query
{
  useCustomer(no: 1111111)
  {
    user
    {
      items
      {
        userName
      }
    }
  }  
}

{
  "data": {
    "useCustomer": {
      "user": {
        "items": [
          {
            "userName": "john.doe"
          },
          {
            "userName": "jane.doe"
          },
          {
            "userName": "boaty.bcboatface"
          }
        ]
      }
    }
  }
}

Joining tables

Tables, regardless they are system or company tables, have relationships with other tables. These could be either one-to-one or one-to-many. For example, the window (from the Win table) has an one-to-one relation to a folder (from the Fldr table). The field FldrNo (2495) in the Win table, appearing as folderNo in the schema, is a foreign key to the FldrNo (2482) in the Fldr table, appearing as folderNo in the GraphQL schema. The schema makes it possible to easily join these related tables based on these relationships. Such a relationship has an upwards direction and in our GraphQL API are available through nodes prefixed with joinup. The opposite are one-to-many relations, such as from an order to order lines. These have a downwards direction and are available through nodes prefixed with joindown.

An example for such a upwards join to a related table is show below. We fetch for the first three windows, the layout name and the number and name of the related folder.

query
{
  useCustomer(no: 1111111)
  {
    window(first: 3)
    {
      items
      {
        layoutName
        joinup_Folder
        {
          folderNo
          name
        }
      }
    }
  }  
}

{
  "data": {
    "useCustomer": {
      "window": {
        "items": [
          {
            "layoutName": "10201. Purrebrev 1*",
            "joinup_Folder": {
              "folderNo": 18,
              "name": "102. Utskriftsoppsett"
            }
          },
          {
            "layoutName": "10202. Purrebrev 2*",
            "joinup_Folder": {
              "folderNo": 18,
              "name": "102. Utskriftsoppsett"
            }
          },
          {
            "layoutName": "10203. Rentenota 1*",
            "joinup_Folder": {
              "folderNo": 18,
              "name": "102. Utskriftsoppsett"
            }
          }
        ]
      }
    }
  }
}

In this example, the node name was joinup_Folder, there Folder is the name of the table that we joined from Window. Sometimes, a table can be joined from another via multiple relations. An example is the Associate table that can be joined via a customer, supplier, employee, or other relations. In this case, when multiple relations are available the node name has the form joinup_<tablename>_via_<relation>. For the mentioned example, these names are joinup_Associate_via_Customer, joinup_Associate_via_Supplier, joinup_Associate_via_Employee, etc. The example with joinup_Folder is a particular case of this general form, when the table and relation have the same name. For such cases the node name has the form joinup_<tablename> (to avoid namings such as joinup_Folder_via_Folder which is unnecessarily repetitive).

In the next example, the Associate table is joined upwards twice from the Order table, once via the Customer relation, and once via the Employee relation.

{
  useCompany(no:9112233)
  {
    order(first: 2)
    {
      items
      {
        orderNo
        customerNo
        employeeNo

        joinup_Associate_via_Customer
        {
          associateNo
          customerNo
          name
        }
        
        joinup_Associate_via_Employee
        {
          associateNo
          employeeNo
          name
        }
      }
    }
  }
}

{
  "data": {
    "useCompany": {
      "order": {
        "items": [
          {
            "orderNo": 1,
            "customerNo": 10010,
            "employeeNo": 101,
            "joinup_Associate_via_Customer": {
              "associateNo": 11,
              "customerNo": 10010,
              "name": "Nordic Designers"
            },
            "joinup_Associate_via_Employee": {
              "associateNo": 967,
              "employeeNo": 101,
              "name": "Erika Karlson"
            }
          },
          {
            "orderNo": 2,
            "customerNo": 10123,
            "employeeNo": 114,
            "joinup_Associate_via_Customer": {
              "associateNo": 336,
              "customerNo": 10123,
              "name": "Lars Erikson"
            },
            "joinup_Associate_via_Employee": {
              "associateNo": 425,
              "employeeNo": 114,
              "name": "Daniel Gunnarson"
            }
          }
        ]
      }
    }
  }
}

For one-to-many relations, that have a downwards direction from parent to child, the prefixed, as mentioned above is joindown. The rest of the name format is the same, the general form being joindown_<tablename>_via_<relation>. When the table and the relation name are the same, the form is simplified to joindown_<tablename>. However, the relation being one-to-many, the result is not a single record but an array of them. This is modeled in the API through connections. Therefore, a node such as joindown_Translation_via_Window or joindown_OrderLine_via_Order are connections.

We can see this in the following example, where we fetch the layout name of the first three windows, as well as the tag and Norwegian text from the translations table.

query
{
  useCustomer(no: 1111111)
  {
    window(first: 3)
    {
      items
      {
        layoutName
        joindown_Translation_via_Window
        {
          items
          {
            tag
            norwegian
          }
        }
      }
    }
  }  
}

{
  "data": {
    "useCustomer": {
      "window": {
        "items": [
          {
            "layoutName": "10201. Purrebrev 1*",
            "joindown_Translation_via_Window": {
              "items": [
                {
                  "tag": "10201. Purrebrev 1*",
                  "norwegian": "10201. Purrebrev 1*"
                },
                {
                  "tag": "",
                  "norwegian": "10201. Purrebrev 1*"
                }
              ]
            }
          },
          {
            "layoutName": "10202. Purrebrev 2*",
            "joindown_Translation_via_Window": {
              "items": [
                {
                  "tag": "10202. Purrebrev 2*",
                  "norwegian": "10202. Purrebrev 2*"
                },
                {
                  "tag": "",
                  "norwegian": "10202. Purrebrev 2*"
                }
              ]
            }
          },
          {
            "layoutName": "10203. Rentenota 1*",
            "joindown_Translation_via_Window": {
              "items": [
                {
                  "tag": "10203. Rentenota 1*",
                  "norwegian": "10203. Rentenota 1*"
                },
                {
                  "tag": "",
                  "norwegian": "10203. Rentenota 1*"
                }
              ]
            }
          }
        ]
      }
    }
  }
}

A second example shows a query to the order table that is joined with the oder line table. We retrieve the first 10 orders and for each order the first three lines. We also fetch the total number of orders and for each order, the total number of lines.

{
  useCompany(no: 9112233)
  {
    order(first: 10)
    {
      totalCount
      items
      {
        orderNo
        joindown_OrderLine_via_Order(first: 3)
        {
          items
          {
            lineNo
            amountDomestic
          }
        }
      }
    }
  }
}

{
  "data": {
    "useCompany": {
      "order": {
        "totalCount": 1340,
        "items": [
          {
            "orderNo": 1,
            "joindown_OrderLine_via_Order": {
              "items": [
                {
                  "lineNo": 1,
                  "amountDomestic": 100
                },
                {
                  "lineNo": 2,
                  "amountDomestic": 250
                }
              ]
            }
          },
          {
            "orderNo": 2,
            "joindown_OrderLine_via_Order": {
              "items": [
                {
                  "lineNo": 1,
                  "amountDomestic": 980
                },
                {
                  "lineNo": 2,
                  "amountDomestic": 1500
                },
                {
                  "lineNo": 3,
                  "amountDomestic": 1999
                }
              ]
            }
          },
          ...
        ]
      }
    }
  }
}

Parameters

In the previous examples we have seen the use of the first parameter when querying various tables. This is not the only available argument. Each connection type has several parameters, which are as follows:

• first and after (and optionally skip) for forward pagination (see Pagination)
• last and before (and optionally skip) for backward pagination (see Pagination)
• filter for filtering data (see Filtering)
• distinct for returning only distinct values (see Distinct)
• sortOrder for sorting the results (see Sorting)
• groupBy for grouping the results (see Grouping)
• having for filtering the results after grouping (see Grouping)
• unoptimized for performing unoptimized request for some category of queries for joined tables that could potentially return incorrent results otherwise (see Unoptimized queries)

These parameters will be described in the GraphQL Features sections.