# Your first automations

## Automate data transfer and calculations <a href="#automate-data-transfer-and-calculations" id="automate-data-transfer-and-calculations"></a>

With the data model in place, you can add automations and logic to make the app behave like an invoice system instead of just a static database.

You will:

* Use logic fields to calculate line amounts and totals.
* Make sure invoices do not change afterward, even if products or customers do.

Why copy data instead of always showing linked data?

In many scenarios, you can simply show data from a linked table via a logic field and always see the latest version. For example, always show the latest product price.

For invoices, this is not what you want:

* In most countries, sent invoices must not be changed afterward.
* Product names, prices, or VAT rates may change over time.
* Invoice records must remain stable as of the time they were issued.

For this reason, you:

* Store customer and product data centrally in their tables.
* Copy relevant data (name, price, VAT, address) into invoice and invoice item records at creation time via automations.
* Use those copied values for calculations and printouts.

Let's take a look at the automations in the “Invoice Management” app.

{% hint style="info" %}
In automations, you need to refer to fields and tables by their internal names. For example, the field "Product descriptions" has the autogenerated internal name `product-descriptions`.
{% endhint %}

### Copy product data into invoice items <a href="#copy-product-data-into-invoice-items-trigger-on-relationship-field" id="copy-product-data-into-invoice-items-trigger-on-relationship-field"></a>

To ensure invoices stay unchanged even if product data changes later, copy the relevant product information into static fields on the "Invoice items" table.

When a user selects a product in the "Product" reference field, the following should happen:

* The product name is copied into "Product description" on the invoice item.
* The sales price is copied into "Unit price" on the invoice item.

Set this up as follows:

{% stepper %}
{% step %}
**Open "Invoice items"**

Open the "Invoice items" table from the app navigation.
{% endstep %}

{% step %}
**Open the field settings**

Click the gear icon in the top right to open the **Settings panel**.\
Select the **Fields** tab.\
Click the "Products" reference field to open **Field settings**.

<figure><img src="/files/oBufLEObKDtFbXDfnsM1" alt=""><figcaption></figcaption></figure>
{% endstep %}

{% step %}
**Open the "On update" automation**

Navigate to **Automations** and select **On update**.\
The logic editor opens.

<figure><img src="/files/K0AvsJ7HIkWxo4GauTMv" alt=""><figcaption></figcaption></figure>
{% endstep %}

{% step %}
**Add the script**

Add the following script, then click **Update** to confirm.

{% code overflow="wrap" %}

```
product_description := products.product_name; 
unit_price := products.sales_price;
```

{% endcode %}

The script line by line:

* `product_description` is the target field in the current "Invoice items" table.
* `:=` assigns a value to that field.
* `products.product_name` reads the field "Product name" from the linked "Products" record.
* `;` ends the line. Add it whenever more lines follow.
* The second line copies the sales price from the linked products record into the "Unit price" field of the current invoice item.
  {% endstep %}
  {% endstepper %}

### Convert a VAT choice to a numeric value <a href="#convert-a-vat-choice-to-a-numeric-value" id="convert-a-vat-choice-to-a-numeric-value"></a>

In the “Products” table, the field “VAT” is a single‑choice field that returns a text value such as “19 %”. For calculations, you need a numeric value, like “19”.

To solve this:

* Keep “VAT” as a single-choice in “Products” for a clear UI.
* Maintain a numeric field “VAT rate” in “Invoice items”.
* Use an automation on the “Product” field in “Invoice items” to:
  * Read the choice text from `products.'VAT'` (for example "19%").
  * Extract the first part:"19" and convert it to a number.
  * Store it in 'VAT rate'.

Set this up as follows:

{% stepper %}
{% step %}
**Open the field settings**

In the "Invoice items" table, open the settings panel and select the **Fields** tab.

Click the "Products" reference field to open the field settings.
{% endstep %}

{% step %}
**Open the "On update" logic**

Navigate to **Automations** and select **On update**.
{% endstep %}

{% step %}
**Add the script**

Add the following script, then click **Update** to confirm.

