After we just created our first invoice, the champagne supplier informs us that they are increasing their prices. This means we also need to adjust our prices!

Instead of €7.50, a glass will now cost €9.00. 

Situation

Currently, our invoice is set up so that prices are always pulled directly from the Food & Drinks table.

If we change a price in the Food & Drinks table, all invoices will be updated – even those that have already been issued! 

We can set this up for the future using a trigger so that the invoice always displays the price that was current at the time of invoicing.

This price will then be permanently "anchored" in the respective invoice.

Solution

In the subtable Invoice Items, we set a Trigger after modification in the link field Food & Drinks. This trigger ensures that the current price is stored in a newly added  number field Unit Price (instead of the previous fx formula field Unit Price).

The trigger transfers the current price into this field, where it is stored as a fixed number. It is no longer pulled dynamically from the Food & Drinks table into a

fx formula field Unit Price.

How Ninox interprets this

1. We add a new invoice item to an invoice (+ Create Record) and select an item in the link field Food & Drinks.
2. The Trigger after modification reacts to this change in the Food & Drinks field (Change = event) and executes its script. In this case, the trigger assigns the current unit price from the Food & Drinks table to the number field Unit Price in the Invoice Items table.

How it works – Part 1

1. Go to the subtable Invoice Items.
2. In the Table Settings, add a number field Unit Price. Don't forget to adjust the number format in the Field Settings!
3. Delete the previous fx Formula Field Unit Price.
4. Open More Options in the field settings of the link field Food & Drinks.
5. Under Trigger after modification, insert the following script: Unit Price := 'Food & Drinks'.Unit Price
6. Now adjust the Total Price: Since the unit price was previously a formula field, it will now be calculated using the new number field Unit Price.

• 7... and save!

Watch the process

How it works – Part 2

Now we need to adjust the Invoices table.

1. Open edit mode in the Invoices table.
2. Click in the Form View on the header of the subtable Invoice Items.
3. Click on Show Column and restore the new Unit Price. If you don't see it immediately, it may be hidden on the right edge. Drag the fields over the header to the left and place the field before the Total Price using drag-and-drop.
4. Save changes and you're done!

Watch the process

OK. Not quite done yet. The only invoice we have at the moment no longer has a calculation basis and currently does not show unit or total prices.

Let’s quickly fix that!