script to find if student enrolled last year

Sean_Wood
1 yr ago
13replies

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?

- 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

script to find if student enrolled last year

13 replies

Content aside

Overview