3 Replies Latest reply on Apr 3, 2012 5:08 PM by cortical

    Count of items in a portal

    khardy.dsi

      I have a database of Companies. Each company has multiple locations. Each location has assets. I have created portals to list the assets that for each location and the entire company. Each asset has a type (chair, desk, table, etc...). In my current portal for each location i have a list of assets (chair, chair, chair, desk, desk, table), I would like to see a portal list that displayed Chair (3), Desk (2), Table (1) and be able to "drill down" to the asset I am looking for. Also for the company, I would like to see a total count for all locations. Stumped?!

        • 1. Re: Count of items in a portal
          ian.moree

          Perhaps a limited data file to see would help someone assist.

           

          The Database of companies has Many Locations; with many assets.

           

          In the current portal (each location) ; you would lie to see a list displaying assets with a COUNT() correct!

          for Company - need a TOTAL COUNT for all locations.

           

          -i

          • 2. Re: Count of items in a portal
            AlanStirling

            Hi there,

             

            Instead of showing the assets in a portal, use a 'Go to Related Records' script step and then display the records in a list view, sorted by asset type.  Swap the Body part for a Sub-Summary on change of Asset type, add a summary field for the quantity and you're done!

             

            Best wishes - Alan Stirling, London, UK

            • 3. Re: Count of items in a portal
              cortical

              A - Company ( PK: company_id)

              B - Location ( PK: location_id )

              AB - CompanyLocations ( PK: company_location_id, FK: company_id, FK: location_id)

              C - Asset ( PK: asset_id, FK: company_location_id) - could add company_id and location_id and denormalise for easier dev checking if required

               

              REL  A-AB = compnay locations, portal uses rel

              TBL A, field c_company_locations_count = Count( AB::company_location_id) is the company location count

              TBL A, field c_company_asset_count = Count( C::asset_id) is the company asset count

               

              REL AB-C = company location assets

              TBL AB field c_company_location_asset_count = Count( Asset::asset_id) is the company loaction asset count

               

              similar approach to count all assets at a  location

               

              Now it might be the case that a given location is related to only one company, so the join may be unecessary. But then again, if the location was a warehousing one, and stored assets for multiple companies... But that could be approched a slightly different way probably. Or if a location was sold to a different company... The point is that not using a join is potentially limiting.

               

              As for the 'drill down'

              REL A to AB, CompanyLocations ( or B Locations if a location is only ever one, and only one company)

              parse the company_location_id to a global to establish a REL using  g_company_location_id = C::company_location_id

              this will establish REL A::C to show all company assets at a  company location

               

              script button on the portal row to parse the company_location_asset_id to a global

              REL A to C  , and portal, for selected company asset, g_asset_id = C::asset_id

               

               

              OR as an alternate to the A::C use a single row iteration of AB to C ( company location assets)  and filter it by C::asset_id = g_asset_id

               

              Or somthing like that.