3 Replies Latest reply on Aug 6, 2013 9:08 AM by philmodjunk

    Value count and date ranges



      Value count and date ranges


           Hey everyone,


           I am trying to set up a database for a small business and I am trying search how many business introductions each employee gets. 


           I am able to do this with this calculation

           ValueCount( FilterValues( List( business numbers::Employee Name); (Employee::Name)))


           However, I need to be able to sort this by date so I can see week,month, quarter, or whatever I want. 


           Right now the database is simple two tables employee table and business numbers table. The only relationship is business numbers::Employee Name and Employee::Name. 


           Thanks for any and all help

        • 1. Re: Value count and date ranges

               Well, you don't have a list here, you just have a count of the number of members in a list and the list is only a list of employee names.

               I can read your request one of two ways:

               You want to see a report that lists one or more employees and breaks down their numbers by week, month and/or quarter

               You want to select an option and see just the count for that one employee for that one specified time period.

               The first is possible with a summary report layout based on business numbers. The second requires either a filtered relationship, a filtered portal or ExecuteSQL (FIleMaker 12 or newer only).

               Filtered portal:

               Define a summary field in business hours that counts some record in the table that is never empty. Employee Name can work for this. Place the summary field inside a one row portal and define a portal filter expression that filters by a range of dates and the employee name (Employee ID number would be better).

               Filtered Relationship:

               You'd need a relationship that matches by employee and by a range of dates:

               Option 1:

               Employee::__pkEmployeeID = Business Numbers::_fkEmployeeID AND
               Employee::gDate1 < Business Numbers::DateFIeld AND
               Employee::gDate2 >Business Numbers::DateField

               gDate1 and gDate2 would be a pair of date fields specifying the first and last dates for your week month, or quarter. They often are best defined as global fields, but this is not necessary to get it to work.

               Option 2:

               Employee::__pkEmployeeID = Business Numbers::_fkEmployeeID AND
               Employee::gDateList = Business Numbers::DateFIeld

               gDateLIst would be a text field listing all the dates in the desired interval separated by returns. It takes a script or custom function to load the field with all the dates from a specified first and last date like you have in Option 1, but with large numbers of records, it can evaluate more quickly than a relationship with inequality operators such as those found in Option 1.

               WIth either relationship, Count ( Business Numbers::Employee Name ) or the summary field defined in Business Numbers that I described in the filtered portal suggestion can be used to get your count.

               And a final option would be to use ExecuteSQL to extract this same total from Business Numbers if you care to use and SQL query.

          • 2. Re: Value count and date ranges

                 Thanks for the reply. 


                 So I attempted to set up option 2


                 I want to display one employee and their week, month, quarter and year intro numbers on one record. It appears because of the relationship I am setting up I can only do one of these at a time unless I duplicate the business table over and over again.


                 Also note that the business table is an auto import and cannot be changed much

            • 3. Re: Value count and date ranges

                   Unless you duplicate the Tutorial: What are Table Occurrences? for the business numbers table, you cannot use option 2 the way that you want. You can either use the filtered portal approach and set up each one row portal with a different portal filter expression, use a set of calculation fields with ExecuteSQL(), or you can duplicate the table occurrence as many times as you need for each set of relationships and then option 2 becomes possible.

                   Please note that duplicating a table occurrence does not physically duplicate the actual table. It just sets up a new Box in Manage | Database | Relationships that refers to the same table under a new name.