5 Replies Latest reply on Nov 1, 2013 8:43 AM by Mike_Mitchell

    Best way to

    carlsson

      I am often asked to create layouts that looks like this:

       

       

      YearJanuaryFebruaryMars
      201110000125005000
      201215000180006000
      201313000200009000




      ...and so forth.

       

      Ie, a "Spreadsheet" kind of way to look at values. Personally, I think this is easy to view and if combined with a graph it's perfect!

       

      The trouble for me is that there are a couple of different ways to produce this. How do you guys do it? Relations? Repeated fields? Other ways?

        • 1. Re: Best way to
          Mike_Mitchell

          It depends. If the number of columns is predictable, a series of global fields as headers, coupled with a virtual list, works quite well.

           

          If, on the other hand, the number of columns is variable, an HTML table displayed in a web viewer is my preferred method.

           

          HTH

           

          Mike

          1 of 1 people found this helpful
          • 2. Re: Best way to
            carlsson

            Thanks for the input Mike!

             

            In this certain case it actually is predictable (Jan-Dec). It's an invoice report.

            I am no HTML king, so I rather not touch that. Unfortunately.

             

            You mention "virtual list". What's that?

             

            The way I'm thinking about to solve this:

            - Create a field from Invoice; cYear: Year(Invoice Date)

            - Create a field from Invoice; cYearandMonth: Year(Invoice Date) & Month(Invoice Date)

            - Create a table with all possible years and months (This is the ugly part)

            - Relate the values in the Year and Month table to cYearandMonth.

            - etc etc

             

            ...or am I messing things up?

             

            Edit: I googled Virtual list and reading about it now...

            • 3. Re: Best way to
              Mike_Mitchell

              You wouldn't need to create a table with all possible year / month combinations. (You're right; that gets ugly fast.) You can do it with a set of relational joins, or via the virtual list.

               

              To use relational joins, one way to accomplish it is to create a separate table where each record is equivalent to a year. You set a relationship between a global month (1 through 12) plus the year (which is a record in your reporting table). Those two predicates link up to the year and month of the invoice. Then, using calculation fields, you provide your totals. (One calculation field per month in the reporting table.)

               

              The nice part about that method is it's completely automatic, once set up. However, it does suffer somewhat from performance problems if you have a large number of records. If your data don't change for past years, you can substitute actual data fields and store previous years' data rather than recalculating them at runtime.. This will help with performance issues.

               

              HTH

               

              MIke

              1 of 1 people found this helpful
              • 4. Re: Best way to
                carlsson

                I looked and understand the concept of virtual list. Actually, I have made things like that before without knowing the term.

                 

                Is this actually an accepted tehnique? It feels very dirty?

                 

                I don't know why, but I'm not comfortable building new contents when we already have all the contents in the fields. The redundancy gene in me screams!

                Even if it's only temporary, it doesn't feel "right".

                And in a network environment with a lot of data, it feels like it should be slow?

                 

                But maybe I'm wrong...

                 

                At least it's a solution.

                • 5. Re: Best way to
                  Mike_Mitchell

                  It’s actually quite fast, because the calculations all occur on the client side. Since you put the virtual calculations in a table that doesn’t contain anything but unstored calcs, nothing downloads from the server. So there’s really no bandwidth impact. However, that’s the virtual list itself. The performance issue here is contingent on the number of invoices across the entire year, which has little or nothing to do with the virtual list; it’s based on the aggregate calculating FileMaker has to do. That will happen whether you use a virtual list or relational joins.

                   

                  As far as redundancy is concerned, one of the beauties of the virtual list technique is that nothing is permanent. You can literally use the same layout and table for hundreds of different reports, all assembled on the fly, as demanded by the user.

                   

                  You basically have two choices here: Either build some sort of structure that can create the reports on the fly (such as the virtual list), or generate the report data and store it in a separate table so it can be called up on demand. The choice between the two is going to be largely dictated by the amount of flexibility you require and the performance constraints.