7 Replies Latest reply on Jul 21, 2013 10:41 AM by philmodjunk

    Conditional Value List, Without Relationship



      Conditional Value List, Without Relationship


           So I am building a DB to track containers that hold computer hard drives. When adding a new entry (A hard drive) to the DB I need to select which box it is in. I want this to be a dropdown that only shows boxes available from the boxes table. I acheived this by setting my relationship between "Inventory" and "Boxes" to Status = BoxStatusMatch. So the trick here was setting the "BoxStatusMatch" for all hard drives to auto enter "Available". So when I use the relationship for the value list it will only show boxes i which status = Available. 

           This works great but I also need to be able to show how many items are in each box. So if I am looking at a Box record it will show that X number of hard drives are in this box. I alos want to be able to show the Box Type field on the Inventory entry layout so users know what type of hard drives are in that particular box. Well that would be easy if I had the relationship between "Inventory" and "Boxes" set to "Box Number" but then my conditional value list wouldn't work. 

           So what is the easist way to keep my conditional value list, but also show the # of items in a particular box?

           Would it be better to setup a portal to the "Boxes" table and filter that by Status = Available and make a way to populate the "Box Number" field for the new entry when highlighting a specific box from the portal? 


        • 1. Re: Conditional Value List, Without Relationship

               You aren't limited to a single relationship between a given pair of tables. You can define as many different relationships as you need.

               The trick is to use the duplicate button (labeled with ++) in Manage | Database | Relationship to make additional Tutorial: What are Table Occurrences? of your Boxes table so that you can select different sets of match fields for different relationships.

          • 2. Re: Conditional Value List, Without Relationship

                 So I started down that route but I think I was thinking of it incorrectly and tried to duplicate the "Inventory" occueence and not the Boxes table.

                 I also can't get the number of items in a box "Box Count" by setting this field to a summary of "Box Number"  I am also not sure how to get a count of the items in a box in the actual box record. "Slots Full" field.

                 My thought on this would be to set "Slots Full" to a calculation that would count instances of box number that match the box number in the current record in the box table. Is this possible to do?

                 Slots Full = If (Inventory::Box Number = Boxes::Box Number; Count(Inventory::Box Number; 0) 

                 I tried this calculation and it doesn't work. its just the logic I was thinking

            • 3. Re: Conditional Value List, Without Relationship

                   You can't use if like this to selectively count ( or sum, or average or any other aggregate function...) a subset of your related records. Instead, you need the relationship you use to only match to the records you want to count and then count ( RelatedTableOccurrence::NeverEmtyField ) will produce the count that you want. And there are other ways that you can get that sub total also.

                   Let's back up from all that a moment and look at the big picture. Please answer the following questions. I need to make sure that I am not making any incorrect assumptions about the structure and intended function of your database tables.

                   What does one record in Boxes represent? Does it represnt a box of a specific size/design or is it a record that documents what items from inventory are being packed in a particular box? I have assumed the second possibility.

                   What does one record in inventory represent? Does it represent a single, one of a kind item or does it represent a group of identical items that all might be shipped in different boxes to different destinations? In other words, does one record represent the the 50 Ford Mustangs that you have in stock or does it represent one Ford Mustang with a specific VIN#?

              • 4. Re: Conditional Value List, Without Relationship

                     So to answer your questions:

                     1: A record in "boxes" refers to a specific box/container. We have numbered boxes that hold 28 hard drives each (Sometimes we put other items in them and then the count would be different than 28, but that the defualt). 

                     2: A record in "inventory" refers to a specific hard drive or item in a particluar box. So 1 specific hard drive with a specific ID#

                     The reasoning for this is so I can setup a way to see what box is available to put hard drives into without having to go into our storage room and physically check. 

                     I figured out the value list part, where only box numbers that have a status of "Available" show in the drop-down list, but now I need to automatically switch a box from "Available" to "Full" once 28 records (Or once "Slots Full" = "# Of Slots").

                     I understand how to make the status change, but I can't figure out how to get the count of each "Inventory" record that matches the particular box when broswing records of boxes in the "Box" table.

                     I had it working in a layout for "Invenotry" before but now its not even working right there. the relationship was setup to match "Box Number" and then when a record in "Inentory" had a box number of, lets say for examble, 100 then it showed a count of all "Inventory" records that also had a box number of 100. 

                     Since I can't use that relationship in order for my Conditional Value List to work correctly, I am not sure how to acomplish this. 

                • 5. Re: Conditional Value List, Without Relationship

                       So one record in boxes matches to one slot in the box, not one box with 28 slots?

                       In which case, I'd add another table here to have:


                       Boxes::__pkBoxID = Slots::_fkBoxID
                       Inventory::__pkInventoryID = Slots::_fkInventoryID

                       You can place a portal to Slots on the Boxes layout to list and select Inventory records for each given Boxes record. Fields from Inventory can be included in the Portal to show additional info about each selected Inventory record and the _fkInventoryID field can be set up with a value list for selecting inventory records by their ID field.

                       You can use a script performed via a script trigger to update a status field in boxes each time you exit the portal object.

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

                  • 6. Re: Conditional Value List, Without Relationship

                         One record in boxes refers to one box. Each box has 28 slots (By defualt/Auto Enter) This can be overridden though. I had this as a value/field of the box record. So box 1 has 28 slots, Box 2 has 28 slots, Box 3 has 24 slots etc. 

                         One record in Inventory refers to one item placed in one slot in a box. So when we open a new box I want to go in and add a new Box record to the Box table. We would enter the box ID, and number of slots available in the box. 

                         Then when someone has a hard drive that needs to be placed in a box they go in and add a new record in the Inventory table with the hardDriveID(Barcode), Box Number (the box it is to be placed in),user, etc. I want users to easily be able to see what boxes have X number of slots free. 

                         So if I add 25 new records into the Inventory table and put Box number 1 in the Box Number field, I should be able to go to a layout showing records form the Box table and look at the record for Box number 1 and see that Slots Full = 25. I can then use this in my layout for entering Inventory records to show "Number of Slots" - "Slots Full" that way users can see that they can add 3 more hard drives to this box.

                         So logically planing this out I simpy want the field "Slots Full" to be a calculation of how many Inventory records have the "Box Number" field that matches the current record in the Box record. 

                         I beleive your solution for adding a new table for slots would work but then we would have to go in and create X number of slots each time rather than just adding a new Box record. My first screenshopt shows the fields for each table (Box, and Inventory)

                    • 7. Re: Conditional Value List, Without Relationship

                           And adding those slots really isn't a big deal as you can automate that process with a simple script.

                           However, you can also use this relationship:


                           Boxes::__pkBoxID = Inventory::_fkBoxID

                           The same scripted update method can be used to update a status field in Boxes via an OnObjectSave trigger on the _fkBoxID field--which you would set up with your conditional value list of available boxes.