6 Replies Latest reply on Sep 8, 2015 3:18 AM by beverly

    Creating a Grid or Metrics Table


      Trying to figure out best way to create a grid metrics in FMP. I am able to create this very easy with PHP, Javascript for online apps but I wanted to incorporate this into my FMP project. See attached PDF for a visual example of how it would look.


      The project would loop on the top row 1 - 30 horozontal columns representing the number of an item (ex: 1- 30 widgets). The vertical rows would be a representation of how many items sold per day. Sales 1 to 20 sales per day of that widget. The corresponding fields that line up by column and row would represent the resulting price based on number of widgets * num sales for that day. (ex: 5 widgets * 10 sales of each * a static price of $10 = $500 per day). Of course the price I would be changing based on a separate field. Hope my explanation makes sense.


      Since in FMP it seems even when you loop values you have to create the tables with those fields to do so unlike creating dynamic temp values on the fly online. This requires more setup in FMP which is fine. Not sure the best way to set this up. I was going to create two tables with a third join table and fourth default price table that would basically hold the price. Example would be table #1: widget table with 30 entries in it or loop them dynamically, table #2 sales table representing 1 - 20 sales per day. Table #3 would be a join table those two tables matching 1 to 1, 1 to 2, 1 to 3 etc. This has the potential of getting big so looping is probably most efficient. Table #4 would be the price table allowing me to modify the price as I need to for user interaction basically to avoid putting a static price in each entry, avoiding redundancy of course.


      Where I am getting stuck at is how if possible could I create the grid like I have pictured. Am I missing a much simpler process or am I on the right track. I also posted a picture of the grid of the basic table setup. This was created with PHP/Javascript.


      Hope my explanation made sense. Still very new to Filemaker Pro so my understanding of the coding end is very much on the novice side but I am picking it fairly quickly and loving some of the built in features which is saving me a ton of code and time.


      I appreciate any input and open to any direction that might be more efficient than my process so far.

        • 1. Re: Creating a Grid or Metrics Table

          There are a variety of ways to create a crosstab report in FileMaker. One way you've already identified, a dedicated table. However, it's neither the most efficient nor the easiest.


          One way you can do this is to use a series of global fields as your column headers, combined with relational joins from those globals to a static table that contains the values you want. The values would be precalculated by a script ahead of time and reside in a flat-file table (sometimes referred to as a "stack" or "data warehouse"). It becomes a simple matter, then, to populate a global field with the corresponding key to match the value you want.


          Another method is to use a Virtual List approach. In this construct, a dedicated table is used that calculates its values "on the fly" based on the contents of a series of variables. You can search the forum or the web for details on the Virtual List, but in its simplest form, you might have a calculation that looks something like this:


               widget01 = GetValue ( $$widget01 ; row )


          where row is a serial number in the table, $$widget01 is a global variable containing a return delimited list of all the values for widget #1, and widget01 is the final result of the first row in column #1.


          A third approach is to calculate the cells completely in memory, building an HTML table and displaying it in a Web Viewer object using the data URL method. Since you're familiar with PHP and JavaScript, I'll assume you would be familiar with such a construct.





          • 2. Re: Creating a Grid or Metrics Table

            you might consider using repeating fields.

            • 3. Re: Creating a Grid or Metrics Table

              Another good option.  

              • 5. Re: Creating a Grid or Metrics Table

                Depending on how you would like to use the resulting grid, different methods may work better than others...


                For a quick view represented in a Web Viewer, see attached example I quickly put together.. It uses a looping script to generate the HTML (that could probably be optimised better than what I have done) based on the values you enter for numbers of rows, columns and price... Try entering different values in those three fields and you will see the web viewer change to reflect it using script triggers....


                Note I have only tested this file on Mac and FileMaker Go on iPad and iPhone, so don't know if the web viewer will behave in Windows... Also, probably not a good idea to put in excessive numbers into the rows and columns fields (could be limited by field validation settings if need be)... On my old macbook here it chokes a little if you go for something like 100 rows and 100 columns....


                Hope this helps,



                • 6. Re: Creating a Grid or Metrics Table

                  sorry, I *hate* when reply-by-email loses what I said...


                  another option? filtered portals (one row each). It depends on what you need to do. this might be a combination using execute sql.