10 Replies Latest reply on Nov 26, 2008 7:27 AM by netguykb

    Displaying record counts (Part One Question)...

    netguykb

      Title

      Displaying record counts (Part One Question)...

      Post

      I have a CRM type database that allows users to create accounts, and assign themselves activities. I want to be able to do the following:

       

      1) Display record count of all activities that the current logged on user has to complete for Today (= Current Date)

      2) Diplay record count of all activities that the current logged on user has to complete for the Future ( > Current Date)

       

      If no records are found then display 0 (IF Statement)

       

      Not sure if I need to create a dynamic table to capture this information and then display it or can I add a field in one of the current tables to calculate this information. I beleive the first option is what I need to do considering their are relations ships built into the primary account table and the activitity table.

       

      Any help would help. Thank you!

        • 1. Re: Displaying record counts (Part One Question)...
          obeechi
            

          Let there two more occurrences of the activities table. Name them so you know which will be for Future and which will be for Today. Create relationships based on primary key from the parent table (whatever you are calling it) to the newer activities tables. Then add a predicate to each of these two newer relations so they are both multi-predicate relations. The additional predicate will be a relation of date to date and the relational operator will be choosen from < > ≤ ≥ = depending on the timeframe you're trying to view via the relationship. 

           

          Then you can use Count () where the parameter counts some field in the related table of your choosing.

          • 2. Re: Displaying record counts (Part One Question)...
            netguykb
               So your advising that I create another table exactly like the master activity table or simply a subset of fields in the second table in order to join the two together. Also what your proposing does not answer the question of only displaying those records for user logged in. I only want to see my activities. In the master activity table I have a field named assigned to. I thought I could either match that name to the current user logged in name to only display those that I am assigned to
            • 3. Re: Displaying record counts (Part One Question)...
              TSGal

              netguykb:

               

              Thank you for your post.

               

              The function Get (AccountName) returns the account name entered.  You can use this function in a startup script to find only those records that belong to the current user.  The script could look like:

               

              Enter Find Mode []

              Set Field [Assigned To; Get (AccountName) ]

              Perform Find []

               

              This should get you pointed in the right direction.  If you have any questions or need clarification, please let me know.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Displaying record counts (Part One Question)...
                netguykb
                  

                I'm just not getting all of this. I am used to using SQL server and It seams like I should understand this, however I would like to start over. I am trying to populate the my activities block (as seen below) based on the user loged in. I have also included a snap shot of those tables that I am working with. The question is: I need a script that will tell the current user logged in

                 

                1)the total number of activitites for the current date based on the following criteria. WHERE Activities::activity_date_due = Get Current Date AND (Activities::activity_outcome = "FOLLOWUP EMAIL" OR "FOLLOWUP CALL" ) AND Activities::activity_assigned_to = Current User

                 

                2) I would also like the script to inform the user the total number of future activities. The criteria is slighly different.  Activities::activity_date_due > than Current Date AND (Activities::activity_outcome = "FOLLOWUP EMAIL" OR "FOLLOWUP CALL" ) AND Activities::activity_assigned_to = Current User

                 

                3) Lastly the the script should inform the user the total number of overdue activities. The criteria is slighly different.  Activities::activity_date_due < than Current Date AND (Activities::activity_outcome = "FOLLOWUP EMAIL" OR "FOLLOWUP CALL" ) AND Activities::activity_assigned_to = Current User. Once the activity has been completed the user will always change to somthing other than "FOLLOWUP EMAIL" OR "FOLLOWUP CALL"

                 

                My Activities Screen

                 

                ERD 

                 

                 

                • 5. Re: Displaying record counts (Part One Question)...
                  TSGal

                  netguykb:

                   

                  Thanks for the clarification.

                   

                  Use the Get (AccountName) to display the login value.  If you want to display a name associated with the login value, then you will need to create an additional table that contains the AccountName and the Name.  You can then reference the Name through the AccountName.

                   

                  The function Get (CurrentDate) will return today's date.

                   

                  1) This can be created with a script:

                   

                  Enter Find Mode []

                  Set Field [Activities::activity_date_due; Get (CurrentDate) ]

                  Set Field [Activities::activity_assigned_to; Get (AccountName) ]

                  Set Field [Activities::activity_outcome; "FOLLOWUP EMAIL" ]

                  Duplicate Record/Request

                  Set Field [Activities::activity_outcome; "FOLLOWUP CALL" ]

                  Perform Find []

                   

                  This enters Find Mode.  We set the fields to look for today's date in activity_date_due, the current user login in activity_assigned_to, AND "FOLLOWUP EMAIL" in activity_outcome.  Since each find request is an OR possibility, we duplicate the request and replace the activity_outcome with "FOLLOWUP CALL".  Now that we have the find criteria in place, we perform the Find.  You can put in error capture routines just in case there are no records found.

                   

                  2) For future activities, the script would be similar...

                   

                  Enter Find Mode []

                  Set Field [Activities::activity_date_due; ">" & Get (CurrentDate) ]

                  Set Field [Activities::activity_assigned_to; Get (AccountName) ]

                  Set Field [Activities::activity_outcome; "FOLLOWUP EMAIL" ]

                  Duplicate Record/Request

                  Set Field [Activities::activity_outcome; "FOLLOWUP CALL" ]

                  Perform Find []

                   

                  3) For overdue activities, the script would also be similar.

                   

                  Enter Find Mode []

                  Set Field [Activities::activity_date_due; "<" & Get (CurrentDate) ]

                  Set Field [Activities::activity_assigned_to; Get (AccountName) ]

                  Set Field [Activities::activity_outcome; "FOLLOWUP EMAIL" ]

                  Duplicate Record/Request

                  Set Field [Activities::activity_outcome; "FOLLOWUP CALL" ]

                  Perform Find []

                   

                   

                  Let me know if you need further explanation.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Displaying record counts (Part One Question)...
                    netguykb
                       Thanks I actually figured out how to do this as you sent me the e-mail. Let me ask you, how can I now get record counts displayed for the trhee categories regadless if a user performs the scripts after pushing a button. Do you know what  mean? The image I poset in my last post indicated 0's fo each I want to count the records and display the number for each record found based on the 3 scripts when the user enters the page
                    • 7. Re: Displaying record counts (Part One Question)...
                      TSGal

                      netguykb:

                       

                      I was so focused on the find criteria that I forgot to answer the question about the found records.

                       

                      After the records are found, there is a function Get (FoundCount) that returns the number of records found.  If there are zero records found, you can store that information into a variable and place it into a global field or field from another table.  So, at the end of each script, you can put the Get (FoundCount) into the variables $today, $future and $overdue.  Please look at the "Set Variable" script step command for more information.

                       

                      Using the first script, and assuming you have a table that displays the users name along with the Login name:

                       

                      Enter Find Mode []

                      Set Field [Activities::activity_date_due; Get (CurrentDate) ]

                      Set Field [Activities::activity_assigned_to; Get (AccountName) ]

                      Set Field [Activities::activity_outcome; "FOLLOWUP EMAIL" ]

                      Duplicate Record/Request

                      Set Field [Activities::activity_outcome; "FOLLOWUP CALL" ]

                      Perform Find []

                      Set Variable [$today; Value: Get (FoundCount) ] 

                      Set Field [Login::TodayNum; $today ]

                       

                      Later, you can reference the TodayNum field (along with a PastDue and Future fields).

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: Displaying record counts (Part One Question)...
                        netguykb
                           Ok so I have created 3 scripts to handle Current, Future, and Pastdue activities. The initial concept was to execute these scripts by creating buttons for users to click on. This means the only way to execute the scripts was to click on the button. However directly next to each of the buttons I wanted to have already pre-populated the total number of found records according to each script, next to each button as if the scripts have already been run. I believe the only way to do this would be to include the same scripts, in addition to your suggestion with the creating global variables to display records counts, in my open script which executes when the DB file is opened. Is this what you would suggest? This means that if I were to change any of the conditional logic I would have to now update many scripts in order to get the results I need. Doing this seems like I am adding more overhead on the DB, more work and the likelihood for error's, should my conditional logic change. Any thoughts?
                        • 9. Re: Displaying record counts (Part One Question)...
                          TSGal

                          netguykb:

                           

                          Most of your assumptions are correct.

                           

                          If you ever change the conditional logic, then naturally, you would have to change the script, just as you would in an SQL statement.  Once you change the condition, it is saved.

                           

                          One of the options when opening a file is to execute a script.  Therefore, you can have a new script which calls the other three scripts to get you the numbers you want displayed.    Your new script would look like:

                           

                          Perform Script [Today]

                          Perform Script [Past Due]

                          Perform Script [Future] 

                           

                          To execute a script at startup, pull down the File menu and select "File Options..."  Under "When opening this file", check the option to "Perform script".  Then select the newly created script which calls the three other scripts.

                           

                          TSGal

                          FileMaker, Inc.

                          • 10. Re: Displaying record counts (Part One Question)...
                            netguykb
                               I understand, and I'm glad I was somewhat close to what my origianal assumptions were. Thanks again for your help. I may have a few questions for you in the future.