Sum up `Adds` and `Removes` from an ongoing change list to its current values

I have two tables.

  • Entries

    • contains metadata of a thing
    • Has a Label: Vial_Label
  • Changes

    • reference one entry in Entries. (Vial_Label)
    • has a choice list: Positions that goes from “1”-“80” (Positions are literally positions in a box)
    • a direction, Add or Remove;
      • If its Add this means, we put something in the Positions (Box)
      • If its Remove this means we take something from the Positions (Box)

The reason i do it like this is, that the rows in Changes get locked over time (and backed up etc),
so that we get a tamperproof log of changes.

Users constantly add rows to Changes.
Where they say for example:

  1. “i put position 2, 4, 5 in the position/box x”
  2. “i took position 2, 5 from the position/box x”

What i want now is a good way to combine all the changes into the current value of Positions. (The things that are currently in the Box)

So the result of the above should be:

“4”. (Since they put 2,4,5 but just took 2,5.)

I did it like this in a Summary table over the Changes by Vial_Label

cur = []
curchanges = Changes.lookupRecords(Vial_Label=$Vial_Label, sort_by="Date_Added")
for change in curchanges:
  if change.Add_or_Remove == "Add":
    # Remove them
    for elem in change.Position:
return cur

So the code above does:

Look up all the changes for one Vial_Label, sort it by insertion date, then iterate over all changes,
adding or deleting positions to calculate the end result: “4”.
This works but i think will blew up quite quickly when a lot of changes or entries are in the database.

Do you have any recommendation how i could do it better?

I could also upload the document with some samples.

PS: It was quite hard to come up with a good question title…