5 Replies Latest reply on Mar 13, 2015 9:22 PM by philmodjunk

    Generating a Total within a Portal

    JonathanCoulter

      Title

      Generating a Total within a Portal

      Post

      I have two tables for making a widget.  The first table contains general information on the widget.  The second table consists of each item required as well as how many of each are needed to make the widget.  Each item is in its own record.

      I have a relationship set up between the two, based on the widget's stock number.  I am NOT using any filter on the portal records.  Each widget is correctly showing only those items required, so I'm assuming I have the relationship filter set up correctly.  All this is functioning correctly.

      Now, I want a total of the number of pieces needed.  So if I need 5 of piece #1, 2 of piece #2, and 10 of piece #3 I want it to show a total number of pieces required of 17.

      I have created a summary field in the item table to total on the quantity field.  I have placed this within the bounds of the portal, and have also tried creating a duplicate portal.  In both cases, my "summary" is only giving me the quantity of the first record showing in the portal.  Obviously I want to total all the records listed in the portal.

      I have also tried using a calculation field, but the result is identical ... only the quantity from the first is showing.

      The portal is on a tab if that makes any difference.

      What am I missing?

      Thanks for the help!

        • 1. Re: Generating a Total within a Portal
          philmodjunk

          What you describe is typically referred to as a BOM or Bill of Materials table set up as part of an item's manufacturing spec.

          You should be able to put your summary field directly on your layout, not inside any portal and it should report the correct total. That assumes that you define the summary field as a field of the BOM table as a "Total Of' summary field and that your field for recording the number of parts is correctly defined as a number field.

          You can also define a calculation field in the layout's table that uses Sum ( BOM::Parts ) and it will compute the same total. This last option is a better one to use in cases where you are editing the portal quantities on the same layout as it will update more smoothly as records and quantities change in the BOM portal.

          • 2. Re: Generating a Total within a Portal
            JonathanCoulter

            Thanks for the input, but it still isn't working.  Yes, I'm creating a BOM and here is what I have.

                   
            • Table called Master (with fields for all the widget details)
            •      
            • Table called BOM (with three fields ... ItemNum, RMNum, Quantity)
            •      
            • Table called Materials (with details on individual BOM items, including a Description)
            •      
            • Relationship between Master and BOM on the field ItemNum
            •      
            • Relationship between BOM and Materials on the field RMNum

            On my layout, I have details from Master displaying.  I have a Tab Control for a variety of things, one of which is a BOM Tab.

            On the BOM tab I have a portal with three columns. BOM::RMNum, Materials::Description, and BOM:Quantity.

            I created a Calculation field, Master::Total with SUM(BOM::Quantity).  I have placed that field within the portal, on the tab outside the portal, and on the main layout outside the tab.  In all three cases the Total is always that of the first BOM item and not a sum of all the items listed in the portal.

            My portal is set up to use Active Row State (which it defaulted to, so I didn't change it), no sort, and no filter.  The filter is in the relationship.

            I tried creating the calculation field in the BOM table as well, but that gives an identical result.

            Probably something painfully obvious, but I'm just not seeing it.

            Thanks again!

            • 3. Re: Generating a Total within a Portal
              philmodjunk

              I'm not seeing it from here either. The calculation field should be defined as a field in master and it should sum the quantity field from the BOM table. The field should not be placed inside the portal.

              The thing to be careful of are your table occurrence names and the sum calculation field's "evaluation context". Table Occurrence names are the "box names" found in Manage | Database | relationships. When you select a name from "show Records from" in Layout setup (or in the new layout wizard) you are selecting the name of a table occurrence. When you select a name from "Show Related Records From" in portal setup, you are selecting a table occurrence name. When you select a name from the context menu inside Specify Calculation, you are selecting a table occurrence name. And the name to the left of the :: when you set up a calculation such as Sum ( BOM::Qty ) is also a table occurrence name.

              So if you have a relationship such as:

              Master::ItemNumber = BOM::ItemNumber

              Then the layout's table occurrence and the specify calculation context should both specify "Master". The portal's table occurrence and the name inside the sum function, should both specify BOM. If they specify something else, such as another occurrence of the same table located elsewhere in your relationship graph, your calculation field will not evaluate correctly.

              And make sure that you have set up a field of type calculation, not a number field with an auto-entered calculation as that approach will fail to automatically update when you edit the BOM records and their quantities.

              • 4. Re: Generating a Total within a Portal
                JonathanCoulter

                Just now getting back to this project, but your help has been greatly appreciated.  The calculation field now sums the portal ... sorta. :)

                When I enter a new related record into the portal, the calculation field is not updated (it's a sum, giving a total).  I am using an unstored calculation field.  If I enter several items into the portal, navigate away, then navigate back the calculation works fine.  If I update an existing value in the portal, the calculation updates just fine.  It only fails to work when ADDING a new record to the portal.

                I tried using a script onvalidate to refresh the object (calculation field) but that didn't seem to help.

                Any suggestions?

                Thanks again!!

                • 5. Re: Generating a Total within a Portal
                  philmodjunk

                  You need to commit the record. This can be done simply by clicking a blank area of the layout.