History table logic

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:
image

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!

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

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.