How to effectively represent a 10X10 storage box

[oilcan]

Hello all,

I've been working on a new filemaker solution for my biology lab to keep track of our production and stocks. I'm approaching a certain aspect that I am having trouble figuring out how to present to the user. Well, to be truthful, I'm still working on how to model the data as well, but I think I have an idea on how to approach that.

So here's the problem. Most of our stocks are stored in small tubes that we keep in small boxes with dividers, separating the box into a (sometimes) 9X9 or (usually) 10X10

grid, so the box can hold up to 100 tubes. These positions are represented with letters for the columns, and numbers for the rows. So individual positions can be denoted by A1, B10, C6, etc. There are many boxes, and so the boxes are assigned a number as well.

My notion for the data structure as it stands now is a multiple join. A table for boxes, a table for columns, a table for rows, and the table for the storage items, all in one to many relationships to the join entity, storage locations. First, I have no idea if this is the right approach to this problem, if this will begin to slow down with the thousands of items that will be entered over time. it seems its either some fashion of a join entity like this, or its a box table with 100 position fields, which seems rather tedious and inelegant. I suppose I could also do 10 repeating fields with 10 repetitions each, but I read time and time again where people are very against using repeating fields.

Anyway, to the layout aspect, I would like to present the user a couple of different things. one would be a graphical representation of the box grid, each storage location showing a reference ID for what is stored there. Also, the user should be able to obtain a list of positions a particular item might be stored in with the column/row notation of D5, F7, etc. They need controls for entering new items, archiving removals, and also an ability to search for empty slots, a storage space finder if you will.

My notions for the layout at this point are rather vague. We have a current working solution with a bunch of checkboxes representing storage positions, but this method is quite limited in how the user can manipulate the data. In designing a database for our new production types, I was hoping to increase the functionality of our storage handling.

I'm curious if anybody has seen any working solutions for a similar storage problem, or offer any advice on how to approach the data structure or layout elements.

Thanks.

[comment]

I think you need a table for Boxes, a table for Slots, a table for Items, and a join table between Items and Slots. Boxes and Slots are related thru BoxID.

For the box grid, use 10 instances of a portal to Slots - the first instance showing rows 1-10, the second instance 11- 20, and so on.

[sbg2]

This intrigued me. My thought is in this instance you would want to create the grids/slots when adding a new box.

Attached is what I came up with in version 7. In version 8 you could get rid of the globals and work with variables. Maybe someone else knows a way to do this without creating the slots/grids up front.

Box.zip

[oilcan]

A while back I started this thread in the Layouts section because at the time I was thinking about the presentation layer of how to effectively represent a 10X10 box in filemaker. I messed around with it a little bit then, but found I should probably be focusing on the rest of my project as a whole.

I've gotten to the point where most of my database project is complete, and now I'm coming back to this bedeviling problem. But this time I wanted to bring this up in the relationships forum, as the relationships layer is a touch more complex than I first thought when I started the other thread, and it is proving most troublesome to get my brain around how the data should be represented.

The last portion of my project is this: I wish to effectively represent a 10 X 10 storage box. All of the stuff we make ends up stored in little tubes, in little boxes with divider grids that divide the box into 100 slots (sometimes 81, but for simplicity I'm currently pretending they are all 100). These grids are broken down into columns of A through J and rows of 1 through 10. The boxes are in turn stored in racks that hold 20 boxes. Each rack has a unique number. Each box in a rack is labelled with a lettter, A through T.

So here is what I'm trying to do. I want to represent these boxes in an easily manipulatable way to the user. I have constructed 5 tables relating to this problem. I have a table of Items (the things to be stored in the boxes). I have a table of Slots which consists of 100 records named according to a corresponding column and row (for example the first record is the upper left most slot, A1, the 100th record is the lower right slot, J10). I have a table of boxes which consists of 20 records, named corresponding to each letter a box may have in a given rack (A through T). I have a table called Racks, each record of which is to represent each unique rack number in our freezers. And finally, I have a join entity called Storage Locations, which I currently have joining all the other tables.

So the problem is, I don't know if this is the best approach. And if it is, I don't know how these things should be related. Should slots, boxes, racks, and items all have a foreign key in the join entity, or should I only have an item foreign key and a rack foreign key, then have boxes relate to racks and slots relate to boxes? Finally, after all of this stuff is related properly, how to approach the presentation layer? I had suggested to me a series of 10 portals showing 10 rows each of Slots, but how does it handle empty slots? A sample file from the old thread attempted to address that, but it used popup scripting to manually enter text in the box slots, which won't do (unless scripts can popup a dropdown menu, which I don't think they can). So if I approach this the way I've described, Will I need to generate 100 new records in the join entity each time a new box is added? How do you prevent duplicates? etc. etc.

So yeah, as you can tell I'm pretty deep in the fog right now. I hope to see what sorts of things people suggest regarding this problem to perhaps shed some light on how I'm finally going to approach this problem.

Thanks for any input you have. For what it's worth I've attached a small sample of the box I'm trying to build for those interested in seeing some really horrid naming and coding }:(

crappybox.zip

Edited June 1, 2007 by Guest

[Ocean West]

oilcan i merged your two posts and moved them to here. }:(

[comment]

You only need a join table where you want to keep history. Otherwise, it's straight parent-child relationships all the way:

1 Rack has 20 Boxes (match on RackID);

1 Box has 100 Slots (match on BoxID);

1 Slot has 1 Item at a time - but may have more in history.

Therefore, if you have, say 3 racks, then you have 60 boxes and 6,000 slots. Once you assign an item to a slot, it's automatically assigned to the parent box and a rack, too. If a box is moved to another rack, then all its slots, and the items in them, are automatically moved with the box - just like in real life.

A simple implementation will have 6,000 slot records (although it will be showing only 100 at a time). A more sophisticated scheme will use only 100 records, and compute their ID's on-the-fly, according to the currently selected rack/box (IOW, 100 REAL records, but 6,000 VIRTUAL slots).

[Ocean West]

perhaps a hierarchy or tree would help in outlining the rack?

http://www.filemakermagazine.com/videos/infinite-hierarchies-creating-a-folder-tree.html