0

Help with Creating First Record in Empty Table via Button in Ninox

Hello Community,

I'm currently working with Ninox and trying to set up a button that will create the first record in a table. My table, named "New Months Report", is initially empty, and I need the button to handle this situation by creating the first record with default values when the table is empty.

Here’s the logic I’m trying to implement:

  1. Check if the table is empty — If the table is empty, I want to create a new record.
  2. Create the first record — If the table is empty, the first record should be created with the current date as the 'Beg Date' and 'End Date' as 30 days after today.

Here’s the formula I have so far:

let oldData := select 'New Months Report';
if count(oldData) = 0 then
    let newRec := create('New Months Report');
    newRec.'Beg Date' := today();  // Set Beg Date to today's date
    newRec.'End Date' := today() + 30;  // Set End Date to 30 days after today
else
    // If there are records, duplicate the last record
    let lastRecord := last(oldData order by 'Beg Date');
    let newRec := duplicate(lastRecord);
    newRec.'Beg Date' := today();
    newRec.'End Date' := today() + 30;
end;

 

However, I'm encountering an issue where, when the table is empty, the formula doesn’t seem to be creating a new record (does not see the table 'New Months Report'). Could anyone point out if there’s a mistake in the formula or if there's a different approach for handling this?

Any guidance would be greatly appreciated!

Thank you!

16 replies

null
    • Fred
    • 2 wk ago
    • Reported - view

    This works for me:

    let oldData := (select 'New Months Report');
    if count(oldData) = 0 then
        let newRec := (create 'New Months Report');
        newRec.('Beg Date' := today());
        newRec.('End Date' := today() + 30)
    else
        let lastRecord := last(oldData order by 'Beg Date');
        let newRec := duplicate(lastRecord);
        newRec.('Beg Date' := today());
        newRec.('End Date' := today() + 30)
    end
    

    When I pasted your code, it gave an error on line 3. You had the parentheses in the wrong place.

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       it gives me an error on the first line that the table "New Months Report"is not found. but it is enough for me to create the first record, without copying old records, specify the date in the field 

      newRec.('Beg Date' := today());

      • Fred
      • 2 wk ago
      • Reported - view

      try removing the select and retyping it.

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       it is necessary to clarify that a table New Monts report is a subtable in a table EMPLOYEES. and there is no record in it when we create a new employee, but old employees have records there

      the error disappeared there but it appeared on line 3, 4  and 5, in the third table not found

      • Fred
      • 2 wk ago
      • Reported - view

      Sorry, I didn't mean to remove the select. Just retype the whole thing.

       said:
      it is necessary to clarify that a table New Monts report is a subtable in a table EMPLOYEES. and there is no record in it when we create a new employee, but old employees have records there

      Can you post a sample DB? I don't know where this code is starting from as that will determine how to link Employee to New Months Reports. I don't know how you create a new employee record. There are lots of unknowns.

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       
      it is necessary that the button creates the first entry in the table. The button that will then make the next one if necessary is there, and it works well, the problem is in the first entry. Basically, all entries in this table should be made by the button, so view will be displayed, to remove the "Create record"

      • Fred
      • 2 wk ago
      • Reported - view

      If you only have 1 table then there is never going to be an empty table. You have to have 1 record to be able to see the button which means there is a record. So there is no need to check for empty as you will never have an empty table to have a button to click on.

      An options is to create a dashboard/page to put your button on.

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       No matter how many records there are in the table, when creating a new employee, his table will be empty, that's the problem for me. When there is at least one record for an employee, I have a button that creates the next record, transferring the information I need that we rarely change

      let t := this;
      let oldData := last('New Months Report' order by 'Beg Date');
      let newRec := duplicate(oldData);
      delete newRec.'LIST OF PROMOTION';
      delete newRec.ADVANCE;
      let x := if month(oldData.'Beg Date') = 12 then
              date(year(oldData.'Beg Date') + 1, 1, 1)
          else
              date(year(oldData.'Beg Date'), month(oldData.'Beg Date') + 1, 1)
          end;
      newRec.('Beg Date' := x);
      newRec.('End date' := date(year('Beg Date'), month('Beg Date') + 1, 0))

      • Fred
      • 2 wk ago
      • Reported - view

      I'm confused about what you want done. Can we start over?

      Please describe the tables/fields involved and the process you want to happen. Or upload a sample DB with the proper tables.

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       Good morning

      In the table  new month report there are many fields, I need a button that will create a first record in the table new month report , indicating only the date of creation in the field Beg Date

      Table new month report field Beg Date is configured so that through triggers field End Date receives the end of that month.
      When we create a formula that can do this, we will add a function that if the table already has a record, then do

      let t := this;
      let oldData := last('New Months Report' order by 'Beg Date');
      let newRec := duplicate(oldData);
      delete newRec.'LIST OF PROMOTION';
      delete newRec.ADVANCE;
      let x := if month(oldData.'Beg Date') = 12 then
              date(year(oldData.'Beg Date') + 1, 1, 1)
          else
              date(year(oldData.'Beg Date'), month(oldData.'Beg Date') + 1, 1)
          end;
      newRec.('Beg Date' := x);
      newRec.('End date' := date(year('Beg Date'), month('Beg Date') + 1, 0))
      

      This allows you to move some data from the previous month.

      • Fred
      • 2 wk ago
      • Reported - view
       said:
      I need a button that will create a first record in the table new month report

      If this button is in the New Months Report table then the button can not create the first record. If you have no records you can't see the button.

      But how does the employee table fit in?

      Does the employee table have it own create button?

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       there is a table EMPLOYEES in which the button is located and there is also an internal table New Months Report. DB which I loaded, everything as I described now. And the button which is outside the table New Months report should create the first record in the table by defining the command for the field Beg Date date today()

    • Fred
    • 2 wk ago
    • Reported - view
     said:
    table New Monts report is a subtable in a table EMPLOYEES

    So the reference field is called New Months Report, but that is not the actual table name. Can you post a screenshot of the Edit Field screen so we can see the relationship? This will show you the actual table name.

      • iliper LTD
      • iliper_LTD
      • 2 wk ago
      • Reported - view

       Hello

       

      • Fred
      • 2 wk ago
      • Reported - view

      Both the select() and create() commands looks at the table name not the reference field name so it should read:

      let oldData := (select 'New Monts Report')
      
      let newRec := (create 'New Monts Report')

      So if Ninox can't find the table name then just go back and look at your table names and put in the correct one.

      • Fred
      • 2 wk ago
      • Reported - view

      But in this case you don't need the select as New Months Reports is a child table of Employees, so you can just use the reference field name.

      let oldData := 'New Months Report'
      

      Which is faster than the select().

Content aside

  • 2 wk agoLast active
  • 16Replies
  • 79Views
  • 2 Following