/businessnxtapi/schema section Details the Business NXT GraphQL schema, Relay-like, exposing system and company databases with types named after tables, supporting queries and mutations. 2024-09-24T15:57:29+02:00 # GraphQL Schema Details the Business NXT GraphQL schema, Relay-like, exposing system and company databases with types named after tables, supporting queries and mutations. The Business NXT data model is exposed through a GraphQL schema. The schema defines the set of types that describe the set of possible data you can query from the service. Schemas are built with types (scalar, enumerations, union, input), objects, lists, arguments, intefaces, and other concepts. You can learn more about schemas and types [here](https://graphql.org/learn/schema/). Business NXT GraphQL uses a [Relay](https://relay.dev/)-like schema, although not fully compliant. Relay is a specification for defining the GraphQL schema used at Facebook (where GraphQL was created) and open sourced. You can read about it [here]( https://itnext.io/improve-your-graphql-schema-with-the-relay-specification-8952d06998eb). The VBNXT GraphQL implementation differs from the Relay specification by not providing the `Node` base type and the `edges` field for a type. The Business NXT data model is formed of two database types: - a system database, containing information such as companies and company groups, users and user groups, folders, windows, active session, and various other data that applies to the whole application - a company database, containing company specific information (such as associates, general ledger accounts, orders, vouchers, and many others) Every table in the database model, regardless it's a system or company table, is exposed in the schema with a type that has the same name as the table (the name that appears in the model, not the actual SQL name in the database). This type includes the table columns and their type, as well as relations to other columns. For instance, the table for the general leger accounts is available through the type called `GeneralLedgerAccount`. However, the schema defines different sets of types for queries and mutations. /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:  Here, you will learn about reading data. For inserts, updates, and deletes, see [The Mutation Type](../mutations/mutation.md). /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 | | ----------- | --- | |  |  | ## 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:  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:  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. /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:  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. /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`.  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:  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 | | --------------------- | ---------------------------- | |  |  | 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 } } } } } ``` /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`.  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:  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 } ] } } } } ``` /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.  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 /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.  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*  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": "..." } } ``` /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). /businessnxtapi/schema/mutations section Guides you on using mutations for inserts, updates, deletes, and running tasks within a GraphQL schema. 2024-09-24T15:57:29+02:00 # Mutations Guides you on using mutations for inserts, updates, deletes, and running tasks within a GraphQL schema. You can perform inserts, updates, and deletes on all the company and system tables. This is possible with the `mutation` operation. Additional mutation operations include running processings and reports as well as executing queries asynchronously. Mutations are available with the `mutation` field at the top of the GraphQL schema, as shown in the following image:  /businessnxtapi/schema/mutations/mutation page Root Mutation type defines operations on company and system tables, enabling create, update, delete, and asynchronous queries. 2024-09-24T15:57:29+02:00 # The Mutation Type Root Mutation type defines operations on company and system tables, enabling create, update, delete, and asynchronous queries. The `Mutation` type is the root of all types that define table objects used in these operations, in the same way the `Query` type was the root for all types used in reading operations. In fact, `Mutation` and `Query` are very similar, as `Mutation` contains the following fields: | Field | Description | | ----- | ----------- | | `useCompany` | Provides access to operations on the company tables. | | `useCustomer` | Provides access to operations on the system tables. | | `asyncQuery` | A field for executing another GraphQL query asynchronously. See [Async queries](../async.md). |  The type of the `useCompany` field is `Mutation_UseCompany`. This type contains a field for each of the create, update, and delete operation for every company table. Similarly, the type of the `useCustomer` field is `Mutation_UseCustomer`. This type contains a field for each of the create, update, and delete operation for every system table. You can see the two types, `Mutation_UseCustomer` and `Mutation_UseCompany`, side-by-side in the following table: | System table | Company table | | ----------- | --- | |  |  | /businessnxtapi/schema/mutations/inserts page GraphQL Insert Operations - Define _create mutation fields, arguments, type structure, and example queries, emphasizing field order and inserting between records. 2025-04-15T09:48:42+02:00 # Insert operations GraphQL Insert Operations - Define _create mutation fields, arguments, type structure, and example queries, emphasizing field order and inserting between records. Insert operations are available through a field having the name of the form `_create` of a type having the name of the form `__Result`. For instance, for the `Associate` table, the field is called `associate_create` and its type is `Mutation_UseCompany_Associate_Result`. The form of this operation is the following: ```graphql associate_create(values: [Associate_Input!]!, insertAtRow: FilterExpression_Order, insertPosition: InsertPosition): Mutation_UseCompany_Associate_Result ``` The `_create` field has several arguments: - one mandatory called `values` which is a non-nullable array of objects of type `_Input`. For instance, for the `Associate` table, the input type is `Associate_Input`. - one optional called `insertAtRow` which is a filter expression that identifies the row where the new records should be inserted. - one optional called `insertPosition` which is an enum of type `InsertPosition` that can have the values `BEFORE` and `AFTER`, which is used in conjunction with the `insertAtRow` argument to define the direction of the insertion of the new records. - one optional called `suggest` which is object of type `Suggest__Input`. For instance, for the `Associate` table, this input type is called `Suggest_Associate_Input`. > [!WARNING] > > The `suggest` argument for requesting suggested values is now deprecated. The input types for the `values` argument expose all the columns of the table, except for: - the primary key column (which is automatically incremented) - in-memory columns - utility columns `createdDate` (SQL name `CreDt`), `createdTime` (SQL name `CreTm`), `createdByUser` (SQL name `CreUsr`), `changedDate` (SQL name `ChDt`), `changedTime` (SQL name `ChTm`), and `changedByUser` (SQL name `ChUsr`), which are present in every table. If a table has more than one column as primary keys, then all but the last of these primary key columns must be filled in. The last primary key column is automatically incremented but the others must be explicitly supplied. These are foreign keys to other tables and the records they point to must exist for the operation to succeed. In this case, the type name has the form `_Insert_Input`. The following image shows a snippet of the `Associate_Input` type from the GraphiQL document explorer:  Similarly, the next image shows a snippet of the `AssociateInformation_Insert_Input`, that has two primary key fields: - `associateNo` is also a foreign key to the `Associate` table and must be supplied with an existing value - `lineNo` is a primary key that is auto incremented, and, therefore, not present in the input type.  The mutation result type (`__Result`) has two fields: - `affectedRows` indicated the number of rows successfully affected by the operation (in the case of inserts the number of records that were successfully inserted) - `items` an array of objects affected by the mutation operation (for inserts, these are the records that were successfully inserted). Here is a snippet of the `Mutation_UseCompany_Associate_Result` from the GraphiQL document explorer:  The objects in the `items` field have the same type that is used for query operations. For the `Associate` table, this is called `Associate` and looks like this:  An insert operation has the following form: ```graphql { title = "Query" } mutation insert($cid : Int!) { useCompany(no: $cid) { associate_create(values:[ { name:"Erik Larson", shortName :"Erik", customerNo: 30101 }, { name :"Frida Olson", shortName:"Frida", customerNo: 30102 } ]) { affectedRows items { associateNo customerNo name shortName languageNo } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_create": { "affectedRows": 2, "items": [ { "associateNo": 547, "customerNo": 30101, "name": "Erik Larson", "shortName": "Erik", "languageNo": 0 }, { "associateNo": 548, "customerNo": 30102, "name": "Frida Olson", "shortName": "Frida", "languageNo": 0 } ] } } } } ``` ### Fields order > [!TIP] > > The order of fields given in the `values` argument is important, because the fields are assigned in this user-given order. Listing the fields in some particular order may result in unexpected results (such as fields having default values). Here is an example when creating an order. When `customerNo` is given before `dueDate`, the results are as expected: ```graphql { title = "Query" } mutation create_order($cid : Int!) { useCompany(no : $cid) { order_create( values:[ { customerNo : 10000 dueDate: 20221124 } ] ) { affectedRows items { orderNo dueDate } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_create": { "affectedRows": 1, "items": [ { "orderNo": 6, "dueDate": 20221124, } ] } } } } ``` However, if the `dueDate` is specified before `customerNo`, then the `dueDate` is not set: ```graphql { title = "Query" } mutation create_order($cid : Int!) { useCompany(no : $cid) { order_create( values:[ { dueDate: 20221124 customerNo : 10000 } ] ) { affectedRows items { orderNo dueDate } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_create": { "affectedRows": 1, "items": [ { "orderNo": 6, "dueDate": 0, } ] } } } } ``` ### Suggested values When you create a new record, you can ask the system to automatically fill in values for numerical fields. This is done by specifying `null` for the value of a field. ```graphql { title = "Query" } mutation create_voucher($cid: Int, $batchId: Int!) { useCompany(no: $cid) { voucher_create(values: [ { batchNo: $batchId voucherNo : null voucherDate: null amountDomestic: 1300 creditAccountType: 2 creditAccountNo: 50000 }, { batchNo: $batchId voucherNo : null voucherDate: null amountDomestic: 600 debitAccountType: 3 debitAccountNo: 4300 }, { batchNo: $batchId voucherNo : null voucherDate: null amountDomestic: 700 debitAccountType: 3 debitAccountNo: 1930 }]) { affectedRows items { batchNo voucherNo voucherDate debitAccountNo creditAccountNo amountDomestic } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "voucher_create": { "affectedRows": 3, "items": [ { "batchNo": 10002, "voucherNo": 60002, "voucherDate": 20220101, "debitAccountNo": 0, "creditAccountNo": 50000, "amountDomestic": 1300 }, { "batchNo": 10002, "voucherNo": 60002, "voucherDate": 20220101, "debitAccountNo": 4300, "creditAccountNo": 0, "amountDomestic": 600 }, { "batchNo": 10002, "voucherNo": 60002, "voucherDate": 20220101, "debitAccountNo": 1930, "creditAccountNo": 0, "amountDomestic": 700 } ] } } } } ``` > [!TIP] > > The order of specifying the fields when creating or updating a value is important in Business NXT. > > Therefore, an incorrect order of fields with either explicit or null values (or both) can produce in incorrect results. There are several fields in the data model that support suggesting an interval as a value. These fields are: - `customerNo`, `suppliedNo`, and `exployeeNo` in `Associate` - `capitalAssetNo` in `CapitalAsset` - `resourceNo` in `Resource` Because a `null` value cannot be used to indicate an interval, for these fields an additional field with the `_suggest_interval` exists. These fields are of the type `SuggestIntervalType` that has a `from` and `to` field to define the bounds of the interval. For instance, for the `customerNo` field in the `Associate` table, an `customerNo_suggest_interval` field is available. This is shown in the following example: ```graphql { title = "Query" } mutation CreateAssociate($cid : Int) { useCompany(no :$cid) { associate_create(values:[{ name : "Demo Customer AS", customerNo_suggest_interval : { from : 10000, to : 20000 }, countryNo : 47, currencyNo : null }]) { affectedRows items { associateNo customerNo } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_create": { "affectedRows": 1, "items": [ { "associateNo": 1234, "customerNo": 15726 } ] } } } } ``` The rules of thumb for providing inputs are as follows: - fields must be specified in the correct order - if a field should have a specific value, provide it as an input - if a field should remain unassigned, do not list it in the input - if a field should have a system given value, specify `null` for its value - if a field can have a suggest value in a given interval and you want to specify the interval, use the `_suggest_interval` suffixed field, such as `customerNo_suggest_interval`. If you do not want to specify the interval, use the regular input and give the value `null`. ### Deprecated suggested values A deprecated way of requesting suggested values is using an optional argument of the create mutation fields. This field is called `suggest`, and its type is an input type containing all the fields from the table for which the system can automatically fill in values. > [!WARNING] > > This method should not be used because fields listed in the `suggest` argument are always listed last in the request issued to the backend. Since the order of fields is important (as previously mentioned) this will produce incorrect results in many cases. > [!NOTE] > > This argument is deprecated and will soon be removed. Here is an example of the `Suggest_Associate_Input` type for the `Associate` table:  The field of all these input types are of one of the following two types: - `Boolean`, in which case you must use the value `true` to include the column in the suggestions list. - `SuggestIntervalType`, in which case you must specify a `from` and `to` value to bound the numeric interval for the value of the field.  > [!NOTE] > > A limited number of fields in the data model support specifying an internal. Here is an example for suggesting values: ```graphql { title = "Query" } mutation create_voucher($cid : Int!, $bno : Int!, $cno : Int!) { useCompany(no : $cid) { voucher_create( values: [{ batchNo : $bno debitAccountNo : 1930 creditAccountNo: $cno customerNo : $cno amountDomestic : 100 }], suggest : { voucherNo : true, voucherDate : true valueDate : true } ) { affectedRows items { batchNo voucherNo voucherDate valueDate } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "voucher_create": { "affectedRows": 1, "items": [ { "batchNo": 2, "voucherNo": 60003, "voucherDate": 20220715, "valueDate": 20220715 } ] } } } } ``` Here is an example with suggesting an interval: ```graphql { title = "Query" } mutation create_customer($cid : Int!) { useCompany(no : $cid) { associate_create( values: [{ name : "Test Customer }], suggest : { customerNo : { from : 10000 to : 19999 } } ) { affectedRows items { associateNo customerNo name } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_create": { "affectedRows": 1, "items": [ { "associateNo": 187, "customerNo": 10928, "name": "Test Customer", } ] } } } } ``` ### Assigning fields from unset variables When fields are assigned from variables, but the variables are not set, the fields are ignored as they were not provided in insert and update operations. For instance, consider the following query: ```graphql { title = "Query" } mutation update_c_ompany( $companyID: Int! $customerNo: Int!, $phone: String, $mobilePhone: String, $privatePhone: String, ) { useCompany(no: $companyID) { associate_update( filter: { customerNo: {_eq: $customerNo } }, value: { phone: $phone mobilePhone: $mobilePhone privatePhone: $privatePhone } ) { affectedRows } } } ``` ```graphql { title = "Result" } { "companyID" : 1234567, "customerNo": 11000, "phone": "9411223344" } ``` The `$mobilePhone` and `$privatePhone` are not set in the variables dictionary. Therefore, the query becomes equivalent to the following: ```graphql mutation update_c_ompany( $companyID: Int! $customerNo: Int!, $phone: String ) { useCompany(no: $companyID) { associate_update( filter: { customerNo: { _eq: $customerNo } }, value: { phone: $phone } ) { affectedRows } } } ``` ### Insert between existing records It is possible to insert new records between existing ones. This is done by using the `insertAtRow` and `insertPosition` optional arguments for the `_create` fields. The `insertAtRow` argument defines a filter for indetifying the position where the new records should be inserted. This filter is applied for every object in the `values` array. Although the filter can be anything, it should include the primary key at least partially. If more that one row matches the filter, the first one is considered the insertion point. The `insertPosition` argument defines where the new records should be inserted in relation to the row identified by the `insertAtRow` filter. The possible values are `BEFORE` and `AFTER`. This argument is optional and if missing, the default value is `BEFORE`. > [!NOTE] > > The objects in the `values` array should not assign values to the primary key fields. These are automatically assigned by the system from the values of the record matching the `insertAtRow` argument. ```graphql { title = "Query" } mutation insert_order_line($cid : Int!, $ono : Int!, $pno3 : String) { useCompany(no : $cid) { orderLine_create(values:[ { productNo : $pno3, quantity : 1 } ], insertAtRow: { _and : [ {orderNo : {_eq : $ono}}, {lineNo : {_eq : 1}}, ]}, insertPosition : AFTER) { affectedRows items { orderNo lineNo sortSequenceNo productNo quantity } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "orderLine_create": { "affectedRows": 1, "items": [ { "orderNo": 2024, "lineNo": 3, "sortSequenceNo": 2, "productNo": "PRO-01", "quantity": 1 } ] } } } } ``` To understand how this works, let's consider the following example of inserting new order lines between existing ones for a particular order. Let's start with the following data for the `OrderLine` table: | OrderNo | LineNo | SortSequenceNo | ProductNo | | ------- | ------ | -------------- | --------- | | 2024 | 1 | 1 | PRO-01 | | 2024 | 2 | 2 | PRO-02 | If we insert a new line with product number `PRO-03` _before_ the line with the primary key `OrderNo=2024, LineNo=1`, the result will be: | OrderNo | LineNo | SortSequenceNo | ProductNo | | ------- | ------ | -------------- | --------- | | 2024 | 1 | 2 | PRO-01 | | 2024 | 2 | 3 | PRO-02 | | 2024 | 3 | 1 | PRO-03 | However, if we insert the same line but _after_ the one with the primary key `OrderNo=2024, LineNo=1`, the result will be: | OrderNo | LineNo | SortSequenceNo | ProductNo | | ------- | ------ | -------------- | --------- | | 2024 | 1 | 1 | PRO-01 | | 2024 | 2 | 3 | PRO-02 | | 2024 | 3 | 2 | PRO-03 | On the other hand, if we insert two order lines at the same time, one for product `PRO-03` and one for product `PRO-04`, the result will be the following when inserting _before_ the record with the primary key `OrderNo=2024, LineNo=1`: | OrderNo | LineNo | SortSequenceNo | ProductNo | | ------- | ------ | -------------- | --------- | | 2024 | 1 | 3 | PRO-01 | | 2024 | 2 | 4 | PRO-02 | | 2024 | 3 | 1 | PRO-03 | | 2024 | 4 | 2 | PRO-04 | Similarly, if the insertion occurs _after_, the result will be: | OrderNo | LineNo | SortSequenceNo | ProductNo | | ------- | ------ | -------------- | --------- | | 2024 | 1 | 1 | PRO-01 | | 2024 | 2 | 4 | PRO-02 | | 2024 | 3 | 3 | PRO-03 | | 2024 | 4 | 2 | PRO-04 | This is because the insertion point is determined each time a new record is inserted (and not just once for all records in a create operation). /businessnxtapi/schema/mutations/updates page API documentation detailing update operations using GraphQL. Highlights use of filter/value pairs and filters/values pairs, with examples. 2025-04-15T09:48:42+02:00 # Update operations API documentation detailing update operations using GraphQL. Highlights use of filter/value pairs and filters/values pairs, with examples. Update operations are available through a field having the name of the form `_update` of a type having the name of the form `__Result`. For instance, for the `Associate` table, the field is called `associate_update` and its type is `Mutation_UseCompany_Associate_Result`. The result type is the same for inserts, updates, and deletes. The form of this operation is the following: ```graphql associate_update( filter: FilterExpression_Associate value: Associate_Input filters: [FilterExpression_Associate] values: [Associate_Input!] ): Mutation_UseCompany_Associate_Result ``` The `_update` field has two pairs arguments, that you must use together. The first pair is: - An argument called `filter` which defines the selection filter for the records to be updated. The type of this argument is the same used for filters in queries and will be described in details later in the document. - An argument `value` which is an object of type `_Input`. For instance, for the `Associate` table, the input type is `Associate_Input`. This is the same input type used for insert operations for tables with a single primary key column. For tables with more than one primary key columns, it's a type slightly different than the one used for inserting, none of the primary key columns being available for updating. You can use this if you want to update all records matching the filter with the same values. If you want to update each (or different) record with a different value, you must use the second pair of arguments: - An argument called `filters` which is a list of filters, one for each record to be updated. The type of the elements of `filters` is the same used as the type of `filter. - An argument called `values` which is a list of objects of type `_Input`. > [!NOTE] > > The `filter` \ `value` pair is considered deprecated and will be removed in the future. You should use the `filters` \ `values` pair instead. An update operation has the following form (in this example, we set the `languageNo` field to 44 for all associates that have the `associateNo` field greater than 546): ```graphql { title = "Query" } mutation update_lang($cid : Int!) { useCompany(no:$cid) { associate_update( filter : {associateNo : {_gt : 546}}, value:{languageNo:44}) { affectedRows items { associateNo customerNo name shortName languageNo } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_update": { "affectedRows": 2, "items": [ { "associateNo": 547, "customerNo": 30101, "name": "Erik Larson", "shortName": "Erik", "languageNo": 44 }, { "associateNo": 548, "customerNo": 30102, "name": "Frida Olson", "shortName": "Frida", "languageNo": 44 } ] } } } } ``` You can collect multiple edits in a single update operation. You can do that with the use of `filters` and `values`. The following examples shows how to update multiple lines of an order with the different values. One filter and one value are provided for each line: ```graphql mutation multi_order_line_update($cid : Int!, $ono : Int!) { useCompany(no : $cid) { orderLine_update( filters: [ # filter for line 1 {_and:[ {orderNo : {_eq : $ono}} {lineNo : {_eq : 1}}]}, # filter for line 2 {_and:[ {orderNo : {_eq : $ono}} {lineNo : {_eq : 2}}]} ] values: [ # value for line 1 { priceInCurrency : 199.99, invoiceNow : 1.0 }, # value for line 2 { priceInCurrency : 59.99, invoiceNow : 1.0 }, ]) { affectedRows items { orderNo lineNo quantity priceInCurrency invoiceNow } } } } ``` You can transform any query using `filter` and `value` into a query using `filters` and `values`. An example is the following query: ```graphql { title = "filter & value" } mutation update_lang($cid : Int!) { useCompany(no:$cid) { associate_update( filter : {associateNo : {_gt : 546}}, value:{languageNo:44}) { affectedRows items { associateNo customerNo name shortName languageNo } } } } ``` ```graphql { title = "filters & values" } mutation update_lang($cid : Int!) { useCompany(no:$cid) { associate_update( filters : [{associateNo : {_gt : 546}}], values: [{languageNo:44}] ) { affectedRows items { associateNo customerNo name shortName languageNo } } } } ``` /businessnxtapi/schema/mutations/deletes page Delete table records using _delete(filter), with results shown in affectedRows. Works similarly to update filters. Example provided. 2025-04-15T09:48:42+02:00 # Delete operations Delete table records using _delete(filter), with results shown in affectedRows. Works similarly to update filters. Example provided. Delete operations are available through a field having the name of the form `_delete` of a type having the name of the form `__Result`. For instance, for the `Associate` table, the field is called `associate_delete` and its type is `Mutation_UseCompany_Associate_Result`. The result type is the same for inserts, updates, and deletes. The form of this operation is the following: ```graphql associate_delete(filter: FilterExpression_Associate): Mutation_UseCompany_Associate_Result ``` The `_delete` field has a single argument called `filter` which defines the selection filter for the records to be delete. This is the same filter used for update operations. A delete operation has the following form (in this example we delete all the associates that have the `associateNo` field greater than 546): ```graphql { title = "Query" } mutation delete_associate($cid : Int!) { useCompany(no: $cid) { associate_delete(filter : { associateNo : {_gt : 546}}) { affectedRows } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "associate_delete": { "affectedRows": 2 } } } } ``` The value of the `items` field of the return type will always be `null` for a delete operation. The API does not return the value of the records that were deleted. /businessnxtapi/schema/mutations/processings page Backend processings perform tasks like canceling orders or importing data via GraphQL mutations, potentially running long and requiring specific table fields like order_processings. 2025-04-15T09:48:42+02:00 # Processings Backend processings perform tasks like canceling orders or importing data via GraphQL mutations, potentially running long and requiring specific table fields like order_processings. Processings are business logic operations that are performed in the backend. Examples of processings include canceling or finishing an order, validating or updating batches, importing data into a company, or create payment suggestions. In GraphQL, these are available as mutations. > [!WARNING] > > Processings are potentially long-running operations. Depending on the nature of the processing and the volume of data it has to process (and return) may increase significantly and exceed the timeout for the HTTP request. In this case, you would get back an error status even though the process continues to run in the background and may finish successfully. Processings are associated with a table and a table can have multiple processes. For each table, a field called `_processings` is available. This is a field of the type `Processings`. For instance, for the `Order` table, the field is called `order_processings` and its type is called `OrderProcessings`. You can see this in the following image:  Under this field, there is one field for each available processing. These fields have the name of the processing. For instance, the `Order` table has processings called `finish`, `cancel`, `confirm`. These are available as fields under the `order_processings` field. This is exemplified here:   Here is an example for executing a processing. The following GraphQL requests executes the finish process on an order. ```graphql { title = "Query" } mutation finish_order($cid : Int!, $orderno : Int!) { useCompany(no : $cid) { order_processings { finish( args : { finishType :0 }, filter : { orderNo : {_eq : $orderno} } ) { succeeded items { handledOrderLine { lineNo finishedNow } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_processings": { "finish": { "succeeded": true, "items": [ { "handledOrderLine": [ { "lineNo": 1, "finishedNow": 1 } ] } ] } } } } } ``` Each processing field has one or two arguments, as follows: - `filter`: allows for selecting the table rows that will be processed. This is the same filter used for querying data from the table. You can read more about that here: [Filtering](../../features/filtering.md). - `args`: is an optional argument present for the processings that have parameters. When present, this is an object of a type with the name having the form `Processing__Parameters`, such as in `OrderProcessing_Transfer_Parameters`. The fields of this type are different for each processing. The `args` field allows to specify arguments for the processing. This could be either: - arguments for the overall processing, which are available in the root, or - arguments for each processed row, which are provided as an array, one element for each row. It is possible to have recursive data, i.e. arrays of arrays, on multiple levels. The following example shows possible arguments for the finish order processing: ```graphql mutation run_processing($cid : Int!, $orderno : Int) { useCompany(no : $cid) { order_processings { finish( args: { finishType : 0 group : [ { key : "1" quantity : 1 }, { key : "2" quantity : 1 }, { key : "3" quantity : 2 } ] }, filter : { orderNo : { _gte : $orderno} } ) { succeeded items { handledOrderLine { lineNo finishedNow } } } } } } ``` In this example: - `finishType` is an argument for the entire processing - `group` is a node containing a collection of objects with two properties, `key` and `quantity`. Each object in this collection is used for one processed row (which are selected here with a filter). If the number of rows is greater than the provided arguments (elements of the array) the rest of the rows are processed as if no arguments were supplied. A similar structure is used for returning results. There are results: - per processing, available directly in the root of the result object. All processings have a Boolean field called `succeeded` that indicate whether the processing completed successfully or not. Additional results, are available at this level. - per row, available under the `items` field, which is an array. Each element in the array represents the result for a processed row. For the previous request of order finishing, the following is a potential result: ```json { "data": { "useCompany": { "order_processings": { "finish": { "succeeded": true, "items": [ { "handledOrderLine": [ { "lineNo": 1, "finishedNow": 1 }, { "lineNo": 2, "finishedNow": 1 } ] }, { "handledOrderLine": [ { "lineNo": 1, "finishedNow": 1 }, { "lineNo": 2, "finishedNow": 1 }, { "lineNo": 3, "finishedNow": 2 } ] }, { "handledOrderLine": [ { "lineNo": 1, "finishedNow": 1 } ] } ] } } } } } ``` You can see here that for each order that was processed, there is an object in the `items` array. The property `handledOrderLine` is also an array and contains one object for each order line. The following table shows another example of a process running on the `CompanyInformation` table that fetches access restrictions. ```graphql { title = "Mutation" } mutation get_access($cid : Int!) { useCompany(no : $cid) { companyInformation_processings { getAccessRestrictions { succeeded tableAccess { tableNo noTableRead noInsert noUpdate noDelete } functionAccess { processingAccess { processingNo noProcessingAccess } reportAccess { reportNo noReportAccess } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "companyInformation_processings": { "getAccessRestrictions": { "succeeded": true, "tableAccess": [ { "tableNo": 361, "noTableRead": 0, "noInsert": 1, "noUpdate": 1, "noDelete": 1 }, { "tableNo": 362, "noTableRead": 0, "noInsert": 1, "noUpdate": 1, "noDelete": 1 }, ... ], "functionAccess": [ { "processingAccess": [ { "processingNo": 754, "noProcessingAccess": 1 }, { "processingNo": 1045, "noProcessingAccess": 1 }, ... ], "reportAccess": [ { "reportNo": 251, "noReportAccess": 0 }, { "reportNo": 162, "noReportAccess": 0 }, ... ] } ] } } } } } ``` From this snippet, you can see that: - the processing returns information about access restrictions to tables, processings, and reports - table access information is gathered under the `tableAccess` field, which is an array of objects, each containing information about a single table - processing and report access information is available under the `processingAccess` and `reportAccess` fields, both being children of the `functionAccess` field. Also, like `tableAccess`, both `processingAccess` and `reportAccess` are arrays This example shows a pattern that defines the general structure of processing results. Notice that even though `tableAccess` and `functionAccess` are themselves arrays, they represent overall processing data, and not results per row. > [!TIP] > > If you don't know what processings are available for each table, or what each process is doing, you can get this information using the schema explorer, available in both GraphiQL and Insomnia. The images at the beginning of this page demonstrate this. /businessnxtapi/schema/mutations/reports page Comprehensive guide on executing and customizing report mutations, including parameters and result structures, for generating business documents in software applications. 2025-04-15T09:48:42+02:00 # Reports Comprehensive guide on executing and customizing report mutations, including parameters and result structures, for generating business documents in software applications. Reports are business logic operations that typically result in one or more documents. Reports have many similarities with [processings](processings.md). They have the very same structure for parameters and results. They are also available as mutation requests. However, unlike processings, they return documents and attachments. > [!WARNING] > > Line, processings, they are potentially long running operations. The time to execute a report may exceed the timeout of the HTTP request. In this case, you would get back an error status even though the process continues to run in the background and may finish successfully. Reports are associated with a table and a table can have multiple reports. However, not all tables have reports. For each table that provides reports, a field called `_reports` is available. The type of this field is called `Reports`. For instance, for the `Order` table, the reports field is called `order_reports` and its type is called `OrderReports`. You can see this in the following image:  Under each field of this form, there is one field for each report available for the table. For instance, for the `Order` table, there are multiple reports such as `pickList`, `packingSlips`, `orderConfirmations`, `orderPrints`, etc. These are available under the field `order_reports`. This is shown in the next image:  The following query shows an example for executing the order confirmation report for an order. ```graphql { title = "Query" } mutation run_report($cid : Int!, $ono : Int!) { useCompany(no : $cid) { order_reports { orderConfirmations( filter : { orderNo :{_eq: $ono} } ) { succeeded documents { name content } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_reports": { "orderConfirmations": { "succeeded": true, "documents": [ { "name": "200022.pdf", "content": "JVBERi0xLjQKJdDExrT...", } ] } } } } } ``` Reports may have multiple arguments, as shown in the following table. All these arguments are optional. | Field | Type | Description | | ----- | ---- | ----------- | | `filter` | `FilterExpression_` | Allows for selecting the table rows that will be processed. This is the same filter used for querying data from the table. You can read more about that here: [Filtering](../../features/filtering.md). | | `args` | `Reports__Parameters` | Provides various arguments for the reporting process. | | `returnDocuments` | `bool` | Indicates whether documents should be returned as part of the result. When present, their content is available as base64-encoded text. | | `splitAttachments` | `bool` | Indicates whether attachments, when available, should be split into separate PDFs. Attachment content is available as base64-encoded text. | | `approval` | `bool` | When this value is `true` (which is the default if not specified) tables in the database will be updated, if applicable for the report. E.g. for order documents (like invoices), the tables with order lines and reservations will accumulate quantities processed so far. The documents may be archived in the database, and vouchers may be produced. The `Approval` property can be set to `false` when you only preview results. | | `documentDate` | `date` | Can be used by the business logic, depending on the actual report (e.g. as the invoice date), as well as be displayed on the printed form. | | `valueDate` | `date` | Used when producing vouchers (as part of the approval processing), to determine the accounting period and VAT period, if applicable for the report. | | `formNo` | `int` | The form number for the document type/form type. The value 0 (the default) indicates the default form. | | `printDestination` | `PrintDestination` | Specify different options for the result documents. | The following options are available for printing destination: | Destination | Description | | ----------- | ----------- | | `PRINT_TO_PDF` | Produces PDF files. This is the default value. | | `PRINT_TO_DEFINED_DESTINATIONS` | Distributes the documents according to "Document delivery methods" on the order/associate, if applicable for the report. The destination can be a selection for whether to use one or more of mail, e-mail, fax, EDI, and AutoInvoice, depending on the actual order; suggested from the customer or supplier. | | `SEND_EMAIL` | Sends PDFs by e-mail. | | `ONLY_APPROVAL` | Approves the documents without printing them (i.e. writes only to the database). | The execution of the order confirmation report is shown again here, this time with all the possible arguments: ```graphql { title = "Mutation" } mutation run_report($cid : Int!, $ono : Int!) { useCompany(no : $cid) { order_reports { orderConfirmations( returnDocuments :true splitAttachments : true approval : true formNo : 0 printDestination : PRINT_TO_PDF documentDate : "2022-03-01" valueDate : "2022-03-01" filter : { orderNo :{_eq: $ono} } ) { succeeded documents { name content attachments { name content } } } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_reports": { "orderConfirmations": { "succeeded": true, "documents": [ { "name": "200022.pdf", "content": "JVBERi0xLjQKJdDExrT...", "attachments": [] } ] } } } } } ``` > [!TIP] > > If you don't know what reports are available for each table, or what each report is doing, you can get this information using the schema explorer, available in both GraphiQL and Insomnia. The images at the beginning of this page demonstrate this. /businessnxtapi/schema/async page Async queries allow for the execution of GraphQL queries and mutations asynchronously, useful for long-running operations that might exceed timeouts. 2025-04-15T09:48:42+02:00 # Async queries Async queries allow for the execution of GraphQL queries and mutations asynchronously, useful for long-running operations that might exceed timeouts. Some GraphQL requests, such as the execution of processings or reports, may take long times to execute. These times could exceed internal GraphQL timeouts or HTTP timeouts (for instance when you're executing a query from a browser-based IDE such as GraphiQL), in which case the status and the result of the execution will be lost, even though it would complete successfully. To avoid this problem, you can run any Business NXT GraphQL request asynchronously. This works as follows: - You request the excution of a query ascynhronously. The service will queue the request and immediatelly return an operation identifier. This identifier will then be used to fetch the result. - You will ask for the result of the async query using the previously returned operation identifier. If the operation is taking a long time to execute, you will have to periodically poll for the result. - In addition, you can request the list of all your asynchronous queries, which would return their operation identifier and status. > [!NOTE] > > An asynchronous query carries the request of executing a synchronous query/mutation. The system executes this query normally (in a synchronous way) but hides the details from the caller and then makes its result available on a further request upon completion. > [!TIP] > >There are several important thing to note: > > - You can only execute a maximum of 10 asynchronous query at a time and you can only start a new one 5 seconds after the previous one has been started. (_Note_: These numbers may be subject to change.) > - You cannot execute an asynchronous query within an asynchronous query. > - The result of the asynchronous query is the stringified JSON of the synchronous query. > [!WARNING] > > Aynchronous queries cannot contain join operations (`joinup_` and `joindown_` fields) or an `@export` directive. > > If you try to execute an asynchronous query containing any of these, you will not get the expected results. This is because an async query containing joins or the `@export` directive implies a sequence of requests to the backend: a first one to retrieve a set of data, and then another one after, based on the previously retrieved data. However, in the case of async queries, the first batch of requests are sent to the backend, but the response is not awaited for. Instead, the API returns an operation ID that is later used to query for the result. Only when you ask for the result of the async query, the response from the backend is read and interpreted. Therefore, the expected sequence of requests to the backend cannot be executed in the case of async queries. > [!NOTE] > > The result of an asynchronous request is stored on the server for 96 hours. During this time, you can read the result multiple times. After this period, the result is deleted and will no longer be available for reading. ## Making an asynchronous request An asynchronous execution is requested with a mutation operation using the `asyncQuery` field.  The `asyncQuery` field has two arguments: | Field | Type | Description | | ----- | ---- | ----------- | | `query` | `String` | Contains the GraphQL query to be executed ascynhronously (it can either be a query or a mutation). | | `args` | `Dictionary` | Contains the key-value pairs representing the arguments for the query. |  The result of executing an asynchronous query contains the following fields: | Field | Type | Description | | ----- | ---- | ----------- | | `operationId` | `String` | The identifier of the requested operation. | | `status` | `TaskStatus` | An enumeration with several possible values: `ERROR`: the operation execution finished because of an error, `SUCCESS`: the request has completed successfully, and `QUEUED`: the request has been received and placed in a queue but the execution has not started. | Here is an example that requests the first five general ledger accounts in an asynchronous way: ```graphql { title = "Query" } mutation { asyncQuery(query:""" query { useCompany(no: 123456) { generalLedgerAccount(first: 5) { totalCount items { accountNo name } } } } """) { operationId status } } ``` ```graphql { title = "Result" } { "data": { "asyncQuery": { "operationId": "29bb4abe-5299-4542-b1e4-c05aca11c3ed", "status": "QUEUED" } }, "extensions": { "vbnxt-trace-id": "43c281879ec08b257563ddbb5668a12b" } } ``` Typically, you would need to pass arguments to the query that needs to be executed asynchronously. This is done using the `args` argument of the `asyncQuery` field. The next example shows the previous query modified to contain arguments for the company number and the page size: ```graphql { title = "Query" } mutation ($args: Dictionary) { asyncQuery(query:""" query read($cid : Int!, $pagesize : Int!){ useCompany(no: $cid) { generalLedgerAccount(first: $pagesize) { totalCount items { accountNo name } } } } """, args: $args) { operationId status } } ``` ```graphql { title = "Variables" } { "args" : { "cid" : 123456, "pagesize" : 5 } } ``` The returned `operationId` must be used to read the result with another request. ## Reading the result of an asynchronous request Reading the result of an asynchronous request is done with the `asyncResult` field of the [Query](queries/query.md) type. This field has a single argument, `operationId`, which is a string containing the identifier returned by an `asyncQuery` request.  ```graphql { title = "Query" } query fetch_results($oid : String!) { asyncResult(id: $oid) { operationId status error data createdAt completedAt } } ``` ```graphql { title = "Result" } { "data": { "asyncResult": { "operationId": "caa8812d-fdb2-4546-86a9-eab30e9dde20", "status": "SUCCESS", "error": null, "data": "{\"data\":{\"useCompany\":{\"generalLedgerAccount\":{\"totalCount\":342,\"items\":[{\"accountNo\":1000,\"name\":\"Forskning og utvikling\"},{\"accountNo\":1020,\"name\":\"Konsesjoner\"},{\"accountNo\":1030,\"name\":\"Patenter\"},{\"accountNo\":1040,\"name\":\"Lisenser\"},{\"accountNo\":1050,\"name\":\"Varemerker\"}]}}}}", "createdAt": "2022-09-23T12:34:07Z", "completedAt": "2022-09-23T12:34:11Z" } }, "extensions": { "vbnxt-trace-id": "ad02666f0531856aaadfc74625dc37f1" } } ``` The result is available as a string containing the JSON result of the requested query. You would deserialize this just as you'd do if the request was executed synchronously. However, data may not be available immediatelly. If the operation is long-runnning and not yet completed, the returned status would be `QUEUED`, as shown next: ```json { "data": { "asyncResult": { "operationId": "caa8812d-fdb2-4546-86a9-eab30e9dde20", "status": "QUEUED", "error": null, "data": "null", "createdAt": "2022-09-23T12:34:07Z", "completedAt": null } }, "extensions": { "vbnxt-trace-id": "eb52491bfe7268e89d61a1121c0453af" } } ``` In this case, you have to poll periodically for the result until the status changes to `SUCCESS` or `ERROR`. ## Getting the list of asynchronous request The result of the requests that executed asynchronously is available for 96 hours on the server. During this time, you can read it multiple times. If you want to know what asynchronous operations you have requested and are still available on the server, you can do so with a query using the `asyncRequests` field. What you get back is the operation identifier and the status of each operation: ```graphql { title = "Query" } query { asyncRequests { operationId status } } ``` ```graphql { title = "Result" } { "data": { "asyncRequests": [ { "operationId": "caa8812d-fdb2-4546-86a9-eab30e9dde20", "status": "SUCCESS" }, { "operationId": "dd2a0f07-bcc2-45fb-bf3f-057b0f5eb5fa", "status": "SUCCESS" }, { "operationId": "c9e979bc-2b03-4b56-91ed-738ffb17dbd4", "status": "QUEUED" } ] }, "extensions": { "vbnxt-trace-id": "c7415ed35a81b7362c602024ff88b77f" } } ``` ## Deleting an asynchronous request An async request that has been queue for execution can be deleted if it is not running (it's either queued or finished). This can be done using the `asyncQuery_delete` field in the mutation scheme. What you need to provide is the operation identifier of the request you want to delete: ```graphql { title = "Query" } mutation delete_async_query($id : String!) { asyncQuery_delete(id: $id) { operationId status } } ``` ```graphql { title = "Result" } { "data": { "asyncQuery_delete": { "operationId": "4918391E-95C2-46E3-B771-3AD99BCA78B0", "status": "DELETED" } } } ``` The possible status values are: | Status | Description | |------- | ----------- | | `DELETED` | The request job has been deleted. | | `DENIED` | The request job cannot be deleted. | | `NOT_FOUND` | The request operation identifier was not found. | | `UNKNOWN` | An unexpected error has occurred. | /businessnxtapi/schema/datetime page Date and time fields API documentation - describes field formats, conversion examples, and usage in GraphQL queries and mutations. 2025-04-15T09:48:42+02:00 # Date and time fields Date and time fields API documentation - describes field formats, conversion examples, and usage in GraphQL queries and mutations. ## Basic types The Business NXT data model has featured fields that represent either dates or time values. These are stored in the database as integer values with some specific formats, as described in the following table: | Type | Format | Examples | | ---- | ------ | -------- | | date | `yyyymmdd` | `20190101` for 1 January 2019 | | time | `hhmm` | `0` for `00:00`, `101` for `01:01`, `2330` for `23:30` | | precision time | `(((h * 60 + m) * 60) + s) * 1000 + ms` | `0` for `00:00:00.000`, `3661001` for `01:01:01.001`, `2862000500` for `23:30:30.500` | Since October 2024, all tables contain two new timestamp fields for created and changed date-time values. These are T-SQL `datetime2` values (which in GraphQL are represented as `DateTime` values). | Type | Format | Examples | | ---- | ------ | -------- | | timestamp | `yyyy-MM-dd HH:mm:ss[.nnnnnnn]` | `2024-10-01 12:45:33.798` for 1 October 2024, 12:45:33.798 | > [!TIP] > > These fields, `createdTimestamp` and `changedTimestamp`, should be preferred for use in filters due to their higher precision. > > However, these fields are not available for already existing rows, only for new or modified rows after the release date. > [!TIP] > > The date/time values represent the local time of the server. They do not indicate the timezone. Since the GraphQL API exposes the data model as it is internally defined, the date and time fields appear in the API as integers. Here is an example: ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order( first : 2, filter : {orderDate : {_gt : 20150101}} ) { items { orderNo orderDate createdDate createdTime changedDate changedTime } } } } ``` ```json { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "orderNo": 75, "orderDate": 20150115, "createdDate": 20200511, "createdTime": 1325, "changedDate": 20200511, "changedTime": 1325, "changeTimeInMs": 48308716 }, { "orderNo": 76, "orderDate": 20150115, "createdDate": 20200511, "createdTime": 1325, "changedDate": 20200511, "changedTime": 1325, "changeTimeInMs": 48308716 } ] } } } } ``` The use of the date and time fields require conversions as follow: For date: ``` { title = "From integer" } value = 20210122 year = value / 10000 value = value % 10000 month = value / 100 day = value % 100 ``` ``` { title = "To integer" } value = year \* 10000 + month \* 100 + day ``` For time: ``` { title = "From integer" } value = 1245 hour = value / 100 minute = value % 100 ``` ``` { title = "To integer" } value = hour \* 100 + minute ``` For precision time: ``` { title = "From integer" } value = 2862000500 ms = value % 1000 timeValue = value / 1000 hour = timeValue / 3600 timeValue = timeValue % 3600 minute = timeValue / 60 second = timeValue % 60 ``` ``` { title = "To integer" } value = (((hour \* 60 + minute) \* 60) + second) \* 1000 + ms ``` The `changeTimeInMs` field is the only precision time field. However, this is only available on a limited number of tables, listed below: - `Associate` - `AssociateReference` - `AssociateInformation` - `Appointment` - `Resource` - `Product` - `Barcode` - `PriceAndDiscountMatrix` - `Order` - `OrderLine` - `IncomingDocumentChange` On the other hand, the new timestamp fields apear as fields of the `DateTime` type, as shown in the following example: ```graphql { title = "Query" } query read_orders($cid : Int!) { useCompany(no: $cid) { order( first : 2, orderBy : {changedTimestamp : DESC}) { items { orderNo orderType customerNo changedDate changedTime changedDateTime changedTimestamp createdDate createdTime createdDateTime createdTimestamp } } } } ``` ```json { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "orderNo": 4656, "orderType": 6, "customerNo": 10002, "changedDate": 20241010, "changedTime": 1022, "changedDateTime": "2024-10-10T10:22:30.399", "changedTimestamp": "2024-10-10T10:22:30.399", "createdDate": 20241010, "createdTime": 1022, "createdDateTime": "2024-10-10T10:22:00", "createdTimestamp": "2024-10-10T10:22:29.958" }, { "orderNo": 1, "orderType": 2, "customerNo": 10002, "changedDate": 20240207, "changedTime": 813, "changedDateTime": "2024-02-07T08:13:14.764", "changedTimestamp": null, "createdDate": 20240217, "createdTime": 1325, "createdDateTime": "2024-02-17T13:25:00", "createdTimestamp": null } ] } } } } ``` ## Date fields and time fields In order to make it easier to work with date and time values, GraphQL is exposing all these fields also in an ISO date format and, respectively, a time format, according to the following table: | Type | Format | Examples | | ---- | ------ | -------- | | date | `yyyy-mm-dd` | `2019-01-01`, `2021-12-31` | | time | `hh:mm` | `00:00`, `01:01`, `23:30` | | precision time | `hh:mm:ss.ms` | `00:00:00.000`, `01:01:01.001`, `23:30:30.500` | To make this possible, every date or time field has a companion with the same name, but the suffix `AsDate` for dates and, respectively, `AsTime` for time. This is exemplified below: | Field | Type | Value | | ----- | ---- | ----- | | `orderDate` | integer | `20211022` | | `orderDateAsDate` | date | `2021-10-22` | | `estimatedTime` | integer | `1710` | | `estimatedTimeAsTime` | string | `"17:10"` | | `changeTimeInMs` | integer | `48308716` | | `changeTimeInMsAsTime` | string | `"13:25:08.716"` | **Note**: There is no natural date type to represent a time value without a date. Therefore, the type of the time fields with the `AsTime` suffix is actually string. These date and time fields are available for: - [connection types](queries/query.md) (used for reading data) - [aggregate types](queries/aggregates.md) (only for the `minimum` and `maximum` aggregate functions) - [input types](mutations/inserts.md) (used with mutations for inserting or updating records) - [filter types](../features/filtering.md) (used for filtering records) Several examples are provided below. Example: reading data from the system. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(first: 2) { items { accountNo name changedDate changedDateAsDate changedTime changedTimeAsTime } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "generalLedgerAccount": { "items": [ { "accountNo": 1000, "name": "Forskning og utvikling", "changedDate": 20210218, "changedDateAsDate": "2021-02-18", "changedTime": 1710, "changedTimeAsTime": "17:10" }, { "accountNo": 1020, "name": "Konsesjoner", "changedDate": 20210219, "changedDateAsDate": "2021-02-19", "changedTime": 1020, "changedTimeAsTime": "10:20" } ] } } } } ``` Example: filtering data. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order( first : 2, filter : { orderDateAsDate : {_gt : "2015-01-01"} } ) { totalCount items { orderNo orderDate orderDateAsDate } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "totalCount": 268, "items": [ { "orderNo": 75, "orderDate": 20150115, "orderDateAsDate": "2015-01-15" }, { "orderNo": 76, "orderDate": 20150116, "orderDateAsDate": "2015-01-16" } ] } } } } ``` Example: input values in insert and update mutations. ```graphql { title = "Query" } mutation create($cid : Int!) { useCompany(no: $cid) { order_create(values :[{ orderNo : 999, orderDateAsDate : "2021-10-25" }]) { affectedRows items { orderNo orderDate orderDateAsDate } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_create": { "affectedRows": 1, "items": [ { "orderNo": 999, "orderDate": 20211025, "orderDateAsDate": "2021-10-25" } ] } } } } ``` Example: computing aggregates `minimum` and `maximum`. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order_aggregate { minimum { orderDate orderDateAsDate changedTime changedTimeAsTime } maximum { orderDate orderDateAsDate changedTime changedTimeAsTime } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_aggregate": { "minimum": { "orderDate": 20130105, "orderDateAsDate": "2013-01-05", "changedTime": 901, "changedTimeAsTime": "09:01" }, "maximum": { "orderDate": 20151205, "orderDateAsDate": "2015-12-05", "changedTime": 1945, "changedTimeAsTime": "15:45" } } } } } ``` ## Datetime special fields Every Business NXT table contains the following fields: | Field | Type | Description | | ----- | ---- | ----------- | | `createdTimestamp` | datetime | The time point when the record was created (e.g. `2021-10-12 13:24:54.165`). | | `createdDate` | integer | The date when the record was created (e.g. `20211012`). | | `createdTime` | integer | The time when the record was created (e.g. `1324`). | | `createdUser` | integer | The ID of the user that created the record. | | `changedTimestamp` | datetime | The time point when the record was changed (e.g. `2021-10-12 09:02:13.843`). | | `changedDate` | integer | The date when the record was last changed (e.g. `20211023`). | | `changedTime` | integer | The time when the recod was last changed (e.g. `902`). | | `changedUser` | integer | The ID of the user that made the last change to the record. | > [!TIP] > > For precise values, prefer to use the `createdTimestamp` and `changedTimestamp` fields. These fields are available for all tables and have a higher precision than the date and time fields. > > If these fields are `null` then you need to use `createdDate`/`createdTime` and `changedDate`/`changedTime`, respectively. The pair `createdDate`/`createdTime` represents the point in time when the record was created. Similarly, the pair `changedDate`/`changedTime` represent the point in time when the record was last changed. As previously mentioned, some tables have another field called `changeTimeInMs` that includes seconds and miliseconds to the time. These are important in different contexts, such as fetching data created or changed after a particular moment in time. In order to simplify the use of these date-time values, the API makes these two pairs available through a compound field, as described in the following table: | Field | Type | Examples | | ----- | ---- | ----------- | | `createdDateTime` | datetime | `2021-10-21T13:20:00` | | `changedDateTime` | datetime | `2021-10-22T14:59:00` or `2021-10-22T14:59:22.456` (where `changeTimeInMs` is available) | These values do not indicate the timezone (as previously mentioned). They represent the local time of the server. A companion set of fields suffixed with `Utc` are available, which represent the same date-time values, but in the Coordinated Universal Time (UTC) timezone. | Field | Type | Examples | | ----- | ---- | ----------- | | `createdDateTimeUtc` | datetime | `2021-10-21T13:20:00Z` | | `changedDateTimeUtc` | datetime | `2021-10-22T14:59:00Z` or `2021-10-22T14:59:22.456Z` (where `changeTimeInMs` is available) | In the following example, the `changedDateTime` is used to select all the general ledger accounts that have been modified since `2021-10-01 17:00:00`. ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(filter : { changedDateTime : { _gte: "2021-10-01T17:00:00"} }) { totalCount items { accountNo name createdDateTime createdDateTimeUtc changedDateTime changedDateTimeUtc } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "generalLedgerAccount": { "totalCount": 2, "items": [ { "accountNo": 9001, "name": "Special ACC", "createdDateTime": "2021-10-21T13:20:00", "createdDateTimeUtc": "2021-10-21T13:20:00Z", "changedDateTime": "2021-10-21T13:21:00", "changedDateTimeUtc": "2021-10-21T13:21:00Z" }, { "accountNo": 9002, "name": "Extra ACC", "createdDateTime": "2021-10-21T13:20:00", "createdDateTimeUtc": "2021-10-21T13:20:00Z", "changedDateTime": "2021-10-21T13:21:00", "changedDateTimeUtc": "2021-10-21T13:21:00Z" } ] } } } } ``` The use of the `changedDateTime` or `createdDateTime` field is equivalent to the use of an expression built with the `changedDate`/`changedTime` or `changedDate`/`changeTimeInMs` fields (when available) or, respectively, the `createdDate`/`createdTime` fields. Alternatively, the `changedDateAsDate`/`changedTimeAsTime` and the `createdDateAsDate`/`createdTimeAsTime` fields can be used. Expression: ``` changedDateTime OP 2021-10-21T13:20:00 ``` Equivalent to: ``` (changedDate OP 20211021) OR (changedDate == 20211021 AND changedTime OP 1320) or (changedDateAsDate OP "2021-10-21") OR (changedDateAsDate == "2021-10-21" AND changedTimeAsTime OP "13:20") ``` Expression (where `changeTimeInMs` exists): ``` changedDateTime OP 2021-10-21T13:20:10.500 ``` Equivalent to: ``` (changedDate OP 20211021) OR (changedDate == 20211021 AND changeTimeInMs OP 48010500) or (changedDateAsDate OP "2021-10-21") OR (changedDateAsDate == "2021-10-21" AND changedTimeInMsAsTime OP "13:20:10.500") ``` Where `OP` is `_gt`, `_gte`, `_lt`, `lte`. An example for the filter expression `changedDateTime >= "2021-10-01T07:00:00"` is provided below: ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(filter : { changedDateTime : { _gte : "2021-10-01T07:00:00"}, }) { totalCount items { accountNo name createdDateTime changedDateTime } } } } ``` ```graphql { title = "Equivalent" } query read($cid : Int!) { useCompany(no: $cid) { generalLedgerAccount(filter : { _or : [ {changedDate : {_gt : 20211001}}, { _and : [ {changedDate : {_eq : 20211001}}, {changedTime : {_gte : 700}} ] } ] }) { totalCount items { accountNo name createdDateTime changedDateTime } } } } ``` To filter with a date time value that includes second and milliseconds the query and its equivalent are as follows (example for `changedDateTime >= "2023-05-16T07:17:28.659"`): ```graphql { title = "Query" } query read($cid : Int!) { useCompany(no: $cid) { order( filter : { changedDateTime : { _gte : "2023-05-16T07:17:28.659"} } ) { totalCount items { orderNo orderDate changedDateTime } } } } ``` ```graphql { title = "Equivalent" } query read($cid : Int!) { useCompany(no: $cid) { order(filter : { _or : [ {changedDate : {_gt : 20230516}}, { _and : [ {changedDate : {_eq : 20230516}}, {changeTimeInMs : {_gte : 26248659}} ] } ] }) { totalCount items { orderNo orderDate changedDateTime } } } } ``` The datetime format has the ISO8601 compliant form `yyyy-mm-ddTHH:mm:ss.ms`, such as `2021-10-01T07:00:00` and `2023-05-16T07:17:28.659` in the examples above. In the case of filter objects, the `changedDateTime` and `createdDateTime` fields have a companion field called `changedDateTimeTZ` and `createdDateTimeTZ`, respectively. The values for thse two fields is expected to be in a specific timezone. Therefore, it will be converted to the local time before being compared with date and time values in the database. ``` query read($cid : Int!) { useCompany(no: $cid) { order( filter : { changedDateTimeTZ : { _gte : "2023-05-16T09:17:28.659+02:00"} } ) { totalCount items { orderNo orderDate changedDateTimeUtc } } } } ``` The Business NXT time fields to not store seconds, only hours and minutes. For the tables that have the `changeTimeInMs` field available, seconds and milliseconds are also available. However, for the majority of tables, this field is not present. Regardless the case, when you use the `createdDateTime` and `changedDateTime` fields and specify a date-time value, you must also supply a value for seconds. Failure to do so, such as in the example `2021-10-01T07:00` will result in the following GraphQL error: ```json { title = "Equivalent" } { "errors": [ { "message": "Argument 'filter' has invalid value. In field 'changedDateTime': [In field '_gte': [Expected type 'DateTime', found \"2021-10-01T07:00\".]]", "locations": [ { "line": 5, "column": 26 } ], "extensions": { "code": "ARGUMENTS_OF_CORRECT_TYPE", "codes": [ "ARGUMENTS_OF_CORRECT_TYPE" ], "number": "5.6.1" } } ] } ``` /businessnxtapi/schema/bitflags page Bitflags allow managing preferences and statuses using bit flags in integer columns. GraphQL queries and mutations simplify interacting with these fields. 2025-04-15T09:48:42+02:00 # Bitflags Bitflags allow managing preferences and statuses using bit flags in integer columns. GraphQL queries and mutations simplify interacting with these fields. Some table columns store various preferences, statuses, or other kind of data that is defined using bit flags. These columns have the type `Int`. Working with them directly, however, requires good knowledge of the corresponding bitflags (both their value and their meaning). To ease this scenarios, all such columns have a corresponding columns with the same name but suffixed with `Flags`, which is defined as an array of an enumeration. An example of such a column is `OrderPreferences` from the `Order` table. It can store a combination of the following flags: | Name | Value | Description | | ---- | ----- | ----------- | | CreditNote | 2 | Credit note | | BatchInvoice | 4 | Batch invoice | | JustifyExchangeRates | 8 | Justify exchange rate | | ExemptFromInvoiceFee | 16 | Exempt from invoice fee | | GrossOrder | 256 | Gross order | | ReserveOnSave | 512 | Reserve on save | | AcceptChangesManually | 1024 | Accept changes manually | | ReservationWithinLeadTime | 2048 | Reserve within lead time | | ProduceCid | 4096 | Produce CID code | | PickWholeOrder | 8192 | Pick complete order | | InvoiceNoFromLabel | 16384 | Invoice no. from 'Label'. | | DeductFromClientBankAccount | 32768 | Withdrawal from client bank account | | PostToClientBankAccount | 65536 | Post to client bank account | | UseClientResponsibility2 | 131072 | Use client responsibility 2 | | FreeOfInterest | 262144 | Ref. Entry Free of interest | | Prepayment | 524288 | Prepayment | | FreeOfReminderFee | 1048576 | Ref. Entry Free of reminder fee | | DontCopyFromRemittanceSupplier | 2097152 | Do not copy from payment supplier | | UseClientResponsibility3 | 4194304 | Use client responsibility 3 | | ExcludeFromReduplication | 8388608 | Exclude from reduplication | | DontMoveConfirmedDeliveryDateEarlier | 16777216 | Do not move confirmed delivery date to earlier | | UseOriginalExchangeRateOnCreditNotes | 33554432 | Use original exchange rate on credit notes | In the GraphQL schema, the following two fields are available, for the `OrderPreferences` column:  The field `orderPreferences` is an integer, while the field `orderPreferencesFlags` is an array of the enumeration type `OrderPreferencesDomain`, shown below:  We can query these fields as follows: ```graphql { title = "Query" } query read_order_preferences($cid : Int!) { useCompany(no: $cid) { order { items { orderPreferences orderPreferencesFlags } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "items": [ { "orderPreferences": 16640, "orderPreferencesFlags": [ "GrossOrder", "InvoiceNoFromLabel" ] }, { "orderPreferences": 0, "orderPreferencesFlags": [] }, ... ] } } } } ``` The bitflags can be used when inserting new records or when updating an existing record. This is shown in the next examples: ```graphql { title = "Query" } mutation insert_order($cid: Int) { useCompany(no: $cid) { order_create(values: [ { orderPreferencesFlags : [ GrossOrder, PickWholeOrder, FreeOfReminderFee ] } ]) { affectedRows items { orderNo orderPreferences orderPreferencesFlags } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_create": { "affectedRows": 1, "items": [ { "orderNo": 4498, "orderPreferences": 1057024, "orderPreferencesFlags": [ "GrossOrder", "PickWholeOrder", "FreeOfReminderFee" ] } ] } } } } ``` ```graphql { title = "Update" } mutation update_order($cid: Int, $ono : Int!) { useCompany(no: $cid) { order_update( filter : {orderNo : {_eq : $ono}}, value: { orderPreferencesFlags : [ GrossOrder, InvoiceNoFromLabel ] }) { affectedRows items { orderNo orderPreferences orderPreferencesFlags } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_update": { "affectedRows": 1, "items": [ { "orderNo": 4498, "orderPreferences": 16640, "orderPreferencesFlags": [ "GrossOrder", "InvoiceNoFromLabel" ] } ] } } } } ``` To reset (erase) all the bitflags of a column, you can choose between: - Using the integer column and set the value to `0` (e.g. `orderPreferences : 0`). - Using the flags column and set the value to an empty array, i.e. `[]` (e.g. `orderPreferencesFlags : []`). This second option is exemplified next: ```graphql { title = "Update" } mutation update_order($cid: Int, $ono : Int!) { useCompany(no: $cid) { order_update( filter : {orderNo : {_eq : $ono}}, value: { orderPreferencesFlags : [] }) { affectedRows items { orderNo orderPreferences orderPreferencesFlags } } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order_update": { "affectedRows": 1, "items": [ { "orderNo": 4498, "orderPreferences": 0, "orderPreferencesFlags": [] } ] } } } } ``` The bitflag fields can also be used in filters, with one of the following operators: | Operator | Description | | -------- | ----------- | | `_is_on` | The specified flag is active (present among the enabled flags). | | `_is_off` | The specified flag is not active (not present among the enabled flags). | | `_eq` | The column value is set to exactly the specified flag (the specified flag is the only one that is active). | | `_not_eq` | The column valus is not set to exactly the specified flag. | An example is used in the following snippet: ```graphql { title = "Query" } query read_order($cid: Int) { useCompany(no: $cid) { order(filter : { orderPreferencesFlags : {_is_on : GrossOrder}}) { totalCount items { orderNo orderPreferences orderPreferencesFlags } } } ``` ```graphql { title = "Result" } { "data": { "useCompany": { "order": { "totalCount": 10, "items": [ { "orderNo": 132, "orderPreferences": 16640, "orderPreferencesFlags": [ "GrossOrder", "PostToClientBankAccount" ] }, ... ] } } } } ``` Flags can be provided as a variable as shown in the following examples: ```graphql { title = "Update" } mutation update_order($cid: Int, $ono : Int!, $opf : [OrderPreferencesDomain]) { useCompany(no: $cid) { order_update( filter : {orderNo : {_eq : $ono}}, value: { orderPreferencesFlags : $opf }) { affectedRows items { orderNo orderPreferences orderPreferencesFlags } } } } ``` ```graphql { title = "Variables" } { "cid" : 123456789, "ono" : 42, "opf": [ "GrossOrder", "PickWholeOrder", "FreeOfReminderFee" ] } ``` ```graphql { title = "Update" } mutation update_order($cid: Int, $ono : Int!, $ord : Order_Input!) { useCompany(no: $cid) { order_update( filter : {orderNo : {_eq : $ono}}, value: $ord) { affectedRows items { orderNo orderPreferences orderPreferencesFlags } } } } ``` ```graphql { title = "Variables" } { "cid" : 12345678, "ono" : 42, "ord": { "orderPreferencesFlags" : [ "GrossOrder", "PickWholeOrder", "FreeOfReminderFee" ] } } ``` /businessnxtapi/schema/enumdomains page Some columns, arguments, or result values are enumeration values. GraphQL defines fields suffixed with AsEnum to handle them using native enumeration values. 2025-04-15T09:48:42+02:00 # Enum fields Some columns, arguments, or result values are enumeration values. GraphQL defines fields suffixed with AsEnum to handle them using native enumeration values. Columns, as well as arguments and result values for processings and reports, have an associated domain for their value. This can be integer, decimal, string, or binary for example. However, there are cases when the domain is an enumeration type. Examples for this include the `orderType` for an order, or the `finishType` argument for the order finish processing. In GraphQL, for every such column, parameter, or result whose value is an enumeration type, there are two fields available: - a field with the same name as the column, parameter, or result, which returns the value as the underlying integral type (e.g. `orderType`, `finishType`) - a field with the same name as the column, parameter, or result, but suffixed with `AsEnum`, which returns the value as an enumeration type (e.g. `orderTypeAsEnum`, `finishTypeAsEnum`) The `AsEnum` fields enable you to define query without having to know what are the exact numerical values for a field and what they actually mean, since their meaning is defined by the name of enumeration values. For instance, the `orgUnit1Processing` to `orgUnit12Processing` fields have the following possible values: | Name | Value | | ---- | ----- | | Blocked | 0 | | InUse | 1 | | Stop | 2 | | Mandatory | 3 | These fields can be used as follows: ```graphql { title = "Query" } mutation create_gla($cid : Int!, $no : Int!, $name : String!) { useCompany(no: $cid) { generalLedgerAccount_create(values:[ { accountNo : $no, name: "demo", orgUnit1ProcessingAsEnum : Mandatory orgUnit2ProcessingAsEnum : InUse } ]) { affectedRows items { accountNo name shortName orgUnit1ProcessingAsEnum orgUnit2ProcessingAsEnum orgUnit3ProcessingAsEnum orgUnit1Processing orgUnit2Processing orgUnit3Processing editStatusAsEnum } } } } ``` ```json { title = "Result" } { "data": { "useCompany": { "generalLedgerAccount_create": { "affectedRows": 1, "items": [ { "accountNo": 12345, "name": "Demo", "shortName": "", "orgUnit1ProcessingAsEnum": "Mandatory", "orgUnit2ProcessingAsEnum": "InUse", "orgUnit3ProcessingAsEnum": "Blocked", "orgUnit1Processing": 3, "orgUnit2Processing": 1, "orgUnit3Processing": 0, "editStatusAsEnum": "Inserted" } ] } } } } ```