5 Replies Latest reply on Nov 6, 2014 4:20 PM by philmodjunk

    Models with multiple attributes...

    AlexPineau

      Title

      Models with multiple attributes...

      Post

      I cannot seem to overcome what is probably a simple solution.  I have a table for line items on an invoice. The items I sell have universal model numbers that have multiple colors and sizes (attributes).  When I create an invoice view I am interested in having a summary of those models rather than many line items showing the same model.  For example:

      Item #         Item  Name     Color   Quantity

      111              Jacket A         (Black)       5

      111              Jacket A         (Red)         5

      222             Jacket B         (Black)       2

      222             Jacket B         (Red)        2

      Would show on my invoice as:

      111              Jacket A                           10    (Expand)

      222             Jacket B                             4     (Expand)

      The user would then be able to click on the expand to see a dropdown or pop up of the breakdown of colors and sizes. 

      I am unsure if I use a certain function or if the problem lies within my structure.  It seems like there should be a function that Counts or Sums the items within an Invoice ID. 

      I am using a view of my Invoice ID with a portal that shows related records from Invoice details.  Can the portal group these items for me?

       

        • 1. Re: Models with multiple attributes...
          philmodjunk

          This is not something you would want to set up inside a portal unless you had no other option as this would take a lot of design work to do.

          You can set up a summary report on a layout based on invoice details that could produce one line for each Item #. The invoice details records used for such a report could be limited to a single order or combine data over multiple orders.

          The trick is to use a sub summary layout part "when sorted by Item #". If you use such a layout part and remove the body, you get one row of data for each different value in Item # provided that you sort your records by this field.

          Getting your drop down will be a bit more complicated. You might be able to use a portal inside popover for this and there are also ways to open a small floating window with this data or make the entire report expand to show the details upon the click of a button.

          • 2. Re: Models with multiple attributes...
            AlexPineau

            Thanks for the quick reply, PhilModJunk.  How do I limit it to just one order.  I am getting it to show all items across all orders, which is useful, but not for viewing as one invoice. 

            • 3. Re: Models with multiple attributes...
              philmodjunk

              Perform a find on the layout for just that one order number. Or you may be able to use Go TO Related records from the Orders layout.

              But a method for showing this data in a portal has also occurrec to me...

              • 4. Re: Models with multiple attributes...
                AlexPineau

                I would loooooove to hear your method for using a portal, as it would really make my current layout slick....

                • 5. Re: Models with multiple attributes...
                  philmodjunk

                  Ok, it's a method that I call a 'reach through' relationship. Data from table A is copied to table B to "reach through" it to data in Table C.

                  I'm guessing that you already have these relationships:

                  Invoices----<InvoiceData>------Products

                  Invoices::__pkInvoiceID = InvoiceData::_fkInvoiceID
                  Products::__pkProductID = InvoiceData::_fkProductID

                  If you are using a file that started out as a starter solution, the above field names will be different but they'll function the same.

                  To the above, we can add:

                  Invoices-----------<Products|Invoice------<InvoiceData|Invoice

                  Invoices::cProductList = Products|Invoice::__pkProductID
                  Products|Invoice::__pkProductID = InvoiceData|Invoice::_fkProductID AND
                  Proejcts|Invoice::gInvoiceID = InvoiceData|Invoice::_fkInvoiceID

                  Products|Invoice and InvoiceData|Invoice are new Tutorial: What are Table Occurrences? of Products and InvoiceData.

                  cProductLIst is an unstored calculation field with a text result type and this calculation:

                  List ( InvoiceData::_fkProductID )

                  gInvoiceID is a global field defined in Products. You'll need to add a script performed by the OnRecordLoad trigger with this script step:

                  Set Field [Products::gInvoiceID ; Invoice::__pkInvoiceID ]

                  Your portal would be a portal to Products|Invoice.

                  Now you can define calculation fields in products that use the sum function compute totals from InvoiceData|Invoice. Just be sure to select Products|Invoice as your context for these calculations.

                  Once you have this working a button placed inside this portal can open a popover placed outside the portal that uses its own portal to list the individual Invoice Items entries that produced the single row total in the portal to InvoiceData|Invoice

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

                  Caulkins Consulting, Home of Adventures In FileMaking