5 Replies Latest reply on May 3, 2011 3:18 PM by philmodjunk

    Date Range search and post count

    HiroshiTakeuchi

      Title

      Date Range search and post count

      Post

      Hello: 

      I am new to FileMaker and DB in general... I am trying to get the following data to show in the following way... 

      I have a field where I have a History of dates and times: (example)

      5/3/2011-9:21:59 AM

      4/18/2011-9:26:31 AM

      3/28/2011-9:10 AM

      3/13/2011-10:54:02 AM

      3/15/2011-6:29:11 AM

      3/5/2011- 6:21:09 PM

      3/5/2011-6:13:31 PM

      2/7/11

      2/3/11

      What I want is to have a different field (example: MoCntMar) which should count the lines that have fall within the month of March, and then display that count in this field. (in this example would be 5)

      I have tried a few things such as "IF" command but have not been able to get it to work... 

      Any help will be really appreciated!

      Hiroshi Takeuchi

        • 1. Re: Date Range search and post count
          philmodjunk

          Is this a field of type text or of type TimeStamp?

          • 2. Re: Date Range search and post count
            HiroshiTakeuchi

            The History field is an "Edit Box" in which I am adding the date stamps manually

            • 3. Re: Date Range search and post count
              philmodjunk

              An "edit box" is layout format designation. What I am asking is what data type was assigned when you defined the field. Open Manage | Database | Fields and check to see if this field is of type text or type timestamp.

              I suspect that this is a field of type text--which will complicate the issue.

              Are you making one entry per field or are you making multiple entries in the same field?

              If you are making multiple entries into one field, this will be one of the first things we'll need to change here.

              • 4. Re: Date Range search and post count
                HiroshiTakeuchi

                Thank you four your quick response... I have it as "Text". What this section of the database is doing is keeping track of how many times certain asset is being accessed and modified, keeping track of the date and time in the history section. The History section has multiple entries in the same field, since I could not find a better way to populate this information.  What would you recommend to do in order to make this happen? you mention changing the filed to a single entry instead of multiple entries in one, but how can I keep a history then?

                Thanks

                Hiroshi Takeuchi

                • 5. Re: Date Range search and post count
                  philmodjunk

                  You use a table of related records where each record logs a different change in your change history. You can then display all the related records in a portal to show the change history for that item.

                  This would allow you to count all change recrords that fall in a given date range very easily and through a number of different methods--for all change records or just for a given item's change history.

                  As a text field with multiple entries, you'd have to extract all the date entries into seperate records and convert them from date to text to do this anyway. This will be complicated by the fact that your text field allows nonstandard or even incorrect text to be entered as a date.