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

I need to design a layout that will display the contents of boxes in a Freezer.  The Freezer has 18 racks (named AA-AR), each rack holds 13 boxes, and each box is 10 x 10 (with a total of a hundred spaces in a box).  

this database is focused on lab samples that are placed in vials and then placed in boxes into the freezer.  

Any suggestions on 1) how i should create the tables? & 2)how i should create the layout

I am using FileMaker Pro 8.  

Things the users would like to see:
-Sort by Racks to get a list of boxes that a rack holds
-Drill down to specific box and see contents (all 100 samples)

thank you,
David
davidaarongAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lesouefCommented:
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.
0
ZadkinCommented:
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

0
lesouefCommented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

davidaarongAuthor Commented:
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    
0
lesouefCommented:
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?
0
davidaarongAuthor Commented:
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?
0
lesouefCommented:
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.
0
davidaarongAuthor Commented:
Sure, thank you for all your help.  

- david
0
lesouefCommented:
see that to check whether I am on the right track or not:
http://www.lesouef.net/files/freezer.fp7

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
davidaarongAuthor Commented:
That was perfect, thank you so much.  
0
davidaarongAuthor Commented:
one other question, how would i modify it if there where more than one Freezer?  
0
lesouefCommented:
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!)
0
davidaarongAuthor Commented:
yes please

thank you again.
- David
0
davidaarongAuthor Commented:
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
0
lesouefCommented:
either you post it on a http or ftpserver, or idisk (osX), or send it by mail, s at lesouef dot net
0
lesouefCommented:
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.
0
davidaarongAuthor Commented:
Thank you again.  How would i go about merging the files together?  
0
lesouefCommented:
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)
0
rogierCommented:
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.
0
davidaarongAuthor Commented:
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
0
lesouefCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.