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
-
Can you post the code you are working with? Or post a sample DB.
-
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)?
-
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.
-
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 tableLine 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?
-
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.
-
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.
-
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.
Content aside
- 1 yr agoLast active
- 13Replies
- 118Views
-
2
Following