1 Reply Latest reply on Dec 8, 2008 12:55 PM by dbrown

    Creating Layout for displaying Activity Status

    netguykb

      Title

      Creating Layout for displaying Activity Status

      Post

      I would like to create an activity report based on specific criteria. Here is the layout page I‘ve created to display this information.



      In general I have a table (Activities) that captures various types of activities, dates they are created, assigned to and follow-up/outcome types. Here is a screen capture of the user interface used for capturing activity.



      When the report gets run the total numbers for each row and associated columns should be calculated dynamically like in the following example. Only records for the current Get (AccountName) should be displayed by default otherwise the user does have the option of selecting from a Users Drop Down menu. Users can also filter on years. So if 2007 is selected from the dropdown then only 2007 total number should be displayed for each of the columns and only for the selected user or default user.

       



      The numbers that are displayed must also take in consideration the current date
      For Example:
       
      4 wks (Last 30 days) must calculate from the current date and only 4 weeks (Last 30 Days) previous regardless of the year. IE January 9 would calculate 1/9/09 equal to or less than only 4 weeks previous, 12/10/08. (Including weekends)

      Q1 should only look at the current year months from January – March

      Q2 should only look at the current year months from April – June, unless its 2009 and we haven’t reached this quarter, then display/calculate 0 (ZERO)

      Q3 should only look at the current year months from July – September, unless its 2009 and we haven’t reached this quarter, then display/calculate 0 (ZERO)

      Q4 should only look at the current year months from October – December, unless its 2009 and we haven’t reached this quarter, then display/calculate 0 (ZERO)

      Total YTD should sum all current quarters. I have always been confused as to when to I should use calculations for field types versus creating scripts. Also is it possible to load and run specific scripts depending on what layout is opened, in other words associate open scripts based on layouts?

       

      Here is the current DB layout with the 3 tables to potentialy be used:

       

       











        • 1. Re: Creating Layout for displaying Activity Status
          dbrown
            

          In FMPro 9 (and probably some prior versions), you can define relationships using more than one test for matching records. In particular, you can define a relationship (MyQ1data) that selects Q1 data by specifying that some Date field must be >= 1/1/2008 and <= 3/31/2008. Then calculate based on the MyQ1data relationship. There are some details to work out, but this should give you a starting point.

           

          HTH,

          -Denny