Your first automations

Set up automations and logic fields to copy data, calculate totals, and generate invoice numbers.

Automate data transfer and calculations

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.

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.

Copy product data into invoice items

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:

1

Open "Invoice items"

Open the "Invoice items" table from the app navigation.

2

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.

3

Open the "On update" automation

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

4

Add the script

Add the following script, then click Update to confirm.

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

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.

Convert a VAT choice to a numeric value

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:

1

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.

2

Open the "On update" logic

Navigate to Automations and select On update.

3

Add the script

Add the following script, then click Update to confirm.

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

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.

You now have a numeric VAT rate ready for calculations.

Calculate line item totals on invoice items

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

1

Open "Invoice items"

Navigate to the "Invoice items" table.

2

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.

3

Add the net amount logic

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

unit_price * quantity

This multiplies the unit price by the quantity in the same record.

4

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.

5

Add the VAT amount logic

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

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

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.

6

Save the field

Click Add field.

Calculate invoice totals

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:

1

Open "Invoices"

Navigate to the "Invoices" table.

2

Create the "Net total" logic field

Create a logic field, for example "Net total".

3

Add the net total formula

Set the formula to:

sum(invoice_items.net_amount)

This adds up the net amount of all related invoice items for the current invoice.

4

Create the "VAT total" logic field

Create another logic field named "VAT total".

5

Add the VAT total formula

Set the formula to:

sum(invoice_items.vat_amount)

This adds up the VAT amount of all related invoice items for the current invoice.

6

Create the "Gross total" logic field

Create one more logic field named "Gross total".

7

Add the gross total formula

Set the formula to:

net_total + vat_total

This sums the net total and the VAT total to get the final invoice amount.

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

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.

1

Open "Invoices"

Open the "Invoices" table.

2

Open the field settings

Click the gear icon to open the Settings panel.

Make sure you are on the Fields tab.

3

Create the "Expected payment date" logic field

Create a new logic field, for example "Expected payment date".

4

Add the formula

Set the formula to something like this:

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

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.

Generate unique invoice numbers

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

1

Open "Invoices"

Open the "Invoices" table.

2

Open the table settings

Click the gear icon to open the Settings panel.

Select the tabs Table and Settings.

3

Open the "On create" automation

Under Automations, choose On create.

4

Add the script

Add the following script and click Confirm:

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

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.

Generate unique customer numbers (Customers table)

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

1

Open "Customers"

Open the "Customers" table.

2

Open the table settings

Click the gear icon to open the Settings panel.

Select the tabs Table and Settings.

3

Open the "On create" automation

Under Automations, choose On create.

4

Add the script

Add the following script and click Confirm:

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

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

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.

This prompt assumes you used the same table and field names as in the Your first app guide.

1

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.

2

Create a button field

Drag a field of type Button into the form. Name it "Generate sample data".

3

Open the button automation

Open the button field settings and go to On click.

4

Ask the AI assistant for the script

In the AI chat paste the prompt below.

Prompt

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

5

Accept the generated script

When the AI assistant inserts the script into the editor, click Accept. Then click Confirm.

6

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.