Lending Library help for noob

[bhmohr]

Hi,

 

I'm trying to create a lending library solution for all the sheet music at my son's music school that hopefully will include the use of a bar code scanner (although I haven't yet figured out how that would work - perhaps a subsequent post?).  Attached is the database structure I've created thus far, but I'm not sure if it's sufficient.  

 

To begin with, the school only had one table (MusicLibraryMaster).  My thought was to add a table for Copies (1 piece of music can have as many as 100 copies), a table for patrons (there are 50 or so students) and a table to record all the transactions called Checkout_Tran, that would have a new record created in it whenever a patron checked in or checked out a copy.

 

I thought the best way to track the checkout or checkin status of a particular copy (and the associated patron who borrowed it) would be via the Copies table - writing the PatronID and a boolean checkout state for each copy's record right there.  For example, when a copy is checked out to a student, the Copies::Checkout_Status field would be set to TRUE and the Copies::Patron_ID field would be set with the student’s PatronID.  Upon checking in the copy of sheet music, the Copies::Checkout_Status field would be set to FALSE and the Copies::Patron_ID field would be set to null.

 

At the same time, a new record for each checkout or checkin transaction (capturing that copy's current state and borrower) would be created in the Checkout_Tran table.  The Checkout_Tran table would serve as a running history of all checkouts and checkins of all copies by all patrons.

 

Would my current structure work for this?

 

The script help I need involves automatically generating n-number of child records in the Copies table for EACH of the almost 500 pieces of music in the MusicLibraryMaster table.  Going forward, every time a new piece of music is entered (committed?) into the MusicLibraryMaster table AND the MusicLibraryMaster::copies field is not null, a script would automatically create as many child records in the Copies table as is specified for that new record in the MusicLibraryMaster::Copies field.

 

Specifically, the script would create (insert) serialized child records (one per copy) as follows…

a.    Piece_ID = Piece ID of the record the cursor was on (this would be the Foreign Key in the Copies table)

b.    CopyID = "PieceID-Copy#" (incremented from 1 to n where n= the # of copies entered for that piece of music in MusicLibraryMaster::Copies field (this field would be the Primary Key in the Copies table)

c.    CheckOut_Status = null

d.    CheckOut_Patron = null

e.    Create_Date = auto-filled with timestamp

f.    Create_User = auto-filled with user

g.    Modify_Date = auto-filled with timestamp

h.    Modify_User = auto-filled with user

 

So, for example, for the record with Piece_ID = 525 in the MusicLibraryMaster table if there were 80 copies of that piece (MusicLibraryMaster::Copies = 80), then 80 records would be created serially in the Copies table, each with a unique Copy_ID starting with 525-001 and going to 525-080

 

That would be for NEW records as they’re entered.  I’d also like a script that would enable me to generate the necessary child records for all of the  500+ records that ALREADY EXIST in the MusicLibraryMaster table.

 

What do you recommend?

 

Thanks,

 

Brian

(never used Filemaker before)

(using FileMaker v.9.0)

Tables.pdf

[Lee Smith]

Hi Brian, and welcome to the Forums,

 

I don't have v9 on this computer, so I can't look at the example files it has. However later version have an example called lending library file with them.

 

I think a good place to start would be to search Google for  site: fmforums.com Lending Library

 

BTW, I will be moving your thread to something more in line with your questions, as this topic is reserved for finding a ready made off the shelf commercial product.

 

Lee

Automatic message

This topic has been moved from "Solutions Wanted" to "Relationships".

[Tom R.]

Hi Brian,

 

I see this post is from a while ago. I think your relationship graph looks good. Do you still need advice on your project?