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 <tablename>_create of a type having the name of the form <parenttype>_<tablename>_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:

associate_create(values: [Associate_Input!]!, 
                 insertAtRow: FilterExpression_Order, 
                 insertPosition: InsertPosition): Mutation_UseCompany_Associate_Result

The <tablename>_create field has several arguments:

one mandatory called values which is a non-nullable array of objects of type <tablename>_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_<tablename>_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 <tablename>_Insert_Input.

The following image shows a snippet of the Associate_Input type from the GraphiQL document explorer:

Associate_Input

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.

AssociateInformation_Insert_Input

The mutation result type (<parenttype>_<tablename>_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:

Mutation_UseCompany_Associate_Result

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:

Associate

An insert operation has the following form:

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
      }
    }
  }
}

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:

Query


mutation create_order($cid : Int!)
{
   useCompany(no : $cid)
   {
      order_create(
         values:[
           {
             customerNo : 10000
             dueDate: 20221124
           }
         ]
      )
      {
        affectedRows
        items
        {
          orderNo
          dueDate
        }
      }
   }
}

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:

Query


mutation create_order($cid : Int!)
{
   useCompany(no : $cid)
   {
      order_create(
         values:[
           {
             dueDate: 20221124
             customerNo : 10000
           }
         ]
      )
      {
        affectedRows
        items
        {
          orderNo
          dueDate
        }
      }
   }
}

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.

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
         }
      }
   }
}

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:

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
      }
    }
  }
}

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.

SuggestIntervalType

Note

A limited number of fields in the data model support specifying an internal.

Here is an example for suggesting values:

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
         }
      }
   }
}

Result


{
  "data": {
    "useCompany": {
      "voucher_create": {
        "affectedRows": 1,
        "items": [
          {
            "batchNo": 2,
            "voucherNo": 60003,
            "voucherDate": 20220715,
            "valueDate": 20220715
          }
        ]
      }
    }
  }
}

Here is an example with suggesting an interval:

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
         }
      }
   }
}

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:

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
      }
   }
}

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:

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 <tablename>_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.

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
      }
    }
  }
}

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).

Last modified September 24, 2024