10 Replies Latest reply on Jul 29, 2013 10:11 AM by HimilconInciarte

    Creating database for a classroom library



      Creating database for a classroom library


           Hi everyone,

           I'm trying to create a databse that will track the "checking in" and "checking out" of books at my classroom library. Right now, I have three tables: Books, Students, and Rentals. I have a field, Books::Status, that states whether or not a book is "available" or "out." I have a button in Rentals (after a book, student, and date have been selected) that, when pressed, sets the Status value to "out."

           I want to create another button so that after entering a check in date, the Status becomes "available" again. However, I can't seem to be able to do after hours of trying. 

           I'm confident I'm missing something essential/basic here, but thank you so much for your help! 

        • 1. Re: Creating database for a classroom library

               Shouldn't be to hard. Should just be the same as what you have done with the other button.

               Use a "Set Field" script step to set the Status field to a value of "Available"

               Just make sure that button is on the correct record. For instance when using a list view.

               Can you give us any more hints on where exactly your efforts are going wrong?

          • 2. Re: Creating database for a classroom library

                 I think that's exactly the problem: the button works when I check out the book without creating a new record. but if anything happens between check out and check in (like checking out another book), the book doesn't check out. I'm not sure what you mean when you say using the list view... Thank you so much!


                 Edited to add: The problem is that the button loses track of the book, so to speak, when I navigate away from the Rental record (where books are checked in and out). The button works just fine if I don't navigate away from the Rental record and press "check in" right after "check out." But, realistically, that's now how the database will be used. How do I get around this? I basically want it to "remember" which record to check in, regardless of what happens in between check out and check in. 

            • 3. Re: Creating database for a classroom library

                   I still don't know a lot about your database. But I'm assuming you have your buttons in your header.

                   Maybe over a list view of books.

                   And when another book is selected then the one you want to 'Check In' the wrong book is checked in (or if that book is already checked in is appeares that nothing happens.)

                   So you have to design your database so that this CAN'T happen.

                   Either you create a dropdown where you first select the book you want to check in and then hit the "check in" button.

                   Or you put the check in button on the book record itself, either in a list view or a form view.

                   In a list view that means that that button will be repeated for every book. But that way, when you click it you will atleast be checking in the correct book.

                   Again, I don't know what's going wrong with your file because I still know nothing of your database.

                   Is this a form view layout or a list view?
                   Where is the button? On the layout or in the header?
                   What table is this layout based on?
                   What does the button do?


              • 4. Re: Creating database for a classroom library

                     The Rental Layout has a value list where a user selects the book (showing only available books) that is being checked out, to whom, and the date it was checked out. At that point, the user clicks on "check out," and that book is labeled "out" (using a SetField script). This button works fine because I'm pressing it right then and there--there is no need for me to navigate away from that record. 

                     Below the "check out" date is another field where they input the date a book is checked in. And next to that field is another button that says "check in." That button is supposed to mark that book as "Available" (using a SetField script), but it only works if I do not navigate away from that Rental record, which is a problem, because I need to navigate away from that record in order to do other things (like check out another book, or run a student analysis, etc) since a book will be checked out for many days at a time.

                     The Layout is based on the Rental Table, which is linked to the Books Table. I've attached a screenshot of the Rental Layout. I'd be happy to upload the files themselves, but I don't see an option for that...

                • 5. Re: Creating database for a classroom library

                       Hi, there shouldn't be any difficulty here. If you are on the rental record for that book a simple set field step should put whatever you want into any field in the rental table. So I don't know exactly where your problem is, maybe you made a mistake somewhere that I can't see.

                       If you want to upload your file you first need to upload it somewhere and then post the URL. You could also (if you use dropbox) put it in your public folder and then give us the ublic address. That way we can download your file.

                       So here is how I understand your file:

                       You probably have about 3 tables.
                       - Books
                       - Students
                       - Rental

                       Books contains all information about books. Like the book name etc. There should be an ID field. That's a number field set to auto enter a serial number.
                       This table probably also contains the "status" field that shows whether a book is available or out. (And I'm am assuming that your problem lies here.)

                       Students just the same, all info about the student plus an ID field.

                       Rental then contains all your rental information and has the following fields:
                       - ID  -  A number field set to auto enter a serial number
                       - BookIdFk  -  A number field where we will store the ID of the book (FK stands for foreign key and that's what we call an id we are storing in another table in order to make a relationship)
                       - StudentIdFk - Again a number field to store the ID of the student who's loaning the book.
                       - CheckedOutOn  -  Date field
                       - CheckedInOn  -  Date Field
                       - Duration  -  Can be a calculation that goes: CheckedInOn - CheckedOutOn
                       - PagesRead  -  A number field
                       - PagesPerDay  -  A Number field

                       Your relationships should be between the ID in the Books table and the BookIdFk in the Rental table.

                       And the same for the ID in the Student table and the StudentIdFk in the Rental table.

                       Then you create any kind of layout, be it list or form view based on your rental table and you have your check in and check out buttons.
                       Now when setting the checked out field there is no problem, because that field is in the rental table.
                       But when setting the status field on the book table you seem to be having difficulty.

                       This shouldn't be a problem if you created your relationships correctly. Because from this record in the rental table you can only see one book in the book table. And that's the book you currently have selected; So from this record you can set the Status field on the book table without any problem.

                       Then the check in button can set the checked in date on the rental table but can also set the status field on the books table back to available.

                       Because of the relationship between books and rental only one book is visible from this context.

                       You could also put check in and check out buttons on your books layout.
                       That check out button would 
                       - remember the book ID (set variable)
                       - Set the status of the book to "out"
                       - Go to the rental record and
                       - create a new rental record
                       - enter the correct Book ID (from the variable we created)
                       - and set the check out date

                       Then you just have to enter the student name and voila.

                       All done.

                       If you have any trouble please let us know.

                  • 6. Re: Creating database for a classroom library

                         Thank you so much for such a detailed answer! I did manage to fix the problem I was having with the book check out, but I have a question about something you said about the relationships between tables:

                         Right now, the foreign key that stores the name of the book being lent in Rentals is linked not to the BookID field in Books, but to the BookTitle in Books. I know this is a problem, but I don't know why. Part of the reason why I didn't link the BookIDpk to BookIDfk is because I don't know what the values on BookIDfk should be. I know to set the pk to be automatically generated upon creation. But what about fk? Do I just leave it blank, or...? 

                         Again, thanks so much for your help! 

                    • 7. Re: Creating database for a classroom library

                           No problem.

                           I understand your confusion because filemaker doesn't make it easy to use a dropdown to choose a book but enter the ID in the BookIdFk field.

                           There is a little trick you can use that I have explained in this video:


                           The bad thing about using a book's title as a relationship is this:

                           You have to understand that in order for a relationship to be valid there needs to be one field in each table where the value is the same.

                           Normally speaking that would be the ID field.

                           In your Books table you will have several books, each of them will have it's own unique ID. There are no two books with the same ID:

                           This ID is a number field set up to auto enter a serial number so that every time you create a new book it get's the next ID number.

                           For example:

                           ID  Title
                           1   Filemaker For Dummies
                           2   Cooking 101
                           3   Brian surgery made easy

                           Now imagine you rent out a book. You will make a record in the rental table where you enter:

                           - ID: The ID of this rental record. If this is the first time you rent something it will be "1"
                           - BookIdFk : This is where you specify the book we are talking about. In my example we would enter "3" in this field if we want to check out the book "Brian surgery made easy".
                           In your example you would use the title of the book. So you would enter the title probably using a dropdown. So your value in this field would be: "Brian surgery made easy"

                           If you have been paying attention you will now see that this value "Brian surgery made easy" exists in two places, once in the Books table as a title and once as a foreign key in the rental table in the BookIDFk field.

                           First of all that is silly because you are duplicating a title while you could just use the number "3"; This takes up less space in terms of computer memory but where it really starts going wrong is here:

                           At some point someone says something, or you notice that you made a typo. It's not "Brian surgery" but of course "Brain surgery"

                           You, quickly whithout anyone noticing (because you are embarrassed about your typo) change the title in the books table and you believe you have been saved.

                           But the problem now is that you broke your relationship. Because you rented out a book called "Brian surgery made easy" But that book no longer exists.

                           If you would have used my system you would have rented out book "3" and no matter how often you change the title of that book, or how many typos you made, that number will never change. And that relationship will never break.

                           Book "3" will always be "3" no matter if it's Brian or Brain.

                           So, questions: How do I enter the ID of a book while never needing to deal with ID's, because I don't know the ID's, I only know the titles?

                           Well, watch my video and use that dropdown trick so you only see the book titles but the ID still gets entered in the BookIdFk field.

                           Another question: How do I change all these book titles that I have entered now into ID's?

                           Very simple. Keep your relationship the way it is now, with the titles related and use the "Replace Field Contents" function to replace the title in the Rental::BookIdFk field.

                           If you want you can create an extra field, yust to be sure. A number field in stead of a text field. Then you aren't overwriting any data in case something goes wrong. And use the Replace field contents function to enter the Book::ID into that field.

                           Because the Book records are stll related by title the ID if the correct book will be entered.

                           After that change the relationship so that the Book::ID is related to the Rentals::BookIdFk

                           Make sure they are both number fields with numbers entered in them.

                           If you have any more question, please let me know!


                      • 8. Re: Creating database for a classroom library

                             When you check out a book you will need to set the correct Book ID in the BookIdFk field. How you do this depends on the table you are starting from.

                             If you start from the rentals table you should use a dropdown as explained in my video.

                             If you create a layout based on the Books table and you just browse trough the books you can have a check out button there that performs this script:

                             Note that you need to take the Book ID with you to the rental layout, so we'll need to remember it for a little while until we arrive on the rentals layout. To remember a value in Filemaker we use the script step "Set Variable". A variable is a value that has a name, so you can set multiple variables and take them with you to another layout.

                             (while you are on the books layout)
                        - Set Variable [ $BookID ; Books::ID ]
                             - Go To Layout [ Rental ]
                             - Create new record / request
                        - Set Field [ Rental::BookIdFk ; $BookId ]
                             - Set Field [ Rental::CheckOutDate ; Get( CurrentDate ) ]

                             This will:

                             - Set a variable with the name "$BookId" this name is important because you will use it later in your "Set Field" step.
                             The value of this variable will ofcourse be the ID of the book and that can be found in the ID field of the Book table.
                             - You will go to the rental layout
                             - And create a new rental record there
                             - Then you will set the variable we created (named $BookId ) in the BookIdFk field in the Rental table.
                             - Then we'll set the Check out date as the "Current Date" so you don't have to enter that manually and voila. All you need to do is enter the student name (or as you should have learned by now the Student Id in the StudentIdFk field!!)

                             And you should be done.

                             I hope this makes sense.

                        • 9. Re: Creating database for a classroom library

                               And I forgot another option:

                               You could also create a portal on the Books layout. This portal would be based on the rentals table and if that's the case, when you create a new record in this portal the BookIdFk field will be filled in automatically by filemaker because you are entering data in a related field. The relationship needs to be valid so that's done automatically.

                               This means you could be on the layout of books, on the record of the book: "Filemaker 101" and you have a portal based on 'Rentals' with fields:
                               - Student
                               - Checked Out
                               - Checked In
                               - ...

                               The moment you create a record there the correct BookIdFk will be entered, so no need to worry about that.

                               You could set the "Checked out" field to Auto enter the creation date and then you don't have to enter that either.

                               And you could create a button to check the book back in, but you need to make sure you set that button within the portal row inside the rental portal.

                               That button can then set the checked in date and can change the Books status.

                          • 10. Re: Creating database for a classroom library

                                 This is absolutely AWESOME. Thank you so much!! I made the changes to the foreign keys in Rentals, and things seem to be working fine so far. I'm sure I'll be back with another question about something else.


                                 Thanks again!!