using formula to update fields or delete records
I have searched and searched these forums because I really like to try to figure these things out on my own, but I cannot find a solution for what I'm looking to do!
Parent Table: Student Information
Sub-Table: Typing Scores
I teach computers, and one use for my database is to find the fastest 3 or 4 typers in each grade level.
The problem comes when I will inevitably have multiple typing scores (records) for each student. For example:
Record 1: Student A - 15 WPM
Record 2: Student B - 11 WPM
Record 3: Student C - 10 WPM
Record 4: Student A - 13 WPM
Technically, the top two fastest typers are both Student A. I want to just see the ONE fastest score for every student, their "High Score" and that way they're not taking up multiple spots on my scoreboard.
When I did the same thing in Access, after inputting my information I had a query set called High Scores that would check for each student whether their "New Score" was higher than their "High Score" and if it was, it would update that information in a separate "High Score" table and that was the table I pulled my scoreboard from.
My goal is for a button called "Update High Scores" to do something similar for me. I'm stuck between two options, neither of which I can get to work:
1) Create a new record in separate High Scores table and "on create" look to see if there is an existing record with the same Student ID and then compare the WPM field. If the new record (which I have been trying to find as a last(select 'High Scores'.'Student ID')) WPM field is greater than the old (first(select 'High Scores'.'Student ID')) then delete the old record.
2) Before creating the new record, check if the new WPM is greater than the old, and if it is then UPDATE the pertinent fields in the existing record.
I hope this makes sense, it's been driving me crazy for weeks! Thank you!
Add a number field called high to your sub-table. Add a button with this code
for a in Student do
a.(high := 0)
for b in unique((select Student).Student) do
let c := last((select Student where Student = b) order by Score);
c.(high := 1)
Lastly add a view to your Parent Table with this code
select Student where high = 1
Another way to solve the problem: create a formula field in the "Student Information" table, name it, for example, "Best Score", and set the formula to:
Then, in the "Student Information" table, create a tabular view sorted in descending order by "Best Score".
Thanks so much, John!
This definitely works, however we're only a few weeks into the school year and already running that formula is taking a significant amount of time. I've already split it so that it's only running through records per grade and therefore not having to pull 3x the amount of records.
Any thoughts on a way to speed it up? Maybe comparing the record where 'high' = 1 and the most recent?
if you are using nixox cloud version you can speed up the processing by adding
a first line in the forumula do a server so that all search processing is done at ninox's server side first
I have one table with 600k records and it make a big dfference
As well as Mel's suggestion (which will speed things up significantly change the first line to
for a in Student where high = 1 do
These are all fixes but the main problem is that I don't think your database is not normalised correctly. Think about creating a students table and adding a 1:N table of scores to that. A formula field in the student table can keep track of their best score.
Just done a quick test on database of 7000 records in one table
Test - I wanted to list and sort all the jobs over the different customers and do a count of how many Jobs were ordered by each customer and then go onto set a flag in each job.
all from a script on a button and a formula field to show the job count. (on ordinary wireless fibre broadband)
from click - to do and set the flag watching the screen update each record on the table view as script ran - it went took approx 35-40 seconds
the same test but this but wih do as server added - from click - an approx 1 second pause and then all records updated within 1-2 seconds
Quite an improvement !
That worked perfectly, thank you!
John - Can you help me understand what's not normalized about the database? I have a table called Student Information and a 1:N related table called Typing Scores. Is there a better way to do that? Thank you! I love learning fom here!