6 Replies Latest reply on Jun 7, 2013 8:45 AM by philmodjunk

    excel like spreadsheet



      excel like spreadsheet


           Is it possible to make a list view report that displays rows of fields and columns of fields?

           For the Rows I would like to have a list of customers

           For the columns I would like to have fields which display products.  I am thinking of having maybe 10-15 columns.  Each field would have an If type statement that if this column field which corresponds to the customer's Row, shows the amount out.

           In the attached image I would like the A column to populate with how ever many customers have items out that correspond with the Columns B,C,D,E,F....

           Row 1 Can be static.  I will only need certain reports on certain products which are out.  Perhaps if I need 10 products than I could create 10 fields that do a search for that specific item ?

           The information will be pulling from my work orders table which matching those particular items which are still out.  IF they are not out than a "0" will be in that cell.

           I'm a bit of a loss how to start this.



        • 1. Re: excel like spreadsheet

               I tried making a field for "Cell B2" called "Full Camera Set"  and added this calculation.

               If (LINES::items_out  ≥ 1 and product_category = " A Full Camera Set";1;0 )

               So basically if Joe Smith  has  A full Camera Set ( Product A )  which would check from the line items having a value of 1 or more than place "1" in the cell or "0" otherwise.

          • 2. Re: excel like spreadsheet

                 If you have the typical table/relationship structure used for invoicing:


                 Then a List View layout (instead of table view) could be set up on a layout based on invoices with a series of  filtered one row portals showing data from line items in columns. The Filters would select for specific product ID's.

            • 3. Re: excel like spreadsheet

                   Instead of filtering by specific product ID's could I filter for certain product categories " text values"  instead of product Id's ?

                   So product A  searches / filters for Cameras.... Product B searches/ filters for Computers etc.  

                   Do you mean that each "Cell"  would be it's own individual portal ?


              • 4. Re: excel like spreadsheet

                     Each cell would be a portal row in a one row portal. And the portal filter can filter by a category instead of a prodect ID, but then you'll have an issue where a given invoice might have more than one item listed in that category. (That could also happen with product ID's). A summary field defined in the portal's table can be used to display an aggregate value such as the total of all such items for the given invoice.

                • 5. Re: excel like spreadsheet

                       instead of a filter would it be possible to have a field in each of those portals that has a specific defined calculation so that

                       the field "Product A"  needs to search the Inventory::product_category field to see if the text entered = " A Camera "

                       If Joe Smith has "A Camera" out as indicated by the search in product category and the search to see if the Lines::items_out > or = to 1

                       then place the amount of Lines:items_out in the field "Product A"

                  • 6. Re: excel like spreadsheet

                         Yes, but that would require defining a different relationship to a different table occurrence for each column of such data on your layout. That adds a lot of complexity to your design.

                         A "count of" summary field that returns the number of such items in the category would be much simpler to set up.