Save Daily Summary in new table

I would like to save these totals daily to another table so I can look at the backlog by location (the amount left to invoice) over time and see changes, trends, etc.

I believe the other forum post said to create another table and create a field for the total for each location but I can't figure out how to do create that field or link the tables together.   The record id is the key for the table that I am trying to summarize and I assume that table would be the child which I think makes this impossible.

Once the summary field is created I believe I would need to create an automation to post the summary field each day to another table to create a backlog history table.

I hope that makes sense.  If not, please let me know what questions you have.  Thanks in advance,

Ben