0

Automatic record creation

Good morning,

 

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,

Giovanni

7 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    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")
    else
    alert("Record already created")
    end

    end

     

    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.

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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 …

    • Fred
    • 2 yrs ago
    • Reported - view

    Nice. Will have to remember that.

    • Giovanni_Zagarella
    • 2 yrs ago
    • Reported - view

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

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Giovanni

     

    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

    • John_Halls
    • 2 yrs ago
    • Reported - view

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

     

    Regards John

    • Giovanni_Zagarella
    • 2 yrs ago
    • Reported - view

    Many thanks John, this seems to work!

     

    Have a great evening,

    Giovanni

Login to reply

Content aside

  • 2 yrs agoLast active
  • 7Replies
  • 620Views