0

Auto create record on first day of the new year

I have a table ( Invoice Summary) which summarises the monthly and quarterly totals from my invoice table.

Each record in this table has a number field where I enter the year I want to summarise and a bunch of formula fields which carry out the various calculations.

Whilst it's easy to create a new record and fill in the year, I'm wondering if there is a way to auto-create a new record and fill in the year the first time I open Ninox in the new year (calendar or financial)?

I have a few other tables where this would be useful (such as my Tax table).

One way I'm thinking that this could work would be to add the formula to "trigger on new record" part of my invoice table that first checks if a record exists for the current year and if not creates this record.

Not quite what I want but would be a work-around. 

Either way I'm not quite sure how to script this so would be grateful for any help

 

Cheers

Michael

7 replies

null
    • John_Halls
    • 2 yrs ago
    • Reported - view

    Hi Michael

     

    Michael said:
    One way I'm thinking that this could work would be to add the formula to "trigger on new record" part of my invoice table that first checks if a record exists for the current year and if not creates this record.

     This is the way to do it, but put your code in 'Trigger after open' (found in Options). This runs when the app is first opened. 

    If count(select 'Invoice Summary' where Year = year(today())) = 0 then
    create('Invoice Summary').(Year := year(today()))
end

    Regards John

    • Michael_Chung
    • 2 yrs ago
    • Reported - view

    Thanks John. That looks perfect. I assume I could also put this in the "on open" global script for the database.

      • Michael_Chung
      • 2 yrs ago
      • Reported - view

      John Halls  Just reread you post and you already said this.

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Michael No worries!

    • Paul_Chappell
    • 2 yrs ago
    • Reported - view

    Why use a Summary table at all? Having 12 monthly and 4 quarterly formula fields in a single Summary record for each year could cause a speed issue presuming you have quite a lot of invoices per year and many years worth moving forward. That’s a lot of calculations on a lot of data. Why not just create Views of the original Invoices table and group by year, quarter, or month as needed. 

      • Michael_Chung
      • 2 yrs ago
      • Reported - view

      Paul Chappell Thanks for the feedback.

      In my case, I don't think that will be a problem. It's a low volume, high-value business (less than 100 invoices per year).

      That being said, I am always open to better ways of doing things and who knows in the future. 

      If I let you know what I am trying to achieve, maybe you can point me in the right direction.

      I have created a table called "Dashboard which has a single field called "Year". I have created a single record in this table. 

      I have set up various tabs with different elements on each one. The first element is a table view which is linked to my Invoices table using the select where commands. This filters the records based on the year I enter in year field. This works perfectly.

      What I haven't been able to do is create a bar chart showing the total value of each month for the year. The best I can do is show the total for each invoice over a year (1 bar for each invoice instead of 1 bar for each month)

      I can sort of achieve this using the Summary table although it's not presenting with the month names along the x-axis.

      Any help would be much appreciated. 

      • Paul_Chappell
      • 2 yrs ago
      • Reported - view

      Michael Have you tried creating a formula field for just the month of the invoice?

      month(InvoiceDate) will return 1 to 12. 

      Then group the chart by this field.  This only works if you have only selected one year obviously.  A formula field I often use is to pick out just the year and month:

      year(InvoiceDate) + "/" + format(month(InvoiceDate), "00")

      which can then return multiple years/months, and will display in proper numeric order because of the 00 formatting.  Otherwise, numeric ordering is wrong.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 7Replies
  • 111Views
  • 3 Following