How to setup a layout to display contents of a Lab Freezer

interesting, I have no time now, I'll be back later on.
my 1st idea is to a all in one layout, and 1 single table for data.
I have to locate an old thing I did which was basically doing the same for a hierachical medicine doc.

For me two tables could do:
Box
 - box ID
 - rack

Sample
 - Sample ID
 - box ID (foreign key)
 - ...

This data structure allows to:
 move a box to another rack  
 move a sample to another box
 keep track of a sample if it leaves a box.

Sort by rack is a view of the Box list
Content of a box is a view of part of the Sample list

I spent a bit of time on this, you can either use a single table, and store in it rack box and sample #, or use 3 tables, 1 for racks, 1 for boxes, 1 for samples
to print/display a list, use a 2 sub-summaries in the samples table layout, based on rack then box.
to display the samples in a rack, use a simple find script, same for samples in a box.
you mention you want to see what boxes you have in a rack, etc... but is that the way they will use it? I would have thought users would have liked to locate a sample of which they know the name or serial number, in which case, this last feature is useless.
the approach with 3 tables is probably easier if you'd like to display boxes for a given rack, and samples for a given box in portals.
so I'd like to know a bit more on how it will be used since this is what will determine which ergonomy you need.

Hi Lesouef,

The entire database is structured around the samples.  The samples table has an ID field (Indexed, Auto-enter Serial Number) and about 16 attributes fields.  The layout’s I’ve created thus far are: Enter a New Sample, Sample Look-up, and Reports.  Users will use this database to keep track of their samples (over a 10 year period), log in test results, and search Freezers for availability of room as well as contents in any given box.  Some users want to be able to lookup a rack # and see what boxes it carries, and then be able to select a box and see all the contents of that box.  

So what I'm really looking to do is make it as graphical as possible.  For example:

Layout 1 = all the racks displayed by name (AA-AR) - then a user would click the rack name
Layout 2= another layout that would display all the boxes - then a user would click the box
Layout 3= the 10 x 10 grid would appear and list all 100 samples by name on the layout    

I see, you basically need both! I'd go for 3 tables then.
But you won't be able to make it as graphic as you'd like: everything can only be shown in columns only, except in preview=/print mode. But in interactive mode, you can only use a column display; that is a pb for only the 10x10 stuff.
If you really want that 10x10 grid, you'll need to have 100 fields per box, and create 100 links to the samples table, too much work I think, and would make the database very rigid; if you expand later on to say bigger boxes with 120 locations > trapped.
Now, this is the idea, what kind of help do you need, an example file or is the idea just enough?

An example file would be great, if you could send one.  

Now as far as you know there is no other way to graphically display a 10 x 10 grid in FileMaker?  That would show the sample names in each cell of the 10 x 10 grid.

Could it be done via a layout that has a drop down of the Rack and Box and once both are filled in (for example, Rack # AA and Box # 1) a GO button is clicked on a script is launched to find a set of records for RACK AA and Box 1 that fill the 10 x 10 grid Layout.  -- Make any sense?

ok, I did an rough example for the structure, but I just had another idea to divide the box in 10 rows to be able to display 10x10.
I then need some more time since it's dinner time!
I'll be back later on, maybe only too-morrow.

Sure, thank you for all your help.  

- david

That was perfect, thank you so much.  

one other question, how would i modify it if there where more than one Freezer?  

you need to create an extra table for freezers and have a freezerID field in every other table.
let me know if you'd like me to implement this in the former example. (too morrow now, I am on my way to my bed!)

yes please

thank you again.
- David

Also is there a way i can send you a copy of the DB that I'm working on so you can see what I'm trying to accomplish

either you post it on a http or ftpserver, or idisk (osX), or send it by mail, s at lesouef dot net

update:
http://www.lesouef.net/files/freezer.zip
includes freezer table, new boxes layout, free cells calc. and location search from yr database into my freezer (click on location box during sample creation)
I have not merged files together, though this could be done, I was too far ahead in the tests to recreate everything in yr file. I have linked them instead.
My boxes layout can be improved by showing what's inside the boxes; so far I only show cell # and free/busy colour.

Thank you again.  How would i go about merging the files together?  

you've got to import my tables in to yr files 'as is'
then import the scripts also.
then modify the relationship you use to have towards my file, to the new table.
and that should be it. you should then have 2 tables which will become redundant (rack and boxes)

you mean a freezer freezer? As an additional idea, take a picture of the freezer and use it as a background image on your layout. Use transparent field with text in a color that contrasts with the freezer.

rogier.

do you have a script that i can use to rename rackID field from locations table.  For the 2nd freezer, the names for the racks are AR, AS, AT, AU, AV, AW, AX, AY, AZ, BA, BB, BC, BD, BE, BF, BG, BH, & BI.  

- David

no, there is none, I did it manually; by the time I write the script, the 18 entries will be finished.
if you change that, you should delete boxes and locations, and recreate them all using the scripts create_boxes and create_locations (takes a while), otheriwse all refs to freezer 2 racks will be wrong.