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
orRemove
;- If its
Add
this means, we put something in the Positions (Box) - If its
Remove
this means we take something from the Positions (Box)
- If its
- reference one entry in
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:
- “i put position 2, 4, 5 in the position/box x”
- “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":
cur.extend(change.Position)
else:
# Remove them
for elem in change.Position:
try:
cur.remove(elem)
except:
pass
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…