{% code overflow="wrap" %}

```
vat_rate := number(item(split(products.text(vat), "%"), 0))
```

{% endcode %}

What this does:

* First you specify the target field on the current record `vat-rate`.
* Then you use `:=` to assign a value.
* `products.text(vat-rate)` gets the text displayed by the VAT choice (for example, `"19 %"`).
* `split(..., " ")` splits that text at the percent character into parts (`["19", "%"]`).
* `item(..., 0)` selects the first part (`"19"`) while indexing starts at `0`.
* `number(...)` converts the text `"19"` into the numeric value `19`.
* That number is stored in "VAT rate" on the "Invoice items" table, every time you update the "Product" reference field.
  {% endstep %}
  {% endstepper %}

You now have a numeric VAT rate ready for calculations.

### Calculate line item totals on invoice items <a href="#calculate-line-item-totals-on-invoice-items" id="calculate-line-item-totals-on-invoice-items"></a>

In each invoice item, calculate the net amount and the VAT amount for that line item based on “Quantity” and “Unit price”:

{% stepper %}
{% step %}
**Open "Invoice items"**

Navigate to the "Invoice items" table.
{% endstep %}

{% step %}
**Create the "Net amount" logic field**

Click the **+** icon in the table header to add a new field. Enter the **Field name** "Net amount". Keep the autogenerated internal name. Select the field type **Logic**.
{% endstep %}

{% step %}
**Add the net amount logic**

Switch to the **Logic** tab. Add the following script, then click **Confirm**.

{% code overflow="wrap" %}

```
unit_price * quantity
```

{% endcode %}

This multiplies the unit price by the quantity in the same record.
{% endstep %}

{% step %}
**Create the "VAT amount" logic field**

Click **Add another field** and switch back to the **New field** tab. Enter the **Field name** "VAT amount". Keep the autogenerated internal name. Select the field type **Logic**.
{% endstep %}

{% step %}
**Add the VAT amount logic**

Switch to the **Logic** tab. Add the following script, then click **Confirm**.

{% code overflow="wrap" %}

```
round(quantity * unit_price * vat_rate / 100, 2)
```

{% endcode %}

This multiplies quantity, unit price, and VAT rate. It then divides by 100 and rounds to two decimal places. Rounding here helps avoid differences in invoice totals later.
{% endstep %}

{% step %}
**Save the field**

Click **Add field**.
{% endstep %}
{% endstepper %}

### Calculate invoice totals <a href="#calculate-invoice-totals" id="calculate-invoice-totals"></a>

On the “Invoices” table, you can now calculate the totals for each invoice by summing up all related invoice items.

For each invoice, you want to see:

* The net total of all line items
* The VAT total of all line items
* The gross total (net + VAT)

Set this up as follows:

{% stepper %}
{% step %}
**Open "Invoices"**

Navigate to the "Invoices" table.
{% endstep %}

{% step %}
**Create the "Net total" logic field**

Create a logic field, for example "Net total".
{% endstep %}

{% step %}
**Add the net total formula**

Set the formula to:

{% code overflow="wrap" %}

```
sum(invoice_items.net_amount)
```

{% endcode %}

This adds up the net amount of all related invoice items for the current invoice.
{% endstep %}

{% step %}
**Create the "VAT total" logic field**

Create another logic field named "VAT total".
{% endstep %}

{% step %}
**Add the VAT total formula**

Set the formula to:

{% code overflow="wrap" %}

```
sum(invoice_items.vat_amount)
```

{% endcode %}

This adds up the VAT amount of all related invoice items for the current invoice.
{% endstep %}

{% step %}
**Create the "Gross total" logic field**

Create one more logic field named "Gross total".
{% endstep %}

{% step %}
**Add the gross total formula**

Set the formula to:

{% code overflow="wrap" %}

```
net_total + vat_total
```

{% endcode %}

This sums the net total and the VAT total to get the final invoice amount.
{% endstep %}
{% endstepper %}

Here, the `sum()` function directly uses the “Invoice items” relationship. Ninox automatically accesses all related invoice items records for the current invoice, so you do not need explicit key fields or `select` statements.

