library-type check-in/check-out system

[TanyaD]

Hello,

Does anyone have any suggestions for resources regarding connecting two databases (patrons and books) so that when an item is checked out and the patron's barcode is scanned, the patron's information is automatically connected to the item and its barcode?

We currently have two separate databases that are not connected, so when we check-out an item, we have the barcode of the patron scanned into the item's record, but the patron's record never shows a history of past or present items checked out. Therefore, we can't search a patron and find out which items s/he has or has had.

Also, the item record only shows it's current status, is it possible to have a history or archive of patrons who have checked the item out?

Thank you in advance for any suggestions or assistance.

Tanya

[Ender]

It sounds like you currently attach a Patron to a Book by putting a PatronID (barcode?) into a field in Book.

In order to keep a history of checkouts, you'll need a join table in between Patron and Book. Let's call the join table "Checkout". The Checkout table would have these fields: PatronID, BookID, Checkout Date, Checkin Date, Status, etc. Patron would be related to Checkout via PatronID, and Book would be related to Checkout by BookID.

With this structure, you could not only see the history of a specific Patron's checkouts, you could see a history of all the Patrons that have checked out a specific Book.

[TanyaD]

Thank you very much for your help.

I must apologize, I am a beginner and I really don't know my way around FM Pro or the jargon.

I created a new table in the books database named Checkout with fields corresponding to both the Books and Patrons Databases.

I created relationships between these corresponding fields, but the information does not seem to be connected.

Should Checkout just be a table in the larger Books Database or should checkout be an entirely different database?

By a "joining table," do you mean a table with relationships between both the Books and Patrons Databases or is it something different entirely?

Again, thank you so much for your assistance.

[Ender]

In FM7, it doesn't matter if each table is in its own file, or they are combined in one or more files. You can always refer to external tables by adding file references to those external files. Once those file references are created, you can add table occurrences to tables in those files.

The join table Checkout joins Book and Patron through a relationship to each. See the attached table occurence graph.

checkout.pdf

[TanyaD]

I apologize for asking so many questions, but this should be it.

I have my tables set up in a similar manner to the attachment you sent (thank you), except that the relationship symbol has the "chicken foot" looking graphic attached in both directions between Book and Checkout and Patron and Checkout. Your example only has it attached from Book to Checkout, meaning the "chicken foot" is only attached to Checkout and same with Patron. All the examples I've found also look like your attachment, so I'm not quite sure why mine are different.

With that said, as I test the relationships by checking items out, there doesn't seem to be an accumulation of records, making it quite obvious that I'm definitely doing something wrong.

Is it possible someone has setup the databases making these relationships difficult to create?

Again, thank you for your help and patience.

[Ender]

If you make your ID fields auto-entered serial numbers, or validated to be unique, the chicken feet will change to a single line in the relationship graph. Validating to be unique, however, can cause some delays in record creation as the number of records gets into the hundreds of thousands.

I'm don't quite follow your description of what's not working. Are you using a portal of Checkouts in a layout based on Patron or Book?

[TanyaD]

I was assuming that once these relationships were setup between both Books and Patrons to Checkout, that I would begin to see the history of items checked in and out. I was thinking that when I checked an item out through Books that a new record would be formed of that transaction in Checkout. As time went on, these records would continue to accumulate and I would be able to track item usage and patron check out history. Am I thinking about this in the wrong way?

I don't think I setup Checkouts as a portal. I currently access the Checkout table by choosing it from the layout drop drown menu in Books, is this correct?

[Ender]

Use portals to enter and view Checkouts. To add new Checkouts through the portal, you'll need to set the option of the Checkouts side of the relationship to 'Allow creation of records in this table via this relationship '.

[Fenton]

I don't see Ender online, so I'll chime in. You need to create a new record in the Checkout table for each instance of a checkout, with both the Book's ID and the Patron's ID. There is more than one way to do this.

You would probably want portals in both Books and Patrons, to see, in the 1st case, the history of the book, and in the 2nd case the history of what books a patron has out.

At first you'd think that the portal in Patrons would have "allow creation of related records." But this quickly becomes useless when a patron has lots of checkouts, because you need access to the last portal row to enter the book ID (with the barcode scanner). Also, you really want the portal sorted descending by date & time (or TimeStamp), so you see the most recent checkouts at top.

So you need a button to create checkout records, in Patrons I imagine. Because the same patron may checkout more than 1 book at a time, so it makes more sense to do it from there. So you first find the Patron, either typing the name, or scanning their library card barcode into their ID while in Find mode.

Then click a button to run a script to create a new book checkout. It creates the new checkout record (row in the portal), by going to the Checkout table's layout and creating a New Record. The Patron ID could be passed as the button's Script Parameter (option at the bottom of the script attachment dialog). It is retrieved with Get ( ScriptParameter) function and set into Checkouts' Patron ID.

The Checkouts' record auto-enters the time & date (or TimeStamp), returns to the Patron layout, Commits the record, which sorts and refreshes the portal so the new record is at the top, then Go to Field puts the cursor in the Book ID field (of the Checkout table; that's who's in the portal).

Now all you have to do is scan the Book ID and you've got one checkout. If a person is checking out several books at once, it's a little clunky to have to hit the button for each book, so you may want to do the above inside a Loop, with a pause while in the Book ID in the portal. There are 2 ways to do the Loop, either using Find mode (which continues with either a Return or Enter) or with a global (which requires an Enter key).

I prefer the global method, but it requires that your barcode scanner be able to enter a "post-amble" Enter key. Their default is usually a Return key, which only works to continue a Find pause. The Enter key will continue either a Find pause or a regular Pause/Resume Script step in Browse mode, so it's more flexible. But both work fine. Let us know if you want to know more about that. But right now I've got to finish my taxes :-[

[TanyaD]

Thank you very much for such a detailed explanation.

I have attached an image ("Checkout Button Spec.pdf") of what I've created for the

Checkout_Button_Speci#97F70.pdf

[Fenton]

Just for fun I built a little example file, after my last post. I used a process which I didn't mention, but which I think works well for this. I kind of forgot about it; it's from the last solution when I helped someone with this process.

It bypasses the problem with the global field and default "post-amble" Return key of the bar code scanner, by using a FileMaker dialog box, with an global input field. A dialog also continues with either Return or Enter, like Find mode.

I'm not using Find mode. I prefer using a global field and relationships (self-relationships). It makes it easier to check things and move along.

I like the dialog box because it pops up over and over, giving a good visual clue about what to do next, which is good if you're going to use a Loop. The Loop is so you can check out several books in a row to the same patron. That's what the real libraries do. Scan the patron's library card once, then scan book after book, without having to re-enter the patron or hit a button to continue. You do have to hit a button at the end however, to stop the loop when you're done; or if there's some problem.

Library_fej.zip

[dual_mon]

Sorry to be a grump, especially as I am new to FMForums, but why is this thread in the Custom Functions forum?

[Fenton]

Oops. I never noticed what forum it was. It looks like it was started in the wrong forum, and neither Ender or I are moderators of Custom Functions (in my case for good reason :-), so we couln't move it.