6 Replies Latest reply on Sep 1, 2011 12:21 PM by ianmoree

    Relationship Question for any expert

    ianmoree

      Title

      Relationship Question for any expert

      Post

      I am throwing a question out there that i just cant seem to figure out no matter how much i read about TOG, TO & ERD's, but here it goes.

      i have a simple Invoicing system that has the following:

      Customer ->Orders<<LINES>>Products --> @Attributes 

       

      the @Attributes is a table that has many options similar to all my products we offer in our small bakery>

      the @ attributes look like this:

      cupcakes attributes
       size ( teeny, shots, medium, large)
       flavor (value lists)
       filling(value lists)
       topping ( buttercream, whip topping)
      cake attributes
       flavor
       filling
       style
       decorations
       color
       kitchen notes
       other notes
      suites
        come with 1 cake, 24 cupcakes or 48 shots, stand
        cake size = 6in
        cake flavor
        cake filling
        cupcake size (shots or medium)
        stand (white | gold | silver)
        design ( lady suite, edible print , fondant)
      pull aparts
       flavor
       filling
       topping
       size ( shot, medium)
       style / design
       pa-# (pa-24, pa-36,etc)

      *as you can see this is too much potential for repetition data. MY question is how can i design this from the start teh correct way or at least have a "modular system" & not end up with spaghetti mess of information,etc.

      -i

        • 1. Re: Relationship Question for any expert
          philmodjunk

          Structure your attributes records into this format:

          ProductID, Attribute Name, Attribute Value

          If necessary, you can define several Attribute value fields of more than one data type if you need to use these attributes in calculations. (That way you can put unit measurements, for example, into number fields, but have a block of text describing an attribute in detail in a different field.)

          • 2. Re: Relationship Question for any expert
            ianmoree

            Phil, in understand what you are saying, however; take for example;

             

            customer places an order, if they choose to order a pull apart, where would i pull this information out? lineitems portal that has a relationship to the @attribs table ? or 

            if they choose pull apart, take them to a separate table that has " ALL PULL APART ATTRibutes" , put the vaues in database with

            product id relationship , take back to original layout for next product?

            Table Structure Like So:

            productid_FK     Attrib Name Attrib Value

            related             message        "taken from user entry when type,etc? "

             

            am i correct in this assumption?

             

            -thanks

            • 3. Re: Relationship Question for any expert
              philmodjunk

              What role does the attributes data play in this transaction?

              If you just need to be able to see and read this data when completing the invoice, you can use a "detail" portal that displays these records for the current row in your Lines portal.

              Define a relationship between Orders and a new occurrence of @Attributes like this:

              Orders::gSelectedProductID = SelectedProductAttributes::ProductID

              It's usually best to define gSelectProductID to have global storage enabled, but isn't strictly necessary.

              You create SelectedProductAttributes, by selecting @Attributes in Manage | Database | Relationships, then click the button with two plus signs. You can doubleclick this new occurrence to pop up a dialog where you can give it a descirptive name like what I am using.

              Now put a portal to SelectedProductAttributes on your Orders layout.

              In layout mode, select the Lines portal and specify that the OnObjectEnter trigger perform this one line script:

              Set Field [ Orders::gSelectedProductID ; Lines::ProductID ]

              Now, when you click into a portal row, you'll see the attributes records for that product appear in the detail portal.

              It's also possible to set up a list view layout based on Attributes that lists all Attributes, Lines and Order data in a single report.

              • 4. Re: Relationship Question for any expert
                ianmoree

                i Actually need the data to be a part of the Order_ID_pk .

                eg. 

                customer : bill gates

                order # ____ 0001

                order_status : "PAID"

                if {product_id = pa-24

                then ask for this information & add to invoice or order #

                • cupcake flavors : red velvet, guava, nilla
                • style: ( edible print )
                • notes: image to be emailed
                • due date: Sept 23 @ 11am
                • message: "happy birthday baby!"
                • delivery choice : "NO"
                } then
                go to payments window.
                 
                - does that make it more clear?

                 
                 
                 
                • 5. Re: Relationship Question for any expert
                  philmodjunk

                  Brace yourself Wink, then you'll need another table where this data can be entered and the Attributes table can serve as a "template" for generating the records in this new table so that you can document these details for the order.

                  Customer---<Orders--<LINES>----Products---<@Attributes
                                          |             |
                                          |             ^
                                          ^         Line_Attribute>----@AttributesLookUp  (this is an occurrence of @Attributes)
                                SelectedLine_Attribute (this is an occurrence of Line_Attribute)

                  Customer::CustomerID = Orders::OrderID
                  Orders::OrderID = Lines::LinesID
                  Orders::gSelectedLineID = SelectedLine_Attributes::LinesID
                  Lines::LinesID = Line_Attribute::LinesID
                  Products::ProductID = Lines::LinesID
                  Products::ProductID = @Attributes::AttributeID
                  @AttributesLookUp::AttributeID = Line_Attribute::AttributeID

                  Your detail portal will now point to SelectedLine_Attribute and will pop up a set of records where you can fill in the needed customer input.

                  Your portal's OnObjectEnter triggered script will change to: Set Field [Orders::gSelectedLineId ; Lines::LineID ]

                  Line_Attribute can link to @AttributesLookUp by AttributeID and the fields in this table can use looked up value auto enter field options to copy data from it.

                  You'll need a script trigger set on the Lines::ProjectID field that updates the matching set of Line_Attribute records needed for this purpose.

                  FreezeWindow
                  Set Variable [$AttributeIDList ; Value: List ( @Attributes::AttributeID ) ]
                  Set Varible [$LinesID ; Value: Lines::LinesID ]
                  Go To Layout [Line_Attribute]
                  Loop
                     Set Variable [$I ; Value: $I + 1]
                     Exit Loop if [$I > ValueCount ( $AttributeIDList )]
                     New Record/Request
                     Set Field [Line_Attribute::LinesID ; $LinesID]
                     Set Field [Line_Attribute::AttributeID ; GetValue ( $AttributeList ; $I ) ]
                  End Loop
                  Go To layout [original Layout]

                  This is only the first part of the process to demonstrate the basic concept. If you should select a Product ID in the Lines Portal and then go back and change this to a different ProductID, the current design will add the attributes for the new product selection to the existing list of Attributes. The next development phase for this script would be to detect such a change, get user confirmation and then delete the original attributes for this line item before adding new ones.

                  • 6. Re: Relationship Question for any expert
                    ianmoree

                    Hey Phil, Filemaker need to hire you man.. You are so quick to share / divulge information. Will attempt and keep u posted. 

                     

                    -i