### Calculate the expected payment date <a href="#calculate-the-expected-payment-date" id="calculate-the-expected-payment-date"></a>

In the “Invoices” table, you can calculate an expected payment date based on the creation date of the invoice, and the selected “Payment term”.

Because “Payment term” is a single-choice field, you need to handle all possible options. With many options, a `switch case` structure is easier to read than multiple nested `if ... then ... else` statements.

{% stepper %}
{% step %}
**Open "Invoices"**

Open the "Invoices" table.
{% endstep %}

{% step %}
**Open the field settings**

Click the **gear** icon to open the **Settings panel**.

Make sure you are on the **Fields** tab.
{% endstep %}

{% step %}
**Create the "Expected payment date" logic field**

Create a new logic field, for example "Expected payment date".
{% endstep %}

{% step %}
**Add the formula**

Set the formula to something like this:

{% code overflow="wrap" %}

```
switch text(payment_term) do 
    case "1 week": 
        creation_date + 7 
    case "2 weeks": 
        creation_date + 14 
    case "30 days": 
        creation_date + 30 
    case "90 days": 
        creation_date + 90 
    default: 
        creation_date + 30 
end
```

{% endcode %}

What this does:

* `switch text(payment_term) do` checks which option is selected in the “Payment term” field.
  * `text(payment_term)` gives the text value of the chosen option. Without `text()`, you would get the numeric ID of the selected option.
* Each `case` block defines how to calculate the expected date for one option.
  * For fixed time spans such as “1 week” or “30 days”, it adds that many days to “Creation date”.
* The `default` block defines what should happen if none of the cases match.
  * Here, it falls back to creation date plus 30 days.

You can adapt the case labels and numbers to exactly match your “Payment term” choices.
{% endstep %}
{% endstepper %}

### Generate unique invoice numbers <a href="#generate-unique-invoice-numbers-invoices-table" id="generate-unique-invoice-numbers-invoices-table"></a>

Every invoice needs a unique invoice number. You can generate it automatically when a new invoice is created, using the **On create** automation on the “Invoices” table.

In this example, invoice numbers follow the format:\
"INV-2026-0001" (prefix + year + running number with leading zeros).

{% stepper %}
{% step %}
**Open "Invoices"**

Open the "Invoices" table.
{% endstep %}

{% step %}
**Open the table settings**

Click the **gear** icon to open the **Settings panel**.

Select the tabs **Table** and **Settings**.
{% endstep %}

{% step %}
**Open the "On create" automation**

Under **Automations**, choose **On create**.
{% endstep %}

{% step %}
**Add the script**

Add the following script and click **Confirm**:

{% code overflow="wrap" %}

```
let myYear := year(today()); 
let myRN := max((select invoices where year(creation_date) = myYear).number(substr(invoice_number, 9))); 
invoice_number := "INV-" + myYear + "-" + format(myRN + 1, "0000"); 
creation_date := today()
```

{% endcode %}

What this does:

* `myYear` stores the current year.
* `myRN` finds the highest existing invoice number for this year.
  * It selects all invoices whose creation date is in `myYear`.
  * It extracts the numeric part of `invoice_number` starting at position `9`, after `"INV-YYYY-"`.
  * `number(...)` removes the leading zeros and transformes the string to a real number data type.
  * It then takes the maximum of these numbers.
* `invoice_number := ...` composes the new invoice number.
  * It adds the prefix `"INV-"`.
  * It adds the current year.
  * It adds a dash.
  * It adds the incremented running number, formatted to 4 digits with leading zeros using `"0000"`.
* `creation_date := today()` sets the creation date when the invoice is created.
  {% endstep %}
  {% endstepper %}

### Generate unique customer numbers (Customers table) <a href="#generate-unique-customer-numbers-customers-table" id="generate-unique-customer-numbers-customers-table"></a>

You can use a similar approach to create unique customer numbers in the "Customers" table, for example: `"CU-00001"`.

