Automatic record creation
I have two tables - Table1 and Table2. Table2 is a subtable of Table1. Every time Field1 (a formula field that shows a number from Table3) in Table1 is higher than 0, I want a record to be created in Table 2 with a table reference to the record in Table1.
However, if a record on Table2 connected to a record on Table1 already exists, a new record should not be created.
Is there a way to achieve this? I hope the explanation was somewhat clear. Many thanks in advance.
All the best,
Try this in a button first:
let curRec := this; <--gathers data from the current record in Table1
if Field1 > 0 then <--checks to see that Field1 is greater than 0
let xT2 := cnt(select Table2 where Table1RefField = curRec); <--replace Table1RefField with the exact name of your reference field to Table1, then it counts the number of records in Table2 that are linked to the same Table1 record
if xT2 < 1 then <--if that number is greater than 1 then it creates as new record in Table2 and links it to the record you are on in Table1
let xCreate := (create Table2);
xCreate.(Table1RefField := curRec);
alert("New Record created")
alert("Record already created")
If that works like you want it to, then you can copy and paste the code into the Trigger after Update section of Field1. If you are using the cloud version of Ninox then you can strip the alerts as they won't show. That is a feature not a bug. :) If you are using the MacOS desktop then the alert will show. This is because trigger alerts happen on the server and the cloud version the server just ignores them. The MacOS version your computer is the server so they show.
Hope that helps.
Since Table2 is a subtable of Table1, there is no need to use a Select statement to find the records of Table2 linked to the current record of Table1. You can simply peruse the existing relation. If the reference fields have kept their default names, which are the names of the tables themselves, you can simply write:
if cnt(Table2) != 0 then …
Nice. Will have to remember that.
Hello Alain and Fred,
Many thanks for your help, I tried the formula in a button first as suggested and it works smoothly.
However Field1 is a formula field gathering data from a field in Table3. Being a formula field, I can't connect a trigger to it (or at least I don't think there's a way to do so).
The other issue is that due to the nature of the data we gather in our Ninox, Field1 takes its value from the latest added record in Table3 (another subtable of Table1). Due to this structure, I'm not sure where exactly I could plug the code.
Let me explain a bit better:
- Table 1 - Master table where each record corresponds to a Project.
- Table 2 (a subtable of Table1) - Used to record procurement contracts only if the value of Field1 is >0 (otherwise a procurement contract is not needed)
- Table 3 (a subtable of Table1) - Used to record historical changes to the contract with the donor and the project budget (the value corresponding to Field1 changes often, and Field1 only shows the most recent value we should take in consideration).
A row should be created in Table 2 only based on the latest value of Field1, which unfortunately is a formula and can be connected to a trigger. Do any solutions come to mind?
Thanks again, your help is very appreciated.
You can place the trigger in Table 3 as this is the table being updated. Use the Trigger on update and place this code in it
Sorry, hit 'Post' before I meant to. The code will look like this
if Table1.Formula > 0 and cnt(Table1.Table2) = 0 then
let a := Table1;
let b := (create Table2);
b.(Table1 := a)
Many thanks John, this seems to work!
Have a great evening,