5 Replies Latest reply on Jan 26, 2012 1:15 PM by philmodjunk

    Relationship Limit



      Relationship Limit


      I am new with FM and new to DB construction in general.  My question, what is the best method to limit relationships to a max qty while maintaining serial identification/Traceability?  For example, 5 pill bottles per box.

      I tried a line items table method with five occurrences of the bottle, but was unable to ensure the same serial number was not used across occurrences, only within occurrences.  Attached is an example of the relationship diagram.

      Something simple is missing, I am aiming to try to control the data with little scripting.  Any thoughts on how to re-arrange the tables/Fields?



        • 1. Re: Relationship Limit

          What is the difference between a "Box" and a "Pill_Box"?

          Will the pill bottles packed in that box always be the same or could bottles with different contents be packed in the same box?

          Are the bottles always the same size/shape?

          • 2. Re: Relationship Limit

            In this instance Pill _box is no different than a line items table creating line items to fill an order/Box.  

            There can be between 1 and 5 pill bottles in each box.  The bottles can be filled with different pill types, with different data stored in the pill bottle attributes.

            • 3. Re: Relationship Limit

              Then you definitely do not need multiple relationships to set a limit on the number of pill bottles packed in your box.

              To repeat a question not yet answered: Are all pill bottles the same size and shape?

              To add a new question: Are all boxes the same size and shape?

              If you have all boxes the same and all Pill Bottles the same (in terms of physical dimensions, not contents), this is an easy problem. If they are not, this can become very complex to manage due to the very large number of variables involved.

              • 4. Re: Relationship Limit

                 Bottles and boxes are all the same size and shape. 



                • 5. Re: Relationship Limit

                  Then you simply need to limit the number of related Pill_Bottle records (or the total of their quantity fields) to the maximum allowed for the box.

                  Key design decision needed here: One Pill_Bottle record for every bottle in the box or do you want to add a Qty field so that if you add 3 bottles of "aspirin", you can create one record for it with 3 in the quantity field?

                  With one record for every bottle and a fairly small number of bottles to every box, you might not need to do more than set up a portal to Pill_Bottle on your box layout sized to have exactly the same number of rows as the max number of bottles and no scroll bar to allow the user to go over the limit.

                  Otherwise, you can set up a calculation field, cBottlecount,  in Box as either Sum ( Pill_Bottle::Qty ) or Count ( Pill_Bottle::Box_ID ) to check to see if the limit for a given box has been reached.

                  A validation rule on a field or fields in Pill_Bottle with this calculation:

                  Box::cBottlecount < 10

                  could be used to limit the pill_bottle records created for a given box to no more than 10 bottles. Trying to enter data to create a 11th bottle record sould throw up a validation error message.