{% stepper %}
{% step %}
**Open "Customers"**

Open the "Customers" table.
{% endstep %}

{% step %}
**Open the table settings**

Click the **gear** icon to open the **Settings panel**.

Select the tabs **Table** and **Settings**.
{% endstep %}

{% step %}
**Open the "On create" automation**

Under **Automations**, choose **On create**.
{% endstep %}

{% step %}
**Add the script**

Add the following script and click **Confirm**:

{% code overflow="wrap" %}

```
let myLast := max((select customers).number(substr(customer_number, 3))); 
customer_number := "CU-" + format(myLast + 1, "00000")
```

{% endcode %}

What it does:

* `select customers` gets all existing customers.
* `substr(customer_number, 3)` takes the customer number without the prefix.
  * For example, from "CU-00001" it extracts "00001".
* `number(...)` converts that text to a number.
* `max(...)` returns the highest existing number.
* `myLast + 1` increments it for the new record.
* `format(..., "00000")` formats the number with five digits and leading zeros.
* The result is combined with the `"CU-"` prefix and written back to "Customer number".
  {% endstep %}
  {% endstepper %}

### Add example data to your app

If you want to see what your first app looks like with a bit more data, use the Ninox AI assistant to generate sample records for testing.

{% hint style="info" %}
This prompt assumes you used the same table and field names as in the [Your first app](/getting-started/builder-getting-started/set-up-your-ninox-manually/your-first-app.md) guide.
{% endhint %}

{% stepper %}
{% step %}
**Open a table in Builder mode**

Open any table in your app and switch to **Builder mode**.

In the settings panel, select the tab **Form** and **Add**. Scroll down to **Controls**.

<figure><img src="/files/CEHRLCZv4c0fZjh48TGk" alt=""><figcaption></figcaption></figure>
{% endstep %}

{% step %}
**Create a button field**

Drag a field of type **Button** into the form. Name it "Generate sample data".
{% endstep %}

{% step %}
**Open the button automation**

Open the button field settings and go to **On click**.
{% endstep %}

{% step %}
**Ask the AI assistant for the script**

In the AI chat paste the prompt below.

{% code title="Prompt" overflow="wrap" %}

```
Task: Create and run a script that inserts example data into four tables: customers, products, invoices, and invoice_items.

Customers:
- Insert 10 example records into the customers table.
- Generate customer_number values in the format CU-00001.
- Continue numbering from the highest existing customer_number.
- Determine the current maximum numeric part with:
  max(number(substr(customer_number, 3)))
- Start at max + 1 and create 10 consecutive unique numbers.

Products:
- Insert 10 example records into the products table.
- Generate product_number values in the format Pr-001.
- Continue numbering from the highest existing product_number.
- Extract the numeric part from product_number, determine the current maximum, then create 10 consecutive unique numbers with 3-digit zero-padding.

Invoices:
- Insert 10 example records into the invoices table.
- Generate invoice_number values in the format Inv-YYYY-00000, where YYYY is the current year.
- Continue numbering from the highest existing invoice_number in that format.
- Determine the current maximum numeric part with:
  max(number(substr(invoice_number, 10)))
- Start at max + 1 and create 10 consecutive unique numbers.
- Link each invoice to one existing customer through the customer relationship on the invoices table.

Invoice items:
- For each invoice, create at least 3 related records in the invoice_items table.
- Link each invoice item to a product from the products table.
- Do not reuse the same product more than once within the same invoice.

Avoid giving variables the same name as field names in these tables. It's best to always prefix variables with something that indicates they are variables, e.g., “my...” >> “myName”.
```

{% endcode %}
{% endstep %}

{% step %}
**Accept the generated script**

When the AI assistant inserts the script into the editor, click **Accept**. Then click **Confirm**.
{% endstep %}

{% step %}
**Run the button**

Exit **Builder mode** and click your new button.

If your app structure matches this guide, Ninox creates sample records in all four tables.
{% endstep %}
{% endstepper %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.ninox.com/getting-started/builder-getting-started/set-up-your-ninox-manually/your-first-automations.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.