Recursive Conditional Value List?

[Joel C R]

I'm building an inventory database for our lab, specifically regarding the storage of cell culture samples in liquid nitrogen. I've got some familiarity with database design and normality, and was wondering if I could put the functionality of Filemaker to work for me.

There are two containers for these samples. They contain varying numbers of racks, and these racks contain a fixed number of boxes, and these boxes have different layouts (all depending on the container, essentially). Here's a basic overview;

Container 1

Has 6 racks

Each rack has 12 boxes

Each box has a "pie" shape, which looks like this:

        XX

        XX

       XXXX

       XXXX

      XXXXXX

     XXXXXXXX

     XXXXXXXX

    XXXXXXXXXX

    XXXXXXXXXX

      XXXXXX

        XX

  

Each "X" represents a sample (or a blank space).

Container 2

Has 4 racks

Each rack has 11 boxes

Each box is arranged in a simple 9 x 9 array.

I was thinking of making a "Containers" table with the following fields...

ContainerID

Name

Racks

Boxes

For each record (each container), I was thinking of just listing out the numbers in the racks/boxes fields.. for example

(Container 1)

RACKS

ONE

TWO

...

SIX

BOXES

ONE

TWO

THREE

...

TWELVE

And having a conditional value list identifying the location of a Sample based on those fields. However, identifying the specific location of the sample within the boxes is more difficult, because there are two different box types. Furthermore, one of those box types doesn't have any mathematically sensible pattern (so far as I know), so my initial idea of having serial identifiers for sample position and basing its row and column coordinates on some sort of calculation is out the window (as far as I know).

My idea was to have a conditional value list (Column) based on another conditional value list (Row/Boxtype) which, in turn, would depend on the type of box the samples are located in. Is this possible? Is my logic sensible? Alternately, I could just put in a graphic and employ a serial numbering system (with the graphic identifying the specific position). That, obviously, would be simpler. But I'm trying to make it easier on the user end.

Any recommendations?

Joel

[SlimJim]

Does the location have to be a number? Reading through your description of the problem I would have thought that you need to have 5 digit identifier for each sample: C.R.B.x.y where CRB represent the container, rack and box numbers and the x and y represent a row and column location within the box. So in the simple 9x9 box type you will have x between 1 and 9 and similarly y. Whereas in the box type that you drew it is more complex but is still a row.column location. Row 1 with column 1 or 2, etc.

[Joel C R]

Understood, but I want to limit the column / row selections to existing values. As in, someone can't type in "row 1, column 3" for a pie box, because such a location does not exist.

[SlimJim]

OK. Let me work on the assumption that the locations are fixed and the contents of the locations change, i.e. as you put different samples into the different boxes. One way to do this is to set up a full collection of records one to each location, however many that is, setting them up with empty contents - which I guess matches the real world initial situation of empty containers.

Then set up a separate navigation table with five fields: container, rack, box, col, row and relate this by a multi-key relationship back to your contents table. That is container = container, ..., row=row. You only ever have one record in this table which is the current location that you are describing. We have a one-to-one relation between the single record in the navigation table and a single specific location in the contents table.

To ensure that a real location is input you have to set up a cascading set of value lists and then fill in the data in a portal. The value lists are Containers, ..., rows. For each value list you make an occurrence of the contents table in the relationship graph and match it to your main contents table occurrence by the corresponding location field. Now to the value lists.

Container = "1", "2"

Rack = values from the field Containers::Rack, showing only related records

Box = values from the field Rack::Box showing only related records

etc

This will work so long as you have the full set of locations sitting in your contents table.

On the data input sheet make sure the operator chooses the container first and then set up the other entries using these value lists. Put the tab order to go through them in order with each field set as a pop-up list with the appropriate value list. Once a full location is picked the portal [edit: sorry you do not need a portal just drop the fields from the related table onto the layout] will show the current contents of the sample ready for entry of the new contents. (with whatever fields you need to describe your sample)

This is the theory, I have used a less complex example (each location with three coordinates) myself to describe the location of CD's in a collections of different sized shelves and racks and so long as you are careful not to delete any of the contents location records it works fine.

[Joel C R]

From what I understand, you'd have two tables, one to describe the sample, the other the position.

Then you'd have the following setup:

SAMPLE......................POSITION

[SlimJim]

For my suggestion to work the sample table has to be created in advance. This is because you are using the positions in that table to produce the value lists from which you choose a position. Once this is done the only changes made to that table are moving the sample descriptions in and out. It will be necessary to blank out the sample description fields if you remove a sample and leave a box position empty. (alternatively you could create a field with values empty/occupied and toggle it and then blank out the existing descriptions when you occupy again)

The cascading value lists all come from the positions in the samples table occurrences (there is no back and forth) when you choose container then tabbing to rack will pull up the rack numbers related to that container, then the next tab picks a box related to the container and rack and so on. The related choices are set up in the value list descriptions where you choose Show only Related Records.

With regard to the last question you have to validate the fields to ensure that that does not happen. That is, each value must belong to the appropriate value list, the operator cannot override and the values must be non-empty. I am not sufficiently familiar with bullet-proof validation to give a definitive answer to this - experiment with overriding the list and putting in incorrect values.

[SlimJim]

This is beginning to sound complicated and I may not have explained it very well so I am including a barebones example to show you how it works.

BareBones.zip

[Matthew F]

Joel,

I'm not sure I have an answer to your question. However I have constructed a FM solution for storing lab freezer samples including in funny "pie" shaped boxes like you mentioned. In case you're interested in how it was done I'm posting an empty template.

Matthew

p.s. This ripped out of a larger lab solution called the MPD so you might find an occasional odd link that goes nowhere.

[Joel C R]

The attachment won't download properly?

[Matthew F]

It downloads fine for me if I "right click" (control click) and select "Download linked file". It is a .sitx file and in case this works better for you I'm attaching a .sit file.

Cheers,

MF

[Matthew F]

P.S. If prompted for a user name type 'admin' and leave the password blank.