using formula to update fields or delete records

Hi Dariel

Add a number field called high to your sub-table. Add a button with this code

for a in Student do
a.(high := 0)
end;
for b in unique((select Student).Student) do
let c := last((select Student where Student = b) order by Score);
c.(high := 1)
end

Lastly add a view to your Parent Table with this code

select Student where high = 1

Regards John

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:

max('Typing Scores'.Score)

Then, in the "Student Information" table, create a tabular view sorted in descending order by "Best Score".

Dariel

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

Mel

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.

Regards John

Dariel

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 !