6 Replies Latest reply on Apr 10, 2015 7:55 AM by petery009

    Excel Table Like Report

    petery009

      Hi All

      Wonder if anybody has idea or experience of building a report like the format below:

      There's a Site table, in that table, there's a Zone field (drop down value is editable: North, Edmonton, Central, Calgary ...)

      There's another field Type (drop down value is editable: Addiction & Mental Health , Cancer Care ....),

      # is the number of the sites.

      report1.png

      I did have some ideas like:

      1. create a new table, every time user wants to generate the report, create new record and set the Type for each , create a repeating field that store Zone value, and another repeating field to store # of site fields.  Make the report based on new table and show in list view. (logically work.)

      but i don't like it, a bit of complex of calculation, and what if multiple user wants to generate the report at the same time.

      2. use Web viewer, use html we can create table and fill the value in the table (theory seems work), again this one, i don't know if the print is an issue or not

      ....

       

      Thanks,

      peter

        • 1. Re: Excel Table Like Report
          mikebeargie

          printing would be a big issue for #2, but if you export the HTML then it would work fine.

           

          #1 is the way to go for complete control in gathering your data, and can be used to feed your table in #2 as well. This technique is called the "virtual list" technique. Seedcode has a good demo on this technique: http://www.seedcode.com/virtual-list/

          This technique would allow you to conditionally format your "cells" (fields) in filemaker as well to match the formatting in your picture above.

          • 2. Re: Excel Table Like Report
            mikebeargie

            oh, and use virtual lists, using repeating fields creates an ungodly amount of overhead. virtual lists keep your data in memory, and calculate out each cell with simple calculations like:

             

            GetValue($$virtualData ; Get(RecordNumber) )

            • 3. Re: Excel Table Like Report
              erolst

              All good suggestions by Mike; I'm partial to building simple HTML tables from a looping script, because it's relatively straightforward, and lets you do nice stuff like this:

              HTMLTable.png

               

              (Imagine some fancy CSS added so it's really nice and be aware that you need to cater for null values to prevent misalignment.

               

              To manage multiple users within the same virtual list table, add a field for userID / accountName / otherDistinguishingFeature, and have the script take that into account.

              • 4. Re: Excel Table Like Report
                Devon Braun

                HTML approach is great, and you can likely do this without it as well.  The table you're showing would be more of a summary table.  There would be a second table that holds the actual data in line item form.  On the summary table, under the layout scheme you illustrated, the zone headers would be global text fields with drop down menus.  The table itself would hold has many or as few of the types you would want to print.  The fields showing the number of sites would be calculated fields.  The calculated values would reference the values in the zone headers and the site types field, and use that to summarizes data held in a second table.  That second table would have at least the following 3 essential fields: Zone, Type, and a Site Count.

                 

                That's all assuming I'm understanding your question correctly, but it seems quiet possible to do what you're proposing with a total of 2 tables, no repeating fields.

                • 5. Re: Excel Table Like Report
                  beverly

                  Hello, Peter. HTML, Virtual List both great ideas and methods I've used. You might research 'cross tab reporting' with FileMaker. This forum and others can point you to other methods.

                   

                  Here are some links to get you started:

                  Fast Grid Displays on a WAN | FileMakerHacks

                  cross tab | Search Results | FileMakerHacks

                  beverly

                  • 6. Re: Excel Table Like Report
                    petery009

                    So many good ideas.

                    Thanks everyone. I will explore all of the techniques provided here.