3 Replies Latest reply on Sep 16, 2013 3:41 PM by philmodjunk

    Novice at FileMaker, need to create a recurring report and need suggestions!

    StellaBear

      Title

      Novice at FileMaker, need to create a recurring report and need suggestions!

      Post

           I am contracting at a help center to create reports and analyze data.  The company wants a report and suggested using FileMaker, so I've been going through the FileMaker Training Series on my own the last few days.  I made it first in Excel but since the data will be updated every two weeks, it needs to be as automatic as possible.

           Here's my situation:

           I have a spreadsheet with 20,000 rows of calls from 2013.  I need to categorize the data into 2 week date ranges.  Then I need to count how many cases each rep gets within each of the 2 weeks.  Then I need to create a table with the date ranges across the top (in the columns) to compare how many calls each rep completes every two weeks.

           I would like to refresh this data every two weeks.  New reps can be added anytime.  :(

           How should I proceed?  Calculations, new layouts, scripts?  Is this just too much for a newbie?

           Thanks!

        • 1. Re: Novice at FileMaker, need to create a recurring report and need suggestions!
          philmodjunk

               Much depends on the structure of tht spreadsheet. If it is logically laid out so that each row represents one call, a summary report based on this data should be very possible to do. But I'd suggest setting up  a database on a network so that people can log new calls directly into the database instead of into the current spread sheet.

               Here's a tutorial on summary reports that you may find useful: Creating Filemaker Pro summary reports--Tutorial

               Please post any questions you might have about that tutorial here, not in the original thread. I am very unlikely to see any comments that you post in it.

          • 2. Re: Novice at FileMaker, need to create a recurring report and need suggestions!
            StellaBear

                 Thank you Phil!  They already have an online system for logging calls and I used that system to generate the excel file.  There's no way they will move systems and the system cannot generate this kind of report easily.

                 I have played around with the summary report!  How do I modify it so that it will show:

                 AGENT    #CALLS

                 Amy   10

                 Bob  20

                 Candy  12

                 Instead of listing all 10 calls under Amy?

                  

                 Also I used the List function to return a list of the agent names but I don't want duplicate names.  So List(Amy, Amy, Bob, Candy) will return only Amy once.  What do I do to prevent repeats?

                  

                 Thanks so so much!

            • 3. Re: Novice at FileMaker, need to create a recurring report and need suggestions!
              philmodjunk

                   What you have requested is covered in the tutorial. You remove the body layout part from your layout and just have a sub summary part "when sorted by" your sales person. A summary field is then used to show the # of calls for each.

                   As to your list function, that function has no part in the summary report that we are describing here. How do you intend to use it?

                   Frankly, I'd set up a related table with one record for each sales person and then such a list of the values from that table will not have duplicates. ExecuteSQL can be used to produce this list and then the DISTINCT keyword can be used to omit the duplicates as well.