0

Button that creates a new record for the next month

There is a button that creates a new record in the table for the next day

let t := this;
let oldData := last(Table1 order by Date);
let newRec := duplicate(oldData);
Table1.(Promotion := null);
Table1.(Comment := null);
newRec.(Date := Date + 1)

how to change the formula so that a record is created not for the next day, but for the beginning of the next month, for example, when you press the button, a record is created for the first day of the next month?

16 replies

null
    • szormpas
    • 1 yr ago
    • Reported - view

     Hi,

    Can you try the following?

    let x := if month(Date) = 12 then
    date(year(Date) + 1, 1, 1)
else
    date(year(Date), month(Date) + 1, 1)
end;
newRec.(Date := x);
      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

       Hello
      gives an error message

       

      at the beginning of the picture there is an old formula, it worked, but did not give the first day of the month. How to fix it so that the next entry starts with the first day of the month?

      Thank you )

      • Fred
      • 1 yr ago
      • Reported - view

      You need to tell Ninox where the Date field is coming from. If it is not in the table the button is on then you need to tell it where to get it from.

      • szormpas
      • 1 yr ago
      • Reported - view

        can you replace the "newRec.(Date := Date + 28);" in your script with the following?

      let x := if month(oldData.Date) = 12 then
            date(year(oldData.Date) + 1, 1, 1)
         else
            date(year(oldData.Date), month(oldData.Date) + 1, 1)
         end;
newRec.(Date := x);
      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

       Good morrning

      The formula works and creates a new record on the first day of the next month. But in my formula, it should copy the some field values,  and delete the old value to 'Promotion' 'Fine/punishment' and 'comment'. After clicking the button, a new day is created, and the field values 'Promotion' 'Fine/punishment' and 'comment' in all previous days are deleted

      Now the formula looks like this

      let t := this;
      let oldData := last(Table1 order by Date);
      let newRec := duplicate(oldData);
      Table1.(Promotion := null);
      Table1.('fine/punishment' := null);
      Table1.(Comment := null);
      let x := if month(oldData.Date) = 12 then
              date(year(oldData.Date) + 1, 1, 1)
          else
              date(year(oldData.Date), month(oldData.Date) + 1, 1)
          end;
      newRec.(Date := x)

      Test.ninox
    • John_Halls
    • 1 yr ago
    • Reported - view

    You are using this

    Table1.(Promotion := null);
Table1.('fine/punishment' := null);
Table1.(Comment := null);

    and, surely it should be this

    newRec.(Promotion := null);
newRec.('fine/punishment' := null);
newRec.(Comment := null);

    Regards John

      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

       Thank you, it works correctly

      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

       I want to ask one more thing)

      Then in order to place data only for one month in the table, I had to set two time fields 'beg date' and 'end date'.

      let t := this;
      let oldData := last('Monthly Report' order by 'BEG DATE');
      let newRec := duplicate(oldData);
      newRec.('BEG DATE' := null);
      newRec.('END DATE' := null);
      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)

      what needs to be added to the formula so that the field 'End date' displays the last day of the same month?

      • John_Halls
      • 1 yr ago
      • Reported - view

       This will give you the last day of the same month

      date(year('End date'), month('End date')+1, 0)

      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

       

      I have explored all possible places where it can be placed, but it creates after the month of Juli December) and the field 'end date' is empti

      If it's not too much trouble, please write the full formula

      • John_Halls
      • 1 yr ago
      • Reported - view

       

      newRec.('END DATE' := date(year('BEG DATE'), month('BEG DATE')+1, 0))

      This needs to be after you assign 'BEG DATE', so the whole thing becomes, after tidying up.

      let oldData := last('Monthly Report' order by 'BEG DATE');
let newRec := duplicate(oldData);
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))

      Regards John

      • iliper LTD
      • iliper_LTD
      • 1 yr ago
      • Reported - view

       Excellent. Thank you very much.

    • John_Halls
    • 1 yr ago
    • Reported - view

     Did you know this will work, even for December?

    date(year(oldData.'BEG DATE'), month(oldData.'BEG DATE') + 1, 1)

    Date() will roll up the year by 1 and set the month to 1 automatically. Similarly setting the day to 0 gives the last day of the month of the previous month.

    Regards John

      • Fred
      • 1 yr ago
      • Reported - view

      I did know that. It is very handy of Ninox to do the math for us. Now you can just add 12 to your months to add years as well.

    • John_Halls
    • 1 yr ago
    • Reported - view

    My full code, above probably won't work as I mean it to. The whole code can simplified to

    let oldData := last('Monthly Report' order by 'BEG DATE');
let newRec := duplicate(oldData);
newRec.('BEG DATE' := date(year(oldData.'BEG DATE'), month(oldData.'BEG DATE') + 1, 1));
newRec.('END DATE' := date(year(newRec.'BEG DATE'), month(newRec.'BEG DATE')+1, 0))
      • szormpas
      • 1 yr ago
      • Reported - view

       thanks,

      I didn't know that, and it's a very handy feature of the date() function!

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 16Replies
  • 99Views
  • 4 Following