Say for example I had 100,000 employees who all make widgets every day. I have a record for each employee's productivity on each day. If Bob has worked 10,000 days, I have 10,000 records for Bob. Field A is the number of widgets and Field B is the date. I want to add a field to each record that calculates the earliest date where the employee made as many or more than the number of widgets he made on that day. If Bob made 200 widgets on 1/1/2020, and the first time time he ever made at least 200 widgets in a day was 5/5/2018, then I want a field that shows 5/5/2018 in his record for 1/1/2020.

I know I can write a looping script that searches for Bob records with >=200 and sorts it by date and then sets a variable for the date in the first record, goes back to the original record, and sets the new field with the variable. I'm dealing with tens of millions of records though and it could take weeks for a script to fill all that in. This is what I've been doing and it's taking nearly a whole second to process each record.

Of course, I assume there's ways to do this with related tables, possibly a self join. I haven't figured it out yet.

2 hours ago, databaser said:

I want to add a field to each record that calculates the earliest date where the employee made as many or more than the number of widgets he made on that day.

The simplest method to do this would be to construct a self-join relationship of the (let's call it) Productivity table as:

Productivity::EmployeeID = Productivity 2::EmployeeID 
AND
Productivity::Date > Productivity 2::Date 
AND
Productivity::Quantity ≤ Productivity 2::Quantity 

and show the date from the first related record in Productivity 2.

However, with "tens of millions of records" this would probably be quite unusable, unless you were looking at one record at a time (and maybe even then). A more practical method would lookup the date, using the same relationship. And you would need to spend the time required to populate the existing records using relookup.