4 Replies Latest reply on May 29, 2012 11:25 AM by philmodjunk

    Summary in a Portal with multiple Records

    TracyBogans

      Title

      Summary in a Portal with multiple Records

      Post

      I have one table that contains all billing records. ( Sample Below Table 1).. Note I have other tables that have relationship between them to include Table1 ... Example  I have a Vendor Table , Account Number Table

      Vendor Table & Table#1  uses VendorName as Relationship

      Account Table & Table #1  Uses AccountNumber as relationship

       

      Table#1

      VendorMonth of ServicesAmountAcct Number

      Sprint                        Feb 2012                        $3                        111
      Sprint                        Feb 2012                        $4                        222
      Sprint                        Feb 2012                        $3                        333
      Sprint                        Mar 2012                        $4                        111
      Sprint                        Mar 2012                        $2                        222
      Sprint                        Mar 2012                        $3                        333
      Sprint                        Apr 2012                        $4                        111
      Sprint                        Apr 2012                        $4                        222
      Sprint                        Apr 2012                        $4                        333

       MCI                        Feb 2012                        $3                        102

      MCI                        Feb 2012                        $4                        202
      MCI                        Feb 2012                        $3                        303
      MCI                        Mar 2012                        $4                        101
      MCI                        Mar 2012                        $2                        202
      MCI                        Mar 2012                        $3                        303
      MCI                        Apr 2012                        $4                        101
      MCI                        Apr 2012                        $4                        202
      MCI                        Apr 2012                        $4                        303

       PPE                        Feb 2012                        $3                        120

      PPE                        Feb 2012                        $4                        220
      PPE                        Feb 2012                        $3                        330
      PPE                        Mar 2012                        $4                        110
      PPE                        Mar 2012                        $2                        220
      PPE                        Mar 2012                        $3                        330
      PPE                        Apr 2012                        $4                        110
      PPE                        Apr 2012                        $4                        220
      PPE                        Apr 2012                        $4                        330
       

       

       

      In the Portal I would like to see the following:

       

      Sprint                   Feb 2012                        $10  ( This is the total for Sprint Feb 2012 All accounts)

      Sprint                   Mar 2012                        $9

      Sprint                   Apr 2012                        $16

      MCI                       Feb 2012                        $10

      MCI                       Mar 2012                        $9

      MCI                       Apr 2012                        $16

      PPEFeb 2012$10

      PPEMar 2012$9

      PPEApr 2012$16

        • 1. Re: Summary in a Portal with multiple Records
          philmodjunk

          That will take creating a new table in your database with one record for each vendor for each month to do this in a portal.

          A summary report based on Table 1, however, can be set up much more easily to get that exact result with sub summary layout parts and a summary field.

          Here's the portal based method:

          You haven't indicated what would be the table occurrence for the layout on which you'd place this portal. I will thus refer to that table occurrence as "LayoutTO":

          LayoutTO::anfield X VendorMonth::anyfield

          VendorMonth::VendorID = Table1::VendorID AND
          VendorMonth::MonthOfServices = Table1::MonthOfServices

          You can define a summary field, sAmountTotal, in Table1 as the total of Amount

          You can then place a portal to VendorMonth on your Layout and include sAmountTotal from Table1 in the portal row to show each vendor's monthly total.

          The relationship for this portal can be modified to limit the records shown or you can use a portal filter to accomplish the same if you only want to see entrees for a given range of dates and/or a given vendor or vendors.

          You can use import records to load VendorMonth with records for your existing data and use script triggers to perform a script that adds a new record for a given vendor and month each time you add a record in Table1 that does not already have a matching record in VendorMonth.

          • 2. Re: Summary in a Portal with multiple Records
            philmodjunk

            Ps. I used VendorID instead of Vendor name in my example because I do not recommend using names in relationships. Vendor names are not always unique, vendors change their names and you can also have problems if a vendor name is entered incorrectly and you don't discover the error immediately. Using an auto-entered serial number field to unqiuel identify each vendor avoids those issues.

            • 3. Re: Summary in a Portal with multiple Records
              TracyBogans

              Sorry..

               I am not getting it...

               Table1  ( VendorName, VendorMOS, Acctnumber, Amount, sAmount) Example"
              Veizon, Jan,111,$1

              Verizon, Jan,222,$3

              Verizon, Feb,111,$2

              Verizon, Feb,222,$1

              ( sAmount is  =Total Amount )

              VendorTable ( VendorName , VendorMOS) in this table is a Vendor Name for every MOS that vendor has example

              Verizon , FEB

              Verizon, Jan

              Sprint, Feb

              Sprint, Jan

               

              I then make the relationship between the two tables

              Table1::VendorName --- VendorTable::VendorName

              Table1::VendorMOS --- VendorTable::VendorMOS

               

              NOw, where am I creating the portal... In VendorTable ?

               Result I am looking for is

              In this portal user will see example:

              Verizon .... Jan..... $4.00 ( This is the total amount just for Verizon in the month of Jan)

              Verzion .... Feb.......$3.00  ( this is the total amount just for Verizon in the month of Feb)

              as the user scrolls down in the Portal the will see summary information for the remaining Vendors ( In this same Portall)

               

              Sorry, I just not getting these results

              • 4. Re: Summary in a Portal with multiple Records
                philmodjunk

                You must put a portal to VendorTable, not Table1 on your layout to get the results you want.

                I am unable to provide much more detail than that as I do not know what, besides this portal you want to see nor if you want to see all records for all vendors and months or just a subset of months and or vendors.

                Can you provide a bit more detail on how you intend to use this portal and what other data besides the portal will be shown on that layout?

                I also would not use a text field with three letter abbreviations for the month as you will get matches to data from the same month, but of different years. I'd use a date field that stores the date for the first day of that month--there are simple calcualtions that can produce that data from an actual date--so that this field matches by both month and year.