3 Replies Latest reply on Nov 4, 2011 10:30 AM by philmodjunk

    Dynamically Generated Summaries and Statistics

    lime_tree

      Title

      Dynamically Generated Summaries and Statistics

      Post

      Hello, I am quite new to File Maker and I have started working on a sales database, which requires several complex functions that I cannot seem to properly do, they are listed below, if someone would be able to help me solve these problems or give advice on the approach, I would greatly appreciate that.

      About the database:

      It has 4 sales reps that gather contact details from potental customers and move theese contacts through 8 stages, from introduction to our ptoduct to a closed sale. The main purpose of this database (aslde from gathering contact information) is to track performance of the 4 sales reps as the commision they get is based on the number of contacs they add to the database, and how many they manage to close.

      1) In the main layout of the database there is a section that needs to show various summary data on performance of each rep. This will be used as a motivational tool. Is this possible?

      2) If it is, the summary section will need to show the tree sections below. The first section of summary data is a list of 10 potential customers at the last stage of the sales process - the ones that are ready to close the sale.

      This list does not depend on the sales rep name, it depends on the stage number that the potential customer is on and the estimated closing date. The question here is: how do I show top 10 records independently of the rep name and how do I make sure that it is dynamically generated? I tried doing it via a portal, but it only shows one record every time.

      3) The next section needs to show a count of potential customers at different stages for each sales rep. Example below:

      Rep #1 - total number contacts in stage 1+2, 3+4, 5+6, 7+8.

      Rep #2 - total number contacts in stage 1+2, 3+4, 5+6, 7+8.

      And so on...

      In this case I do not know how to even start - do I need summary fields for each stage? If so, how do I make sure that only stages for customers under a particular sales rep are shown?

      4) The last section shows the average number of contacts that each sales adds on a: daily, weekly and monthly basis. Example below:

      Rep #1 - average number of contacts added per day; per week; per month.

      Rep #2 - average number of contacts added per day; per week; per month.

      And so on...

      Again, here I don't know how to start. I had an idea about counting the number of records created by each user, but I do not know how to separate them into daily/weekly/monthly groups and particularly how to make those numbers into an average for each sales rep, that does not change as one browses through the records in the database.

      I am not advanced in coding at all, but I understand the logic behind File Maker databases quite well. If someone could let me know how to start going about the above tasks, I will pick it up fast. I just do not know how to properly begin these so that the database does not get unnecessarily complicated. Is this too complex for a beginner user?

      Thank you very much for your help.

        • 1. Re: Dynamically Generated Summaries and Statistics
          philmodjunk

          Everything you describe can be done, but the structure of your database is crucial and is also likely why your portal didn't work as expected.

          What tables have you created in your database?

          How are they related to each other?

          I think you'll need at least two tables, it could easily need several more:

          SalesReps----<Contacts

          SalesReps::SalesRepID = Contacts::SalesRepID

          With this relationship, a portal to Contacts, placed on a SalesReps layout will list all Contacts for which that SalesRep is responsibile. That's just to get started here. Once you have that working, different relationships and sorted, filtered portals can be used with summary fields defined in the Contacts table to produce your statistics.

          Can you confirm this basic set up?

          • 2. Re: Dynamically Generated Summaries and Statistics
            lime_tree

            Thanks for replying so fast: I had this database started from a different one, which had th Contacts table as the parent with several other tables related to it. Following your advice, I created a Sales Reps table, have it related as a parent table to the Contacts one and have the fields you mentioned in both tables. I assume that the SalesRepID is a serial number. I also duplicated the Contacts Layout to be the same as the Sales Rep layout. Now I am facing a problem of the phrase "Index Missing" in all the fileds of that layout. It says that the relationship between tables is invalid. I wonder if it is because I created the SalesRep table afterwards and it has no data in it. I guess I should create some fields for the Sales Rep table other than their serial number, but again, I am not sure what they should be in order to not cause this problem.

            • 3. Re: Dynamically Generated Summaries and Statistics
              philmodjunk

              I also duplicated the Contacts Layout to be the same as the Sales Rep layout.

              Did you use Duplicate Layout for this or copy all layout objects from the contacts layout to the Sales Rep layout?

              Did you create the relationship between these two tables as I specified?

              SalesRepID should be a number field in both tables. In the SalesRep table, you are correct that it should be defined as an auto-entered serial number field. Both of these fields should have indexing turned on in field options on the storage tab. (That should be the default setting for these unless you changed something.)

              To link a contact to a sales rep, you enter the ID number from that salesRep's record into the contacts::SalesRepID field. This is easiest to set up if you create a value list of SalesRepID's in field 1 and their name specified for Field 2. The user selects a Sales Rep by name, but the drop down list or pop up menu actually enters and stores the ID number.