Queries /businessnxtapi/schema/queries section Learn how to read data using the query request in the Business NXT schema. For data modifications, refer to Mutation Type documentation. 2024-09-24T15:57:29+02:00 # Queries Learn how to read data using the query request in the Business NXT schema. For data modifications, refer to Mutation Type documentation. The Business NXT schema supports reading data (with a `query` request) as well as modifying data (with a `mutation` request). These two operations are exposed at the top of the schema with the `query` and `mutation` fields, as shown in the following image: ![Company table](./schema0.png) Here, you will learn about reading data. For inserts, updates, and deletes, see [The Mutation Type](../mutations/mutation.md). The Query Type /businessnxtapi/schema/queries/query page GraphQL Query type enables querying various tables with fields like useCompany for company data and useCustomer for system information. 2025-04-15T09:48:42+02:00 # The Query Type GraphQL Query type enables querying various tables with fields like useCompany for company data and useCustomer for system information. ## Overview The `Query` type is the root for all types used when you do a query (read) operation. This type contains the following fields: | Field | Description | | ----- | ----------- | | `useCompany` | The entry point for accessing company tables. Has a single argument, the Visma.net company ID. | | `useCustomer` | The entry point for accessing system tables. Has a single argument, the Visma.net customer ID. | | `useModel` | The entry point for accessing model information. See [Model information](modelinfo.md). | | `asyncRequests` and `asyncResult` | See [Async queries](../async.md). | | `availableCustomers` | See [Fetching user's available companies](companies.md). | | `availableCompanies` | See [Fetching user's available customers](customers.md).| 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 | | ----------- | --- | | ![System table](../schema1.png) | ![Company table](../schema2.png) | ## 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: `__Connection`. Here is how the `Query_UseCompany_GeneralLedgedAccount_Connection` type looks in the GraphiQL document explorer: ![GeneralLedgerAccount type](../schema3.png) 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: ```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: ```graphql generalLedgerAccount { totalCount pageInfo { hasNextPage hasPreviousPage startCursor endCursor } items { accountNo name # more columns } } ``` > [!TIP] > > Keep in mind that all of these fields are optional when making a query. The result will contain only the information that has been requested. This is a key feature of GraphQL, which makes it preferable to other web service architectures. 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: ![UseCompany_GeneralLedgerAccount_Connection type](../schema4.png) 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: | Tag | Description | | --- | ----------- | | `[PK]` | Primary key | | `[M]` | Memory column (not physically present in the database) | | `[Fn/a]` | Formula not available for a memory column | | `[B]` | Blob field whose value is returned as a base64-encoded string | | `[S(n)]` | Limited string of maximum `n` characters | 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: ```graphql { title = "Query" } query { useCompany(no: 9112233) { generalLedgerAccount { totalCount pageInfo { hasNextPage hasPreviousPage startCursor endCursor } items { accountNo name } } } } ``` ```graphql { title = "Result" } { "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__Connection`. In order to query a system table, you must specify a customer's Visma.net identifier. Let's look at an example: ```graphql { title = "Query" } query { useCustomer(no: 1111111) { user { items { userName } } } } ``` ```graphql { title = "Result" } { "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. ```graphql { title = "Query" } query { useCustomer(no: 1111111) { window(first: 3) { items { layoutName joinup_Folder { folderNo name } } } } } ``` ```graphql { title = "Result" } { "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__via_`. 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_` (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. ```graphql { title = "Query" } { useCompany(no:9112233) { order(first: 2) { items { orderNo customerNo employeeNo joinup_Associate_via_Customer { associateNo customerNo name } joinup_Associate_via_Employee { associateNo employeeNo name } } } } } ``` ```graphql { title = "Result" } { "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__via_`. When the table and the relation name are the same, the form is simplified to `joindown_`. 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. ```graphql { title = "Query" } query { useCustomer(no: 1111111) { window(first: 3) { items { layoutName joindown_Translation_via_Window { items { tag norwegian } } } } } } ``` ```graphql { title = "Result" } { "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. ```graphql { title = "Query" } { useCompany(no: 9112233) { order(first: 10) { totalCount items { orderNo joindown_OrderLine_via_Order(first: 3) { items { lineNo amountDomestic } } } } } } ``` ```graphql { title = "Result" } { "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 } ] } }, ... ] } } } } ``` > [!WARNING] > > Because the execution of queries for joined tables is optimized for performance, the `totalCount` and pagination does not work as described in this document. To understand the problem and the workaround see [Unoptimized queries](../../features/unoptimized.md). ## 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](../../features/pagination.md)) - `last` and `before` (and optionally `skip`) for backward pagination (see [Pagination](../../features/pagination.md)) - `filter` for filtering data (see [Filtering](../../features/filtering.md)) - `distinct` for returning only distinct values (see [Distinct](./distinct.md)) - `sortOrder` for sorting the results (see [Sorting](../../features/sorting.md)) - `groupBy` for grouping the results (see [Grouping](./grouping.md)) - `having` for filtering the results after grouping (see [Grouping](./grouping.md)) - `unoptimized` for performing unoptimized request for some category of queries for joined tables that could potentially return incorrent results otherwise (see [Unoptimized queries](../../features/unoptimized.md)) These parameters will be described in the *GraphQL Features* sections. Grouping /businessnxtapi/schema/queries/grouping page API documentation on grouping data, including SQL examples, GraphQL equivalents, and parameters for filtering, sorting, paginating, and applying aggregate functions. 2025-04-15T09:48:42+02:00 # Grouping API documentation on grouping data, including SQL examples, GraphQL equivalents, and parameters for filtering, sorting, paginating, and applying aggregate functions. ## Overview An important scenario when working with data is grouping. Grouping allows you to aggregate data based on a specific column or columns. This enables fetching summary of data based on various aggregate functions. For example, you may want to group orders by the customer column and then count the number of orders and their total value in each group. In SQL, this can be done using the `GROUP BY` clause as follows: ```sql SELECT CustNo, Count([OrdNo]), SUM(DNOrdSum) FROM [Ord] GROUP BY CustNo ``` When grouping data, you can select: - The columns that you want to group by. The rows with the same value in the selected columns are grouped together. - The aggregate functions that you want to apply to the grouped data. The aggregate functions are applied to the columns that are not included in the `GROUP BY` clause. Because the `WHERE` clause is evaluated before the `GROUP BY` clause, you can use the `WHERE` clause to filter the rows that are included in the grouping. For instance, if we want to take into account only the orders in a specific interval (let's say the current month) we can apply a filter as follows: ```sql SELECT CustNo, Count(OrdNo), SUM(DNOrdSum) FROM Ord WHERE ChDt > 20231101 GROUP BY CustNo ``` However, filtering the rows in the `WHERE` clause is not always possible. For instance, if we want to retrieve data only for customers that made purchases over some specific amount, we cannot use the `WHERE` clause because the aggregated sum is not a table column. In this case, we can use the `HAVING` clause to filter the groups. The `HAVING` clause is evaluated after the `GROUP BY` clause and it can be used to filter the groups based on aggregate functions. An example is shown here: ```sql SELECT CustNo, Count(OrdNo), SUM(DNOrdSum) FROM Ord WHERE ChDt > 20231101 GROUP BY CustNo HAVING SUM(DNOrdSum) > 1000 ``` You can also specify a sorting order for the resulting data using the `SORT BY` clause. The `SORT BY` clause is evaluated after the `HAVING` clause. In the following example, data is sorted ascending by the customer number. ```sql SELECT CustNo, Count(OrdNo), SUM(DNOrdSum) FROM Ord WHERE ChDt > 20231101 GROUP BY CustNo HAVING SUM(DNOrdSum) > 1000 SORT BY CustNo ASC ``` The capabilities to group data and filter based on aggregate functions in available in BNXT GraphQL as well. The following sections describe how to use the `groupBy` and `having` clauses in BNXT GraphQL. ## The connection type In the previous section, we learned about the connection types and how to perform queries using them. Every connection types has a set of parameters that allows to filter, sort, paginate, but also group. An example is shown in the following image for the `order` table: ![Order table](../schema15.png) The following parameters can be used to group data: | Parameter | Description | | --------- | ----------- | | `filter` | Specifies the filter expression. The filter expression is evaluated before the `groupBy` clause. | | `groupBy` | Specifies the columns that you want to group by. The rows with the same value in the selected columns are grouped together. | | `having` | Specifies the aggregate functions that you want to apply to the grouped data. The aggregate functions are applied to the columns that are not included in the `groupBy` clause. The `having` clause is applied to the data after grouping. | | `sortOrder` | Specifies the sort order of the grouped data. | ## Grouping data The following example shows how to group data using the `groupBy` parameter. The example groups the orders by the customer number and then counts the number of orders and their total value in each group. ```graphql { title = "Query" } query read_grouped_orders($cid : Int) { useCompany(no : $cid) { order( groupBy : [{customerNo : DEFAULT}]) { items { customerNo aggregates { count { orderNo } sum { orderSumNetDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "customerNo": 0, "aggregates": { "count": { "orderNo": 111 }, "sum": { "orderSumNetDomestic": 3195 } } }, { "customerNo": 10125, "aggregates": { "count": { "orderNo": 1 }, "sum": { "orderSumNetDomestic": 0 } } }, ... ] } } } } ``` To filter the date, you can use the `filter` parameter. The following example groups the purchase orders by the customer number and then counts the number of orders and their total value in each group. The orders are filtered by amount, which must be positive, and by the order date. ```graphql { title = "Query" } query read_grouped_orders($cid : Int) { useCompany(no : $cid) { order( filter : {_and : [ {orderSumNetDomestic : {_gt : 0}}, {orderDate : {_gt : 20210101}}, {customerNo : {_not_eq : 0}} ]}, groupBy : [{customerNo : DEFAULT}]) { items { customerNo aggregates { count { orderNo } sum { orderSumNetDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "customerNo": 10000, "aggregates": { "count": { "orderNo": 289 }, "sum": { "orderSumNetDomestic": 305400 } } }, { "customerNo": 10001, "aggregates": { "count": { "orderNo": 3 }, "sum": { "orderSumNetDomestic": 58047 } } }, ... ] } } } } ``` You can also filter the aggregated data by using the `having` parameter. The following example groups the purchase orders by the customer number and then counts the number of orders and their total value in each group. The orders are filtered by amount, which must be positive, and by the order date. The aggregated data is filtered by the total value of the orders, which must be greater than 100000. ```graphql { title = "Query" } query read_grouped_orders($cid : Int) { useCompany(no : $cid) { order( filter : {_and : [ {orderSumNetDomestic : {_gt : 0}}, {orderDate : {_gt : 20210101}}, {customerNo : {_not_eq : 0}} ]}, groupBy : [{customerNo : DEFAULT}], having : { _sum : { orderSumNetDomestic : {_gt : 100000} } }) { items { customerNo aggregates { count { orderNo } sum { orderSumNetDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "customerNo": 10000, "aggregates": { "count": { "orderNo": 289 }, "sum": { "orderSumNetDomestic": 305400 } } }, { "customerNo": 10285, "aggregates": { "count": { "orderNo": 186 }, "sum": { "orderSumNetDomestic": 312975 } } } ] } } } } ``` Finally, you can also sort the data using the `sortOrder` parameter. The following example, expands the previous one by retrieving the data sorted by descending customer number. ```graphql { title = "Query" } query read_grouped_orders($cid : Int) { useCompany(no : $cid) { order( filter : {_and : [ {orderSumNetDomestic : {_gt : 0}}, {orderDate : {_gt : 20210101}}, {customerNo : {_not_eq : 0}} ]}, groupBy : [{customerNo : DEFAULT}], having : { _sum : { orderSumNetDomestic : {_gt : 100000} } }, sortOrder : {customerNo : DESC}) { items { customerNo aggregates { count { orderNo } sum { orderSumNetDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "customerNo": 10285, "aggregates": { "count": { "orderNo": 186 }, "sum": { "orderSumNetDomestic": 312975 } } }, { "customerNo": 10000, "aggregates": { "count": { "orderNo": 289 }, "sum": { "orderSumNetDomestic": 305400 } } } ] } } } } ``` ## The `groupBy` argument The type of the group by argument is an array of non-null objects of the type `_GroupByType`. An example is `Order_GroupByType` for the `Order` table. Each element of the array specifies one column for grouping. Their order in the `GROUP BY` clause is the one in which they appear in the array. Let's take the following example: ```graphql groupBy : [{customerNo : DEFAULT}, [orderDate : DEFAULT]] ``` This groups the data by the `customerNo` and the `orderDate` columns. When you specify a column for grouping, you must also specify the kind of grouping that you want to perform. The possible values are: | Value | Description | | ----- | ----------- | | `DEFAULT` | The default grouping. The rows with the same value in the column are grouped together. | | `ROLLUP` | The `ROLLUP` grouping. This creates a group for each combination of column expressions and also rolls up the results into subtotals and grand totals. | While the column order is not important for the `DEFAULT` grouping, it is important for the `ROLLUP` grouping. For instance, `GROUP BY ROLLUP (A, B, C)` creates groups for each combination of column expressions as show in the following list: ``` A, B, C A, B, NULL A, NULL, NULL NULL, NULL, NULL ``` To learn more about the `ROLLUP` grouping, see the [SELECT - GROUP BY- Transact-SQL](https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15#rollup) documentation. To show how this work, lets take the following example: select transactions grouped by account number and period. First, we use `DEFAULT` for account number and `ROLLUP` for `period`: ```graphql { title = "Query" } query read_gla_transactions_grouped($cid : Int) { useCompany(no : $cid) { generalLedgerTransaction( filter : { _and: [ {accountNo : {_gte : 3000}}, {year : {_gte : 2020}} ]}, groupBy: [ {accountNo : DEFAULT}, {period : ROLLUP}, ], having : { _sum : {postedAmountDomestic : {_not_eq : 0}} }) { items { accountNo period aggregates { sum { postedAmountDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "generalLedgerTransaction": { "items": [ { "accountNo": 3000, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": -4950 } } }, { "accountNo": 3000, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": -4950 } } }, { "accountNo": 4000, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 450 } } }, { "accountNo": 4000, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": 450 } } }, { "accountNo": 4410, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 13035 } } }, { "accountNo": 4410, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": 13035 } } } ] } } } } ``` Due to the applied filter, the result includes the account numbers 3000, 4000, and 4410 and the period 12. The result is a series of groups for the following combinations: | Account number | Period | | -------------- | ------ | | 3000 | 12 | | 3000 | 0 | | 4000 | 12 | | 4000 | 0 | | 4410 | 12 | | 4410 | 0 | Next, we change the grouping type use `ROLLUP` for account number and `DEFAULT` for `period`: ```graphql { title = "Query" } query read_gla_transactions_grouped($cid : Int) { useCompany(no : $cid) { generalLedgerTransaction( filter : { _and: [ {accountNo : {_gte : 3000}}, {year : {_gte : 2020}} ]}, groupBy: [ {accountNo : ROLLUP}, {period : DEFAULT}, ], having : { _sum : {postedAmountDomestic : {_not_eq : 0}} }) { items { accountNo period aggregates { sum { postedAmountDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "generalLedgerTransaction": { "items": [ { "accountNo": 3000, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": -4950 } } }, { "accountNo": 4000, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 450 } } }, { "accountNo": 4410, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 13035 } } }, { "accountNo": 0, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 8535 } } } ] } } } } ``` The result is a series of groups for the following combinations: | Account number | Period | | -------------- | ------ | | 3000 | 12 | | 4000 | 12 | | 4410 | 12 | | 0 | 12 | Finally, let's use `ROLLUP` for both account number and period: ```graphql { title = "Query" } query read_gla_transactions_grouped($cid : Int) { useCompany(no : $cid) { generalLedgerTransaction( filter : { _and: [ {accountNo : {_gte : 3000}}, {year : {_gte : 2020}} ]}, groupBy: [ {accountNo : ROLLUP}, {period : ROLLUP}, ], having : { _sum : {postedAmountDomestic : {_not_eq : 0}} }) { items { accountNo period aggregates { sum { postedAmountDomestic } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "generalLedgerTransaction": { "items": [ { "accountNo": 3000, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": -4950 } } }, { "accountNo": 3000, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": -4950 } } }, { "accountNo": 4000, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 450 } } }, { "accountNo": 4000, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": 450 } } }, { "accountNo": 4410, "period": 12, "aggregates": { "sum": { "postedAmountDomestic": 13035 } } }, { "accountNo": 4410, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": 13035 } } }, { "accountNo": 0, "period": 0, "aggregates": { "sum": { "postedAmountDomestic": 8535 } } } ] } } } } ``` This time, we get the following combinations: | Account number | Period | | -------------- | ------ | | 3000 | 12 | | 3000 | 0 | | 4000 | 12 | | 4000 | 0 | | 4410 | 12 | | 4410 | 0 | | 0 | 0 | ## The `having` argument The type of the `having` argument is an object of the type `_HavingType`. An example is `Order_HavingType` for the `Order` table. The `having` clause is applied to the data after grouping and defines filters on aggregated data. The `_HavingType` type has the following fields: - all the columns in the table - all the available aggregate functions, such as `SUM` and `COUNT`, but prefixed with an underscore: `_sum`, `_count`, etc. - `_and` and `_or` operators that allow to combine multiple conditions The available aggregate functions are listed in the following table: | Aggregate | Column types | Description | | --------- | ------------ | ----------- | | `sum` | numerical | The sum of all the values. | | `sumDistinct` | numerical | The sum of all distinct values. | | `average` | numerical | The average of all the values. | | `averageDistinct` | numerical | The averate of all distinct values. | | `count` | numerical | The number of the items. | | `countDistinct` | numerical | The number of distinct items. | | `minimum` | numerical, date, time | The minimum value. | | `maximum` | numerical, date, time | The maximum value. | | `variance` | numerical | The statistical variance of all the values. | | `varianceDistinct` | numerical | The statistical variance of all the distinct values. | | `variancePopulation` | numerical | The statistical variance for the population of all the values. | | `variancePopulationDistinct` | numerical | The statistical variance for the population of all the distinct values. | | `standardDeviation` | numerical | The statistical standard deviation of all the values. | | `standardDeviationDistinct` | numerical | The statistical standard deviation of all the distinct values. | | `standardDeviationPopulation` | numerical | The statistical standard deviation for the population of all the values. | | `standardDeviationPopulationDistinct` | numerical | The statistical standard deviation for the population of all the distinct values. | In the previous example we have used this condition: ```graphql having : {_sum : {orderSumNetDomestic : {_gt : 100000}}} ``` This is equivalent to the following SQL clause: ```sql HAVIG SUM(DNOrdSum) > 100000 ``` Because of the nature of GraphQL, the order of fields is: 1. aggregate function (AF) 2. column name (COL) 3. operator (OP) 4. value Therefore, the specification `{AF : {COL : {OP : value}}}` is translated to `AF(COL) OP value` and not `AF(COL OP value)`. You can build complex filters such as the following where we select all the groups either have the count of orders greater than 100 or the sum of the order values is between 100000 and 200000: ```graphql having : { _or: [ {_count :{orderNo : {_gt : 100}}}, { _and : [ {_sum : {orderSumNetDomestic : {_gt : 100000}}}, {_sum : {orderSumNetDomestic : {_lt : 200000}}}, ] } ] } ``` > [!TIP] > > The operators used in `having` clauses are the same used for filter expressions (for the `filter` argument). To learn more about these, see [Filtering](../../features/filtering.md). ## Limitations There are also some limitations when grouping data that you must be aware of: - You cannot use pagination. The `first`/`after` and `before`/`last` arguments do not work. - You cannot group by or filter data from a joined table. - Sorting does not include the aggregated data. Aggregates /businessnxtapi/schema/queries/aggregates page Aggregate functions in GraphQL for computing values in table fields. Supports sum, average, count, min, max, variance, and more. 2025-04-15T09:48:42+02:00 # Aggregates Aggregate functions in GraphQL for computing values in table fields. Supports sum, average, count, min, max, variance, and more. > [!WARNING] > > This schema section is obsolete and will be removed in the future. > > Use grouping for fetching aggregated data. See [Grouping](./grouping.md) for more information. Business NXT GraphQL supports computing aggregate values for table fields. Aggregate functions ignore null values in the tables. These are deterministic and return the same value each time that they are called. They are similar to the aggregate functions available in SQL. The supported aggregates are listed in the following table. The returned values are computed from the selection determined by provided filter. The filter is optional and if not specified all the records in the table are included. | Aggregate | Column types | Description | | --------- | ------------ | ----------- | | `sum` | numerical | The sum of all the values. | | `sumDistinct` | numerical | The sum of all distinct values. | | `average` | numerical | The average of all the values. | | `averageDistinct` | numerical | The averate of all distinct values. | | `count` | numerical | The number of the items. | | `countDistinct` | numerical | The number of distinct items. | | `minimum` | numerical, date, time | The minimum value. | | `maximum` | numerical, date, time | The maximum value. | | `variance` | numerical | The statistical variance of all the values. | | `varianceDistinct` | numerical | The statistical variance of all the distinct values. | | `variancePopulation` | numerical | The statistical variance for the population of all the values. | | `variancePopulationDistinct` | numerical | The statistical variance for the population of all the distinct values. | | `standardDeviation` | numerical | The statistical standard deviation of all the values. | | `standardDeviationDistinct` | numerical | The statistical standard deviation of all the distinct values. | | `standardDeviationPopulation` | numerical | The statistical standard deviation for the population of all the values. | | `standardDeviationPopulationDistinct` | numerical | The statistical standard deviation for the population of all the distinct values. | Aggregates can be computed on all the tables that can be querried from GraphQL. Every field in the schema used to query a table (having the same name as the table) has a companion field used to execute aggregate functions. This field has the name format `_aggregate`. For example, for reading data from the orders table, there is a field called `order` and for reading aggregate values there is a field called `order_aggregate`. ![System table](../aggregate1.png) The `
_aggregate` field has a query argument that represents a filter. This is the same filter object used to query data from the table. This is documented in the [Filtering](../../features/filtering.md) page. The type of the `
_aggregate` field has the name format `Query__
Aggregate_Node`. For instance, for the `order_aggregate` field the type name is `Query_UseCompany_OrderAggregate_Node`. This type contains one field for each aggregate function listed in the above table. This can be seen in the GraphiQL document explorer as follows: ![System table](../aggregate2.png) The `minimum` and `maximum` fields are of a type with the name format `
WithDateTimeAggregate`. This type includes fields that represent dates and times. The others fields have a different the type that only contains numerical columns. This type has the name format `
Aggregate`. For instance, for the `Order` table, the type that includes dates and times is called `OrderWithDateTimeAggregate`, and the type that only includes numerical fields is called `OrderAggregate`. | Only numerical fields | Numerical + Date/Time fields | | --------------------- | ---------------------------- | | ![System table](../aggregate3.png) | ![System table](../aggregate4.png) | Executing aggregate functions is demonstrated with the following example: ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order_aggregate { count { orderNo } sum { orderSumNetDomestic vatAmountDomestic } average { orderSumNetDomestic vatAmountDomestic } minimum { orderDate orderSumNetDomestic vatAmountDomestic } maximum { orderDate orderSumNetDomestic vatAmountInCurrency } variance { orderSumNetDomestic vatAmountDomestic } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_aggregate": { "count": { "orderNo": 344 }, "sum": { "orderSumNetDomestic": 26930, "vatAmountDomestic": 7952.5 }, "average": { "orderSumNetDomestic": 73.174085, "vatAmountDomestic": 19.363742 }, "minimum": { "orderDate": 20130105, "orderSumNetDomestic": 0, "vatAmountDomestic": 0 }, "maximum": { "orderDate": 20151205, "orderSumNetDomestic": 21950, "vatAmountInCurrency": 5487.5 }, "variance": { "orderSumNetDomestic": 1529866.15103014, "vatAmountDomestic": 93992.56825201 } } } } } ``` To include a filter, you need to specify it as an argument to the `order_aggregate` field, as shown here: ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order_aggregate(filter : { orderDate : {_gt : 20140101} }) { count { orderNo } sum { orderSumNetDomestic vatAmountDomestic } average { orderSumNetDomestic vatAmountDomestic } minimum { orderDate orderSumNetDomestic vatAmountDomestic } maximum { orderDate orderSumNetDomestic vatAmountInCurrency } variance { orderSumNetDomestic vatAmountDomestic } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_aggregate": { "count": { "orderNo": 312 }, "sum": { "orderSumNetDomestic": 25127, "vatAmountDomestic": 6642.5 }, "average": { "orderSumNetDomestic": 82.553134, "vatAmountDomestic": 21.453173 }, "minimum": { "orderDate": 20140102, "orderSumNetDomestic": 0, "vatAmountDomestic": 0 }, "maximum": { "orderDate": 20151205, "orderSumNetDomestic": 21145, "vatAmountInCurrency": 5675.5 }, "variance": { "orderSumNetDomestic": 1542331.617824, "vatAmountDomestic": 95752.0148624473 } } } } } ``` Distinct values /businessnxtapi/schema/queries/distinct page Guide to fetch distinct table values using distinct argument, translating into SQL. Examples include account groups, customers, and order types, with equivalent groupBy queries. 2025-04-15T09:48:42+02:00 # Distinct values Guide to fetch distinct table values using distinct argument, translating into SQL. Examples include account groups, customers, and order types, with equivalent groupBy queries. It is possible to fetch distinct values from a table using the `distinct` argument. This is a boolean parameter that must be set to `true` to enable distinct data fetching. This translates into the `SELECT DISTINCT` SQL statement. The following example shows how to fetch the distinct account groups from the general ledger table: ```graphql { title = "Query" } query read_distinct_account_groups($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(distinct : true) { items { accountGroup } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "generalLedgerAccount": { "items": [ { "accountGroup": "" }, { "accountGroup": "100K_FORSKNING_UTVIKLING" }, { "accountGroup": "102K_KONSESJON_PATENT_LISENSER" }, { "accountGroup": "107K_UTSATT_SKATTEFORDEL" }, { "accountGroup": "108K_GOODWILL" } ... ] } } } } ``` It is possible to select distinct values for multiple fields. The following example shows how to distinct customers and order types from the order table: ```graphql { title = "Query" } query read_orders($cid : Int!, $dt : Int) { useCompany(no: $cid) { order( filter : {changedDate : {_gt : $dt}}, distinct : true, first : 10) { pageInfo { hasNextPage startCursor endCursor } items { customerNo orderType } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "pageInfo": { "hasNextPage": false, "startCursor": "MQ==", "endCursor": "NQ==" }, "items": [ { "customerNo": 0, "orderType": 1 }, { "customerNo": 10000, "orderType": 1 }, { "customerNo": 10001, "orderType": 1 }, { "customerNo": 10002, "orderType": 2 }, { "customerNo": 10286, "orderType": 1 } ] } } } } ``` As mentioned before, the use of the `distinct` argument will produce a `SELECT DISTINCT` SQL statement for fetching data. The following two SQL statements are roughly equivalent: ```sql SELECT DISTINCT a,b,c FROM T ``` ```sql SELECT a,b,c FROM T GROUP BY a,b,c ``` Therefore, the same results can be achieved, typically, by using the `groupBy` argument. The following example shows how to fetch the same distinct customers and order types from the order table using the `groupBy` argument: ```graphql { title = "Query" } query read_orders($cid : Int!, $dt : Int) { useCompany(no: $cid) { order( filter : {changedDate : {_gt : $dt}}, groupBy : [ {customerNo : DEFAULT}, {orderType : DEFAULT} ], sortOrder : { customerNo : ASC, _thenBy : { orderType : DESC} } first : 10) { pageInfo { hasNextPage startCursor endCursor } items { customerNo orderType } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "pageInfo": { "hasNextPage": false, "startCursor": "MQ==", "endCursor": "NQ==" }, "items": [ { "customerNo": 0, "orderType": 1 }, { "customerNo": 10000, "orderType": 1 }, { "customerNo": 10001, "orderType": 1 }, { "customerNo": 10002, "orderType": 2 }, { "customerNo": 10286, "orderType": 1 } ] } } } } ``` > [!NOTE] > > When requesting distinct values, the `totalCount` field will still return the total number of records in the table that match the provided filter (if any was given), not the number of distinct records. > > It is recommended that you do not use `totalCount` when fetching distinct values, as it will indicate a misleading quantity. ## Obsolete distinct values > [!WARNING] > > This schema section is obsolete and will be removed in the future. > > It is recommended to use the `distinct` argument for fetching distinct values, as described above. An option to fetch distinct values of a field is also available under the [aggregates](./aggregates.md) field with a subfield called `distinct`. Aggregate types have the name of the form `Aggregate`, such as `AssociateAggregate`. On the other hand, the field used for fetching distinct values have their own type, using the format `Distinct`, such as `AssociateDistinct`. ![Distinct field](../aggregate5.png) The distinct type have the same fields as the aggregate types, which are the columns of the database tables. However, their type is a list of values and not a single value. The following image shows a snippet of the `AssociateDistinct` type: ![Distinct type](../aggregate6.png) The following table shows an example for fetching the distinct post codes and country codes from the `Associate` table: ```graphql { title = "Query" } query($cid:Int) { useCompany(no: $cid) { associate_aggregate { distinct { postCode countryNo } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_aggregate": { "distinct": { "postCode": [ "", "AL20 0XX", "AL3 8JH", "B11 2BH", "B11 3RR", "B42 1DU", "B60 3DR", ... "WV10 7Ln", "WV14 OQL", "WV15 5HR" ], "countryNo": [ 0, 1, 33, 44, 46, 47, 353 ] } } } } } ``` You can use the same filters as for the aggregate functions. Here is an example: ```graphql { title = "Query" } query read($cid:Int) { useCompany(no: $cid) { associate_aggregate(filter:{ postalArea : {_eq : "London"} }) { distinct { postCode } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_aggregate": { "distinct": { "postCode": [ "L63 4DJ", "MW10 2XA" ] } } } } } ``` Fetching distinct values for unique primary keys will not provide any benefit over a regular query for that specific field. In other words, the following two queries will return the same date, although in different forms. ```graphql { title = "Query" } query read($cid:Int) { useCompany(no: $cid) { associate_aggregate(filter:{ postalArea : {_eq : "London"} }) { distinct { associateNo } } associate(filter:{ postalArea : {_eq : "London"} }) { items { associateNo } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_aggregate": { "distinct": { "associateNo": [ 28, 149 ] } }, "associate": { "items": [ { "associateNo": 28 }, { "associateNo": 149 } ] } } } } ``` Fetching user's available customers /businessnxtapi/schema/queries/customers page Fetch multiple customers linked to a user via GraphQL, using the availableCustomers query field with optional pagination and sorting. 2025-04-15T09:48:42+02:00 # Fetching user's available customers Fetch multiple customers linked to a user via GraphQL, using the availableCustomers query field with optional pagination and sorting. Typically, a user is associated with a single Visma.net customer. However, it is possible that one user is linked to multipled customers. Business NXT GraphQL allows to fetch all these customers. The list of customers linked to the authenticated user is available with a special field under the `Query` type. This field is called `availableCustomers`. It is similar to the connection types seen so far, except that the `pageInfo` field is missing. ![availableCustomers field](../availablecustomers.png) Here is a query example: ```graphql { title = "Query" } { availableCustomers { totalCount items { name vismaNetCustomerId } } } ``` ```graphql { title = "Result" } { "data": { "availableCustomers": { "totalCount": 1, "items": [ { "name": "Test Customer for Business NXT", "vismaNetCustomerId": 1234567 } ] } } } ``` The `availableCustomers` field has several optional arguments: - an argument called `first` that indicates the number of elements to be fetched from the top of the customers list - an argument called `last` that indicates the number of elements to be fetched from the back of the customers list; if both `first` and `last` are present, `first` is used and `last` is ignored - an argument called `sortOrder` that defines the sorting order of the result Fetching user's available companies /businessnxtapi/schema/queries/companies page Retrieve a list of companies available to an authenticated user using the availableCompanies GraphQL field, with optional filtering and sorting arguments. 2025-04-15T09:48:42+02:00 # Fetching user's available companies Retrieve a list of companies available to an authenticated user using the availableCompanies GraphQL field, with optional filtering and sorting arguments. The list of companies available to the authenticated user is available with a special field available under the `Query` type. This field is called `availableCompanies`. It is similar to the connection types seen so far, except that the `pageInfo` field is missing. ![availableCompanies field](../availablecompanies.png) Here is a query example: ```graphql { title = "Query" } { availableCompanies { totalCount items { name vismaNetCompanyId vismaNetCustomerId } } } ``` ```graphql { title = "Result" } { "data": { "availableCompanies": { "totalCount": 2, "items": [ { "name": "Marius Test AS", "vismaNetCompanyId": 5280... "vismaNetCustomerId": 9123... }, { "name": "JAM&WAFFLES", "vismaNetCompanyId": 5199... "vismaNetCustomerId": 9456... } ] } } } ``` The `availableCompanies` field has several optional arguments: - an argument called `customerNo` that indicates the Visma.net number of the customer for which available companies should be retrieved - an argument called `first` that indicates the number of elements to be fetched from the top of the companies list - an argument called `last` that indicates the number of elements to be fetched from the back of the companies list; if both `first` and `last` are present, `first` is used and `last` is ignored - an argument called `sortOrder` that defines the sorting order of the result To fetch companies specific to a particular customer, pass its Visma.net identifier in the `customerNo` parameter, as shown in the following example: ```graphql { title = "Query" } { availableCompanies(customerNo : 12345678) { totalCount items { name vismaNetCompanyId } } } ``` ```graphql { title = "Result" } { "data": { "availableCompanies": { "totalCount": 2, "items": [ { "name": "Marius Test AS", "vismaNetCompanyId": 5280... }, { "name": "JAM&WAFFLES", "vismaNetCompanyId": 5199... } ] } } } ``` If the `customerNo` parameter is missing, the companies available to all the available customers in the list are returned. To retrieve the list of available customers see [Fetching user's available customers](customers.md). ## Service context When you authenticate with client credentials (using a client ID and a client secret), fetching the list of available companies requires specifying a customer no (as described earlier). You can find out the customer number in two ways: - Authenticate with a Visma.net user and use the `availableCustomers` field to fetch the list (see [Fetching user's available customers](customers.md)). - Retrieve this information from Visma.Net Admin. To do the latter: - Logon to - Open the *Configuration* tab - Copy the value for *Visma.net Customer ID* ![Visma.Net Admin Configuration](../vismaadm1.png) When then customer ID is not provided, an error is returned, as follows: ```json { "errors": [ { "message": "GraphQL.ExecutionError: Could not fetch the list of available companies. Customer number is mandatory." } ], "data": { "availableCompanies": null }, "extensions": { "vbnxt-trace-id": "..." } } ``` Model information /businessnxtapi/schema/queries/modelinfo page Query model information using useModel to retrieve details on tables, columns, relations, domains, processings, reports, and folders. 2025-04-15T09:48:42+02:00 # Model information Query model information using useModel to retrieve details on tables, columns, relations, domains, processings, reports, and folders. You can query information from the data model. This is possible using the `useModel` field in the top-level query. This allows to retrieve information about the following entities: - tables - columns - relations - domains and domain members - processings - reports - folders The information that is available for each entity include: - primary key (such as `tableNo` for tables, `columnNo` and `tableNo` for columns, etc.) - identifier, which is a language-independent name of the field; this is what the GraphQL schema uses for entity names - name, which is a language-specific name for the entity - access restrictions and availability In addition, each type of entity has it's own specific fields. The languages supported for translations are: - English (this is the default, if no language is specified) - Norwegian - Swedish - Danish Here is an example for fetching the ID, identifier, name in Norwgian, and the database type it belongs to (which can be either `COMPANY` or `SYSTEM`) for all the existing tables: ```graphql { title = "Query" } query get_tables_info { useModel { tables(lang: NORWEGIAN) { tableNo name identifier databaseType } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "tables": [ { "tableNo": 1, "name": "Arbeidsområde­vindu", "identifier": "WorkspaceWindow", "databaseType": "SYSTEM" }, { "tableNo": 2, "name": "Arbeidsområde­element", "identifier": "WorkspacePageElement", "databaseType": "SYSTEM" }, # ... { "tableNo": 457, "name": "Inngående betalingslinje", "identifier": "AutopayVipPaymentLine", "databaseType": "COMPANY" }, { "tableNo": 458, "name": "Inngående betaling ekstratekst", "identifier": "AutopayVipTextInfo", "databaseType": "COMPANY" } ] } } } ``` On the other hand, it's possible to ask for this information for a specific table by specifying the table number: ```graphql { title = "Query" } query get_table_info { useModel { tables(lang: NORWEGIAN, tableNo : 152) { tableNo name identifier databaseType } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "tables": [ { "tableNo": 152, "name": "Aktør", "identifier": "Associate", "databaseType": "COMPANY" } ] } } } ``` Similarly, you can query, for instance, for: - all the columns in the system - all the columns of a specified table - a single column specifed by its column number In the next example, we query information about all the columns from the `Associate` table: ```graphql { title = "Query" } query get_columns_info { useModel { columns(lang: NORWEGIAN, tableNo : 152) { columnNo tableNo name identifier } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "columns": [ { "columnNo": 4028, "name": "Aktørnr", "identifier": "AssociateNo" }, { "columnNo": 4029, "name": "Navn", "identifier": "Name" }, # ... ] } }} ``` On the other hand, in the next example, we query information about the associate number column (from the `Associate` table): ```graphql { title = "Query" } query get_column_info { useModel { columns(lang: NORWEGIAN, columnNo: 4028) { columnNo tableNo name identifier } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "columns": [ { "columnNo": 4028, "tableNo": 152, "name": "Aktørnr", "identifier": "AssociateNo" } ] } } } ``` You can additionaly query for domain information for a column, as shown in the following example: ```graphql { title = "Query" } query read_column_info { useModel { columns(lang: NORWEGIAN, columnNo : 3363) { columnNo tableNo name domain { domainNo name length columnWidth storeFixedDecimals displayFixedDecimals fixedDecimals dataType fieldJustification fileName domainMembers { name identifier valueNo includeValue initiallyOn } } } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "columns": [ { "columnNo": 3363, "tableNo": 52, "name": "Ordrenr", "domain": { "domainNo": 555, "name": "Ordrenr", "length": 0, "columnWidth": 8, "storeFixedDecimals": false, "displayFixedDecimals": false, "fixedDecimals": 0, "dataType": "INT_32", "fieldJustification": "RIGHT", "fileName": false, "domainMembers": [] } } ] } } } ``` You can also query domains directly, by specifying the domain number: ```graphql { title = "Query" } query read_domain_info { useModel { domains(lang: NORWEGIAN, domainNo : 555) { domainNo name length columnWidth storeFixedDecimals displayFixedDecimals fixedDecimals dataType fieldJustification fileName domainMembers { name identifier groupName valueNo includeValue groupIncludeValue initiallyOn } } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "domains": [ { "domainNo": 555, "name": "Ordrenr", "length": 0, "columnWidth": 8, "storeFixedDecimals": false, "displayFixedDecimals": false, "fixedDecimals": 0, "dataType": "INT_32", "fieldJustification": "RIGHT", "fileName": false, "domainMembers": [] } ] } } } ``` Some domains are enumeration types. They define a set of possible values that can be stored in a column. The `domainMembers` field in the domain information query returns a list of domain members. An example is shown below: ```graphql { title = "Query" } query read_domain_info { useModel { domains(lang: NORWEGIAN, domainNo : 111) { domainNo name length columnWidth storeFixedDecimals displayFixedDecimals fixedDecimals dataType fieldJustification fileName domainMembers { name identifier groupName valueNo includeValue groupIncludeValue initiallyOn } } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "domains": [ { "domainNo": 111, "name": "Dok.­type", "length": 0, "columnWidth": 8, "storeFixedDecimals": false, "displayFixedDecimals": false, "fixedDecimals": 0, "dataType": "INT_32", "fieldJustification": "RIGHT", "fileName": false, "domainMembers": [ { "name": "Purrebrev", "identifier": "Reminder", "groupName": "", "valueNo": 1, "includeValue": -1, "groupIncludeValue": -1, "initiallyOn": false }, { "name": "Rentenota", "identifier": "InterestNote", "groupName": "", "valueNo": 2, "includeValue": -1, "groupIncludeValue": -1, "initiallyOn": false } ] } ] } } } ``` ## Text transformations The translated text may contain several application-specific escape characters or sequences. These are: - `^` for a hyphen - `|` for a new line (`\n`) - `&` for an access key accelerator - `{Ampersand}` for an `&` character When you retrieve the translated texts, these are automatically replaced with the appropriate character or sequence of characters. However, you can opt to perform your own custom replacement. This is possible with the following two arguments, available for all the fields under `useModel`: | Argument | Description | | -------- | ----------- | | `transform` | Indicates the transformation type: `AUTO` (the default option, application defined transformations), `NONE` (no transformation is performed), and `CUSTOM` (user-specified transformations are applied). | | `transformArgs` | Defines the transformation arguments when the `CUSTOM` value is specified for the `transform` argument. | The properties of the `transformArgs` parameter are as follows: | Property | Type | Description | | -------- | ---- | ----------- | | `modifyOptionalHyphen` | Boolean | Indicates whether hyphen replacement will be performed. | | `optionalHyphen` | String | Text for replacing the escape character (`^`) for a hyphen. | | `modifyManualLineBreak` | Boolean | Indicates whether manual line break replacement will be performed. | | `manualLineBreak` | String | Text replacing the escape character for manual line break. | | `modifyAccessKey` | Boolean | Indicates whether access key replacement will be performed. | | `accessKey` | String | Text for replacing the escape character (`&`) for an access key. | | `modifyAmpersandSubstitute` | Boolean | Indicates whether ampersand substitute replacement will be performed. | | `ampersandSubstitute` | String | Text for replacing the `{Ampersand}` escape sequence. | Here is an example for fetching raw texts: ```graphql { title = "Query" } query get_table_info { useModel { tables(lang: NORWEGIAN, tableNo : 138, transform : NONE) { tableNo name identifier } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "tables": [ { "tableNo": 138, "name": "Ordre^journal", "identifier": "OrderJournal" } ] } } } ``` On the other hand, the following sample shows how to perform user-defined transformations: ```graphql { title = "Query" } query get_table_info { useModel { tables(lang: NORWEGIAN, tableNo : 138, transform : CUSTOM, transformArgs : { modifyOptionalHyphen : true optionalHyphen : "-" }) { tableNo name identifier } } } ``` ```graphql { title = "Result" } { "data": { "useModel": { "tables": [ { "tableNo": 138, "name": "Ordre-journal", "identifier": "OrderJournal" } ] } } } ``` ## Access restrictions and availability Most model entities provide properties that indicate the availability and access restrictions. These properties are: | Property | Type | Description | Applies to | | -------- | ---- |----------- | ---------- | | `visible` | `Bool` | Indicates whether the entity is visible in the on-premise application. | All | | `cloudVisible` | `Bool` |Indicates whether the entity is visible in the BNXT front-end application. | All | | `writable` | `Bool` | Indicates whether the entity is writable in the on-premise application. | Columns, domain members | | `cloudWritable` | `Bool` | Indicates whether the entity is writable in the BNXT front-end application. | Columns, domain members | | `insertable` | `Bool` | Indicates whether the entity is insertable in the on-premise application. | Tables | | `cloudInsertable` | `Bool` | Indicates whether the entity is insertable in the BNXT front-end application. | Tables | | `updatable` | `Bool` | Indicates whether the entity is updatable in the on-premise application. | Tables | | `cloudUpdatable` | `Bool` | Indicates whether the entity is updatable in the BNXT front-end application. | Tables | | `deletable` | `Bool` | Indicates whether the entity is deletable in the on-premise application. | Tables | | `cloudDeletable` | `Bool` | Indicates whether the entity is deletable in the BNXT front-end application. | Tables | | `readAccess` | `Access` | Indicates the read access level for the entity in the on-premise application. | All | | `cloudReadAccess` | `CloudAccess` | Indicates the read access level for the entity in the BNXT front-end application. | All | | `writeAccess` | `Access` | Indicates the write access level for the entity in the on-premise application. | Columns, domain members | | `cloudWriteAccess` | `CloudAccess` | Indicates the write access level for the entity in the BNXT front-end application. | Columns, domain members | | `insertAccess` | `Access` | Indicates the insert access level for the entity in the on-premise application. | Tables | | `cloudInsertAccess` | `CloudAccess` | Indicates the insert access level for the entity in the BNXT front-end application. | Tables | | `updateAccess` | `Access` | Indicates the update access level for the entity in the on-premise application. | Tables | | `cloudUpdateAccess` | `CloudAccess` | Indicates the update access level for the entity in the BNXT front-end application. | Tables | | `deleteAccess` | `Access` | Indicates the delete access level for the entity in the on-premise application. | Tables | | `cloudDeleteAccess` | `CloudAccess` | Indicates the delete access level for the entity in the BNXT front-end application. | Tables | | `deleteAccess` | `Access` | Indicates the delete access level for the entity in the on-premise application. | Tables | | `cloudDeleteAccess` | `CloudAccess` | Indicates the delete access level for the entity in the BNXT front-end application. | Tables | | `executionAccess` | `Access` | Indicates the execution access level for the entity in the on-premise application. | Processings, reports | | `cloudExecutionAccess` | `CloudAccess` | Indicates the execution access level for the entity in the BNXT front-end application. | Processings, reports | | `availability` | `Availability` | Indicates whether the entity is available for use (`CURRENT`), it will be available in the future (`FUTURE`), or it is available but it is deprecated and will be removed (`OBSOLETE`). | All | The `Default` value for `CloudAccess` indicates that the access level is inherited from the on-premise application. For a column to be available in GraphQL for reading, the following conditions must be met: - `availability` should be `CURRENT` - `cloudReadAccess` must be different than `NONE` and `DEFAULT` - if `cloudReadAccess` is `DEFAULT` then `readAccess` must be different than `NONE` Similar rules are used for writing, inserting, updating, deleting, and executing access. ## Model information properties ### Tables The following properties are available for tables : | Property | Type | Description | | -------- | ---- | ----------- | | `tableNo` | `Long` | The table number. | | `name` | `String` | The translated name of the table. | | `identifier` | `String` | A language-independent identifier of the table. | | `databaseType` | `DatabaseTypeNo` | One of `COMPANY` or `SYSTEM`. | | `view` | `bool` | Indicates whether the table is a view. | | `primaryKeys` | `[Long]` | The primary keys' column numbers in the order they're defined in the database. | | `primaryKeyClustered` | `Bool` | Indicates whether the primary key is clustered. | | `orgUnitClass` | `OrgUnitClass` | The property returns one of the twelve values `ORGUNITCLASS01` (1) – `ORGUNITCLASS12` (12) if the table is associated with an organisational unit class, else `NONE` (0). The table will not be visible if the corresponding `CompanyInformation.OrgUnit1Name` – `OrgUnit12Name` column is empty. | | `folderNo` | `Long` | The folder number the table belongs to, or 0 if none. | | `formView` | `Bool` | `true` if new table page elements against this table should initially be displayed in Form view (instead of Grid view). | | `primaryKeyAssignment` | `PrimaryKeyAssignment` | Returns one of the following values for how the last primary key column is assigned a value on rows in the table: `NORMAL` - the ordinary behaviour of the table, `INCREMENTAL` - the last primary key column will get the next unused value when a value suggestion is requested, or `IDENTITY` - The last primary key column is created as an IDENTITY column in the database (the last two only for top tables that does not have a sort sequence column). | | `fromRelationsNo` | `[Long]` | A collection of relation numbers for the relations **from** the table, in priority order. | | `toRelationsNo` | `[Long]` | A collection of relation numbers for the relations **to** the table. | | `processingsNo` | `[Long]` | A collection of processing numbers for the processings in the table. | | `reportsNo` | `[Long]` | A collection of report numbers for the reports in the table. | ### Relations The following properties are available for relations: | Property | Type | Description | | -------- | ---- | ----------- | | `relationNo` | `Long` | The relation number. | | `name` | `String` | The translated name of the relation. | | `identifier` | `String` | A language-independent identifier of the relation. | | `fromTableNo` | `Long` | The table number that this relation refers from. | | `toTableNo` | `Long` | The table number that this relation refers to. | | `fromColumnsNo` | `[Long]` | A collection of column numbers that define the from columns. | | `toColumnsNo` | `[Long]` | A collection of column numbers that define the to columns. | | `switchOfColumnNo` | `Long` | The number of a column for this relation to apply to. | | `switchValue` | `Int` | The value that a column needs to have for this relation to apply. E.g. the debit (or credit) account type column on a voucher row that determines whether the debit (or credit) account number column refers to (1) a customer, (2) supplier, (3) general ledger account, or (4) capital asset. | | `noLookup` | `Bool` | `true` if the relation is not applicable for lookup, but e.g. only for (left outer) join purposes in i.a. layouts. | | `necessity` | `Necessity` | Returns one of the following values for how necessary a corresponding reference is: `REQUIRED`, `OPTIONAL`, or `PASS`. | | `deleteRule` | `DeleteRule` | Returns one of the following values to determine behavior when deleting or discarding a parent row: `CASCADE` will also delete or discard child rows, recursively downwards the hierarchy, or `RESTRICT` that demands that no references exist, e.g. to reject deletion of accounts with transactions. | ### Columns The following properties are available for columns: | Field | Type | Description | | ----- | ---- | ----------- | | `columnNo` | `Long` | The column number. | | `tableNo` | `Long` | The table number that this column belongs to. | | `name` | `String` | The translated name of the column. | | `identifier` | `String` | A language-independent identifier of the column. | | `orgUnitClass` | `OrgUnitClass` | One of the twelve values `ORGUNITCLASS01` – `ORGUNITCLASS12` if the column is associated with an organisational unit class, else `NONE`. The column will not be visible if the corresponding `CompanyInformation.OrgUnit1Name` – `OrgUnit12Name` column is empty. | | `suggestValueInInterval` | `Bool` | `true` if the column supports suggesting values in intervals. | | `breakValues` | `Bool` | `true` if the column by default should show an aggregated value on group and total rows. | | `accumulateOnMerge` | `Bool` | true if the value in the column should be accumulated when rows are merged, e.g. on collection invoices. | | `recalculation` | `Recalculation` | one of the following values for whether the value in the column should be recalculated according to the actual value on the current, parent row (order line), when page elements (e.g. stock balance or shipment) are joined via an underlying table (e.g. product): `NO`, `UNIT`, or `PRICE`. | | `formatting` | `Formatting` | one of the following values for whether the column should apply formatting defined on the row: `NO`, `ROWFRONT`, or `UNDERLINE`. | | `viewZero` | `ViewZero` | one of the following values for whether 0 should be shown if the column is empty: `NO`, `ZEROIFEMPTY`, or `ZEROIFNOTSUMLINE`. | | `debitCreditType` | `DebitCreditType` | one of the following values for how the memory column should be calculated: `NONE`, `DEBIT`, `CREDIT`. | | `currencyHandling` | `CurrencyHandling` | one of the following values for whether the column is involved in currency calculations: `NORMAL`, `LOGISTICSDOMESTICVALUE`, `ACCOUNTINGCURRENCYVALUE`, `ACCOUNTINGDOMESTICVALUE`, `EUROVALUE`, or `CURRENCY2VALUE`. | | `lookupProcessingNo` | `Long` | The number of the processing that declares the lookup dialog for this column, if any. | ### Domains and domain members The following properties are available for domains: | Field | Type | Description | | ----- | ---- | ----------- | | `domainNo` | `Long` | The domain number. | | `name` | `String` | The translated name of the domain. | | `dataType` | `DataType` | The kind of data that is stored in the column. Can be one of: `INT_8`, `INT_16`, `INT_32`, `INT_64`, `DECIMAL`, `LIMITED_STRING`, `UNLIMITED_STRING`, `BLOB`. | | `lenght` | `Int` | The maximum number of characters that can be stored, or 0 if the domain type is not `LIMITED_STRING`. | | `columnWidth` | `Decimal` | The default column width, measured in the average number of characters that will fit in the column. | | `storeFixedDecimals` | `Bool` | `true` if the domain type is `DECIMAL` and a fixed number of decimals should be stored. | | `displayFixedDecimals` | `Bool` | `true` if the domain type is `DECIMAL` and a fixed number of decimals should be displayed by default. | | `fixedDecimals` | `Int` | The actual number of fixed decimals (up to 6) that may be applied by default if the domain type is `DECIMAL`, else 0. | | `fieldJustification` | `FieldJustification` | Returns one of the following values for how the values (and the column heading in grid view) should be aligned by default: `CENTER`, `LEFT`, or `RIGHT`. | | `fileName` | `Bool` | `true` if the domain type is LimitedString and file name lookup is applicable. | | `domainMembers` | [`Query_UseModel_Domains_DomainMembers`] | A list of domain member objects. | The following properties are available for domain members: | Field | Type | Description | | ----- | ---- | ----------- | | `valueNo` | `Long` | Unique domain member indentification number. | | `identifier` | `String` | A language-independent identifier of the domain member. | | `name` | `String` | The name of the domain member. | | `includeValue` | `Int` | An integer value to include in the name, or -1 if not applicable. | | `groupName` | `String` | The name of the group the domain member belongs to. | | `groupIncludeValue` | `Int` | An integer value to include in the group name, or -1 if not applicable. | | `initiallyOn` | `Bool` | `true` if this flag domain member is intended to be initially ON. | ### Processings The following properties are available for processings: | Field | Type | Description | | ----- | ---- | ----------- | | `processingNo` | `Long` | The processing number. | | `name` | `String` | The translated name of the processing. | | `identifier` | `String` | A language-independent identifier of the processing. | | `description` | `String` | A textual description of the processing. | | `dialogOnly` | `Bool` | `true` if the processing is intended to only show a dialog, typically visualizing one or more columns. Such processings have no parameters, and no processing contributions in the backend, so operations for getting data for a processing dialog, or executing a processing, should not be performed on them. | | `rowIndependent` | `Bool` | `true` if the processing is row-independent. | ### Reports The following properties are available for reports: | Field | Type | Description | | ----- | ---- | ----------- | | `processingNo` | `Long` | The report number. | | `name` | `String` | The translated name of the report. | | `identifier` | `String` | A language-independent report of the processing. | | `description` | `String` | A textual description of the report. | ### Folders The following properties are available for folders: | Field | Type | Description | | ----- | ---- | ----------- | | `folderNo` | `Long` | The folder number. | | `name` | `String` | The translated name of the folder. | | `identifier` | `String` | A language-independent identifier of the folder. | | `isRoot` | `Lool` | `true` if the folder is a root folder. | | `parentFolderNo` | `Long` | The number of the parent folder, or 0 if the folder is a root folder. | | `childFoldersNo` | `[Long]` | A collection of folder numbers for the child folders. | ## Language selection The language for the translations can be specified in two ways: - using the `lang` argument in the query, with one of the following values: `ENGLISH`, `NORWEGIAN`, `SWEDISH`, `DANISH` - using the `langNo` argument in the query, with one of the following values: `44` (English), `45` (Danish), `46` (Swedish), `47` (Norwegian) If both arguments are specified, the `lang` argument is used (`langNo` is ignored). The following two examples are equivalent: ```graphql { title = "Query" } query get_tables_info { useModel { tables(lang: NORWEGIAN) { tableNo name identifier databaseType } } } ``` ```graphql { title = "Result" } query get_tables_info { useModel { tables(langNo: 47) { tableNo name identifier databaseType } } } ``` The `langNo` argument is useful for specifying the language based directly on settings, such as the user's language preference (from the `User` table).