0

script to find if student enrolled last year

Hi, I'm not finding the logic to write a script that would check if in a given school year (Sept-Aug) the Parent table , a student currently enrolled - the child table, was enrolled the previous year . Student details are in a linked other table.

Finding duplicates works for the current year but not retrospectively and not as soon as a new year is started.

Basically I need to be able to show which students are new and which are returning each year.

Any ideas?

13 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    Can you post the code you are working with? Or post a sample DB. 

      • Sean_Wood
      • 1 yr ago
      • Reported - view

      Fred This is my set up:

      - School year table (2021-22 / 2022-23 etc) I have a code for each made up of the last 2 digits of the year the 1st term starts so 2022-2023 would be S22. This a formula.

      - Student enrollment table (child of school year) with a link to:

      - Student details table

      So If I look at a student from the student details table , I can see all the enrollments they had.

      What I need is to find a formula that checks if a student was enrolled the previous year (or not).

      I have made a formula field with:

      if 'Student details'.'Nbr. enrolledyears' > 1 then    "Previous"else    "New"end

      this is fine for the last year (or current year) but obviously isn't accurate for years before as it only counts if there are more than one enrollments for a student.  So I can't set up a print view which could list new and previous student numbers , grouped into classes or levels.
      Hope this helps

    • Fred
    • 1 yr ago
    • Reported - view
    AlexisO said:
    So I can't set up a print view which could list new and previous student numbers , grouped into classes or levels.

    How do you want to select the year(s) you want to print?

    Do you already have a dashboard where you can select the year(s)?

      • Sean_Wood
      • 1 yr ago
      • Reported - view

      Fred Thanks for looking into this.

      I am inside a school year and call up a view of all the enrollments, sorted by level.

      (sorry this is all in french)
      So on screen the above checking and counting of number of enrollments, means I can list them as Previous student or New depending if I get a true or false. Here are screen grabs of the view, grouped by level , then with one level unrolled. And finally the print view I get using same formula for the previous / new (Anciens/Nouveau) columns. Only I either get just the true or false (Oui, Non) - see Anciens column or the total of entries, not divided by previous and new - see Nouveau column. Hope this helps

    • Fred
    • 1 yr ago
    • Reported - view

    You can try this in a formula field in your enrolled tabled:

    let t := this;
    let allYears := ((select SchoolYear) order by code);
    let curIndex := index(allYears, t.SchoolYear);
    let prevYear := item(allYears, curIndex - 1);
    let enrollData := allYears[= prevYear].Enrollment.Student;
    if enrollData != "" then
        contains(enrollData, t.Student)
    end
    

    Line 1: gets the record data for the record in enrollment

    Line 2: gets all records from the SchoolYear table (change to match yours) then orders it by code (change to match yours). I do this just in case the records are not in chronological order or if the record Id are not sequential.

    Line 3: using the index() command I find where the school year of the record that I'm on is in the sorted school year records. Change the SchoolYear after "t" to match your link.

    Line 4: using the item() command, I can now find the recordId of the previous school year using the Index found in line 3.

    Line 5: I now gather all the students from that previous year. Change the Enrollment and Student links to match yours.

    Lines 6-8: is just there for the very first year as there will be no records to compare so we don't want a No. Now we can use the contains() command to see if the current student is in the array from line 5. Change the Student after "t" to match your link.

      • Sean_Wood
      • 1 yr ago
      • Reported - view

      Fred oh wow, very clear for me to follow. Thanks. I'm going to try this out and get back to you.

    • Sean_Wood
    • 1 yr ago
    • Reported - view

    Ok, so I completely follow your reasoning in the script but have come across error messages from Ninox. Must be how I reference tables and fields.
     

    let t := this;
    let allYears := ((select 'SAISON-INSCRIPTION') order by 'No.Saison');
    let curIndex := index(allYears, t.Saison);
    let prevYear := item(allYears, curIndex - 1);
    let enrollData := allYears[= prevYear].ID;
    if enrollData != "" then
        contains(enrollData, t.ID)
    end
    

    SAISON-INSCRIPTION = SchoolYear table
    Saison = the child-parent link in the enrollment table with the SchoolYear table

    Line 1 OK
    Line 2 OK
    Line 3 : function not defined, index([nid],nid) column 42
    Line 4: non valid operator: void  - number , column 44
    Line 5 OK
    Line 6 OK
    Line 7 OK
    Line 8 : function not defined, index([nid],nid)

    As you can see I am not a script expert.
    I'm putting all this in a formula field, might that be my error?
     

    • Fred
    • 1 yr ago
    • Reported - view

    Are you using the cloud version? or the app version (MacOS or iPad)?

    If the app version what version are you on?

    Just to verify that this formula is in the enrollment table (a child of Saison) that is a many to many link between Saison and Student.

    Once line 3 gets settled then line 4 & 7 will work.

    let t := this;
    let allYears := ((select 'SAISON-INSCRIPTION') order by 'No.Saison');
    let curIndex := index(allYears, t.Saison);
    let prevYear := item(allYears, curIndex - 1);
    let enrollData := allYears[= prevYear].ID;
    if enrollData != "" then
        contains(enrollData, t.ID)
    end
    
    

    Line 5: I think you need to get the list of students from the school year not the school year. Thus my code goes down the links to get a list of all records of students for that Saison. You are asking to get the recordId of the record in Saisons that matches the recordId of the previous school year, and it will only be one.

    Line 7: Once line 5 is a list of students then you need to point to the student link in enrollment, see my code. Since you changed line 5 to only get the Saison record it will not match correctly since you are asking to compare it to the recordId of the current enrollment record.

    • Sean_Wood
    • 1 yr ago
    • Reported - view
    Fred said:
    If the app version what version are you on?
    Just to verify that this formula is in the enrollment table (a child of Saison) that is a many to many link between Saison and Student.

     Using the Mac app version 3.7.14

    Yes it's in the enrollment table , child of Saison, that is a many to many link between saison and Student.

      • Fred
      • 1 yr ago
      • Reported - view

      AlexisO The code i’m using needs version 3.8.x to work. Are you planning on updating or do you need me to rework the code?

    • Sean_Wood
    • 1 yr ago
    • Reported - view

    Ah, well the answer can be both as several computers need to share this info in the office and I'm not sure all of them can have the same app version. Let me start by updating my own version.

      • Fred
      • 1 yr ago
      • Reported - view

      AlexisO If you have multiple users then you probably use the cloud version, so you can just access it through a browser and try it out without having to update your app.

      • Sean_Wood
      • 1 yr ago
      • Reported - view

      Fred YEEESSSS it works ! Thank you so much for your time and effort in helping. This is just what I needed.  Hope this might help someone else with a similar set up.

      I even checked with a student that was flagged true with a duplicate search but had actually only re-applied with a year away in between and your solution was therefore more acurate and year-specific.

Content aside

  • 1 yr agoLast active
  • 13Replies
  • 118Views
  • 2 Following