History table logic

[AFEI]

I have a book borrowing app,

I have a table with the list of the books, and other fields such as UserID, Borrowing Date, Returning Date, and Status.
These last 4 fields’ records are updated through the app. And when a book is returned, I delete/change these records manually.
I also have a table with User information, such as name, email, phone number…

I want to create a table History where I can store a list of the books a user has already borrowed as well as the borrowing date for each.
Ideally, the history table would update automatically when a book is borrowed from the app and the table with all of the books is automatically updated. Also, it would keep the info even if the info in the book table is deleted because the book has been returned.

I am struggling to make it work on airtable, if any of you could give me a lead on what way to take to make it work, it would be awesome.

Thank you.

PS: Some screenshots of the different tables

book list table:

User table:

History table:

[TheTimeSavingCo]

Hi @AFEI, with the way your base is set up now, I would do the following:

1. Create a new checkbox field in the Book List table called “Returned”
2. Create an automation where, when the checkbox field is ticked, will create a new record in the History table with all the data that I want to keep, and also clear the checkbox field and whatever other fields you need to clear once a book is returned

---

If you’re open to changing the way your base is setup, I think I would recommend that you have three tables:

1. Book List
2. Users
3. Borrows

Then, when a book gets borrowed, a new record gets created in the Borrows table that’s linked to the borrowed book as well as the user that borrowed it

The Borrowing Date and the Returning Due Date would be in the Borrows table

This way you don’t need to worry about deleting data and creating history etc, the data will exist in the places you want it right from the beginning

If you’d like someone to just solve this problem for you, you can also hire me to do it!

[AFEI]

Hello @Adam_TheTimeSavingCo thanks for the advice, it worked perfectly :slightly_smiling_face:

[greekat]

Hi this is a great post with a great example.  I'm trying to use this strategy with a consultant-style business.  So instead of Borrows it would be Job Assignments.  So I have three tables:

Jobs, Guards, Job Assignments

The assignment table has Assigned Date and Unassigned Date and the Guards are placed on job in a multi-select field.  And are removed the same way.  How do I capture the Unassigned Date when a guard is removed from a job?  

 Right now I have an automation that fills in the two dates based on of Record Created Date:

 I can't think of how to capture the unassigned date so that's it different from the assigned date.

Thank you for all your help.

kat