11 Replies Latest reply on Aug 12, 2013 3:53 PM by trent.fowler@hci.utah.edu

    Refrigerator Storage



      Refrigerator Storage


           I am in the process of creating a database for the Lab that I work for. We have between 800 and 1000 samples that we would like to track. Thus far I have a table set up that captures all of the information that I need on each sample (collection date, time, notes), but I am trying to figure out the best way to map the storage of samples and how many tables that would require. Any suggestions on the best way to do this would be welcomed.

           Thus far, each sample is physically stored in a single cell of a 9x9 box. Each Box is in a rack and there are two racks. Each Rack is in one of three refridgerators.

           For Example:

           -Refrigerator 1

                - Rack H

                      - Box 6

                            - Cell 62

           I would also like to display this information graphically in a way that I can click on a cell and bring up the related record. I have uploaded an image of what the boxes should look like.

           Let me know if I can clarify at all. Thanks!


        • 1. Re: Refrigerator Storage

               You seem to be on the correct path  here:


               Refrigerators::__pkRefrigeratorID = Racks::_fkRefridgeratorID
               Rackes::__pkRackID = Boxes::_fkRackID
               Boxes::__pkBoxID = Cells::_fkBoxID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               I am assuming that your example image shows the contents of a single box. A layout based on boxes can use a grid of one row portals Cells to show the contents of each cell in the box. The field in each such portal can use Go To Related records to take the user to a record for that Cell on a layout based on Cells.

          • 2. Re: Refrigerator Storage

                 This idea will work great. Yet, I am slightly confused on the implementation of the one row portals. I have all of my relationships set up (Refrigerators------<Racks----<Boxes-----<Cells), but do I need an additional occurance/relationship since I need each portal to show related records from both the correct box and the related cell number? To simplify, how should my portal be set up?

                 Thanks Phil!

            • 3. Re: Refrigerator Storage

                   You will not need another occurrence if you are using FileMaker 11 or newer, you can put a filter expression in that filters the portal for a specific cell.

              • 4. Re: Refrigerator Storage

                     The filter worked perfecly for displaying each item in each cell. However, it is making the "Go to Related Record" tricky because it goes to the first related record in the unfiltered list of related records (goes to the first record every time). Any suggestions on how to fix this while retaining a found set (not entering find mode in a script)?

                     Also any suggestions on how to ensure a cell is not double booked?

                     Finally, any suggestions on how to display records from multiple tables in a single box (i.e. if I wanted to split my samples into blood, saliva, etc). I am guessing I would need an intermediary table?


                • 5. Re: Refrigerator Storage

                       Why not use find? Constrain found set could be used to apply the same effect as the portal filter to drop you down to just the record for that cell.

                       If you want to see all the cell records for trhat box, you'll need to be a bit more creative. You can capture the ID of the cell record at the time it is clicked, set a global field in Cells to that value and after the first GTRR, do a Second GTRR where you reference a self join relationship to a second occurrence of Cells based on that global field.

                       Or your script can loop through the records until the cell record with ID is reached.

                       Also any suggestions on how to ensure a cell is not double booked?

                       Given the above format, I'm not sure how you could double book a cell without making an extremely obvious to the user error, but if you set up a text field that combines box and cell ID in a single auto-enter calculation (don't use a calcualtion field), you can specify a unique values validation.

                       Finally, any suggestions on how to display records from multiple tables in a single box

                       I gather that you want to put each type of sample in a different table. I suggest that you NOT do that unless you have a very good reason for doing so. If you do go that route, you'll need either that intermediary table  or a match field in your cell record that links to each of the different tables. In the Second Case, a second field in Cells would be used to identify the type of sample (and thus which related table is linked to it).

                  • 6. Re: Refrigerator Storage

                         It all worked well.


                         Final question - Is there a way I can create a dynamic value list that is based on another dynamic value list? In the attached picture I would like to go down the fields and have each subsequent field show only the related records from what the previous entry is.

                    • 7. Re: Refrigerator Storage

                           We call dynamic value lists Conditional value lists. And I"ve taken to calling a "chain" of conditional value lists where a selection from each list filters the values listed in the next, hierarchical conditional value lists.

                           Here's my standard list of links on conditional value lists. The last one listed refers to conditional value lists.

                           There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                           The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                           Forum Tutorial: Custom Value List?

                           Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                           Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                           Hierarchical Conditional Value lists: Conditional Value List Question

                           Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                      • 8. Re: Refrigerator Storage

                             Thank you for the resources, especially the Hierarchical Conditional Value Lists. That is exactly what I am looking for. However, my problem is just a little different because my tables are tangentially related. I am realizing that I need two different occurences of each table, but I am lost on how to ensure my value lists are correct. Any suggestions would be great!

                        • 9. Re: Refrigerator Storage

                               Again, I am choosing storage options based on a "Samples" Layout.

                          • 10. Re: Refrigerator Storage

                                 You need to set up additional table occurences that match the form shown in the thread on Hierarchical Conditional value lists. Your conditional value lists will each list values from one of those added table occurrences and specify Samples as the "starting from" table occurrence.

                            • 11. Re: Refrigerator Storage

                                   Worked Great, Thanks!