0

database wide variables

I'm a teacher, and a lot of the formulas/views/filters are based off the current school year.  Multiple times I reference either a students grade, the current school year, the current trimester etc. across multiple tables in my database.  

I've now set myself up to have to make a whole bunch of changes to my database for next school year in order for everything to work correctly.  I was hoping someone knew of a way that I could create a variable that was available database wide so at the beginning of the new school year, all I would have to do is change the dates or whatever else in one place and it would update across all my tables.

For example - for the 2021 - 2022 school year I would have:

if 'Graduation' = 2022 then Grade := "5th"

if 'Graduation' = 2023 then Grade := "4th"

YearBeg := date(2021, 9, 1)

YearEnd := date(2022, 6, 30)

Tri2End := date(2022, 3, 14)

but for the 2022 - 2023 school year those variables would read:

if 'Graduation' = 2023 then Grade := "5th"

if 'Graduation' = 2024 then Grade := "4th"

YearBeg := date(2022, 9, 1)

YearEnd := date(2023, 6, 30)

Tri2End := date(2023, 3, 17)

 

I hope this makes sense, thank you for all your ideas!!

6 replies

null
    • Ninox partner
    • RoSoft_Steven.1
    • 2 yrs ago
    • Reported - view

    you can use the "global script definitions", located in the Options - TAB when you are in ADMIN-mode. these are database wide accessible.

      • dbernstein1_commackk12
      • 2 yrs ago
      • Reported - view

      RoSoft_Steven Rooryck I've never used those before - does it use different formatting than a formula would?

      • Ninox partner
      • RoSoft_Steven.1
      • 2 yrs ago
      • Reported - view

      Dariel There are videos on youtube by Nioxus explaining Global functions and variables : https://www.youtube.com/watch?v=psPLrRX2_AA&t=749s

      Hope this helps.

    • Lars
    • 2 yrs ago
    • Reported - view

    Hi Dariel, why don't you calculate your values?

    I don't know how you store the school year, but let's assume, it's a string like "2021 - 2022". So to get the interesting part, you could use 

    let schoolyear1 := number(substr(schoolyear, 0, 4));
let schoolyear2 := number(substr(schoolyear, 7, 4));
Grade := string(5 - Graduation - schoolyear2);
switch Grade do
    case 1: Grade := Grade + "st"
    case 2: Grade := Grade + "nd"
    case 3: Grade := Grade + "rd"
    default: Grade := Grade + "th"
end

    For the years you could use stuff like 

    YearBeg := date(schoolyear1, 9, 1);
YearEnd := date(schoolyear2, 6, 30);

    And for the Tri2End you do something similar. You can check the weekday of (I suppose) middle May and then calculate the date for Friday (or whatever your end trimester end should be).

    • Fred
    • 2 yrs ago
    • Reported - view

    Another option would be to use dashboards to access your data. Please take a look at the attached sample DB. It is only setup like your example of two grades. If you need to do a whole elementary school then that is another issue.

    You can select the school year and it will bring up the students whose graduation year is equal to or +1 of the end year of the school year.

    Using dashboards is a way of looking into your raw data without having to constantly filter or change views of your raw data. It is something that I use a lot. I have a sports league DB so I have raw data tables that I never touch directly. I get a view of my data through dashboards.

    School Year.ninox
      • dbernstein1_commackk12
      • 2 yrs ago
      • Reported - view

      Fred Thank you for the idea, I'll have to look at it when I get home as I'm on a Windows PC at work.  The information is used mostly in formulas to calculate based on date range or in views to filter the information for dashboards I currently have set up.

Login to reply

Content aside

Categories

View all topics
  • 2 yrs agoLast active
  • 6Replies
  • 107Views
  • 4 Following