7 Replies Latest reply on Dec 16, 2016 5:42 AM by max88s

    Creating a "spreadsheet" look with Filemaker

    max88s
      I've been asked to provide a report on earnings that will require a "spreadsheet" look on Filemaker. I'm relatively inexperienced with the more advanced elements of Filemaker and am having difficulty putting a report together wherein the columns represent the months of the year and the horizontal lines represent the various sales people. I would like for each month to reflect the gross sales for each sales person. Can anyone help me with this? I would be most appreciative!

       

       

       

        • 1. Re: Creating a "spreadsheet" look with Filemaker
          philmodjunk

          Sounds like yet another cross tab report.

           

          A common way to do this is with a list view layout where one row portals form your columns of summarized (aggregate) data. Each portal might use a different relationship or you might set up portal filters. Summary fields defined in the portal's table can show totals, max, min, average, etc aggregate values based on the employee and the specified month.

           

          To learn more and to explore some of the alternatives, you might search this forum for threads dealing with Cross Tab reports.

          1 of 1 people found this helpful
          • 2. Re: Creating a "spreadsheet" look with Filemaker
            max88s

            Many thanks! I'll follow your advice.

            • 3. Re: Creating a "spreadsheet" look with Filemaker
              erolst

              A report for a year's worth of data can be implemented (relatively) easily using a repeating field and a bit of SQL, because the number of columns is fixed.

               

              Screen Shot 2016-12-16 at 01.23.15.png

               

              (The gaps are there because I was too lazy to enter records with matching dates ...)

               

              If you need a report for 12 months that could stretch across years, the calculation needs to bit a more sophisticated.

               

              Have a look at the attached file that demonstrates the concept in a minimalist way.

              • 4. Re: Creating a "spreadsheet" look with Filemaker
                FileMakerMentor

                Your talking about making a pivot really, and this can be done in a few different ways. I'd make a table with the period records in, say MonthYear, create a field in your source Table to create the Same MonthYear (you can make a calculated field using date calculations to do this). Then Make a relationship based on the two tables, by joining the MonthName Fields. You can then place a series of 1x1 portals across left to right, and then use filters to show the data you want in each one. Portal filters wont refresh on their own, so include a button to refresh the object or window.

                 

                I know this is a lite description of what you need to achieve, and i saw above the suggestion of SQL, although I like to keep things native where I can.

                 

                If you have the answer you need, great, otherwise, happy to give some further guidance, or even via our mentoring service which is metered and with no minimum cost. If it takes five mins we bill for five mins.

                 

                You will get a wide range of suggestions here, ranging from very simple, to highly complex, our aim is to keep it simple, and most importantly help the mentee, to understand the FileMaker tools, rather than just giving directions, or doing it for you.

                 

                Hope your able to get something working that makes sense to you.

                 

                David

                1 of 1 people found this helpful
                • 5. Re: Creating a "spreadsheet" look with Filemaker
                  max88s

                  Hello David, Thank you so much for taking the time to walk me through this. I am a real novice at Filemaker, although I've been slugging away at a solution that I'd like to market for going on 10+ years. I intuitively figured out that I needed to create relationships to filter the various months on my columns. But I get lost after that. So I would like to schedule a session with you to tutor me through the balance of my task. Please let me know how to schedule this. Any time is fine by me. Again, thank you! - Mac Frampton

                  • 6. Re: Creating a "spreadsheet" look with Filemaker
                    erolst

                    Why don't you take a look at the sample file?

                    • 7. Re: Creating a "spreadsheet" look with Filemaker
                      max88s

                      Thank you! I just downloaded the sample file and will study it. You have been a huge help!!!!