11 Replies Latest reply on Mar 5, 2013 10:49 AM by philmodjunk

    How To sort records By Dates

    user14040

      Title

      How To sort records By Dates

      Post

           I am trying to figure out how do I create a report that will should me only certain dates.

           what I need is for the records to be sorted from 6/30/2012 to 7/1/2013 and do it for the next fiscal year 6/30/2013 to 7/1/2014 (when data is added) but I cant figure out how, i know it probably something simple and I am just over looking it.

           Thanks

        • 1. Re: How To sort records By Dates
          philmodjunk

               You need a calculation that identifies the fiscal year. Let's assume your date field is called "Date". cFiscalYear might then be defined as:

               Year ( Date ) - Month ( Date ) < 6

               This will return a "fiscal year" of 2012 for the first date range and 2013 for the second date range specified in your posts.

               You can then perform finds and sorts using cFsicalYear to find and group records by fiscal year.

          • 2. Re: How To sort records By Dates
            user14040

                 Thank you very much,

                 I created a field called cFiscalYear with the following calcualtion Year ( Date ) - Month ( Date ) < 6 But I get a question mark in the field.

                 do I set cFiscalYear  Calculated result as a Date or Number?

                 When doing a find or sort do I use the Date Field and the cFiscalYear? 

                 The data I am currently working with is from January 2012 to December 2012

                 I have another file that has January 2013 to present. I just found out that the data needed is fiscal year 6/30/2012 to 7/1/2013 so I will have to import the 2013 data into the 2012 file to have all data in one file.

            • 3. Re: How To sort records By Dates
              philmodjunk

                   The result type should be number.

                   And you definitely should keep all of this data in the same table of the same file if there is the slightest chance that you will need reports spanning multiple fiscal years.

                   When you sort, you can sort by cFiscalYear, then by date or even just by cFiscalYear. It depends on the results that you want. By sorting by cFiscalYear, you can add a sub summary layout part to your layout and summary fields placed in that layout part will compute totals for the fiscal year.

                   And you can put search criteria in cFiscalyear for finds if you want, but you can also put date ranges in the date field to get the same results.

                   Example:

                   in find mode:

                   Putting 2012 in cFiscalYear will be the same as putting 6/30/2012...7/1/2013 in the date field. to find all records in cFiscalYear 2012.

              • 4. Re: How To sort records By Dates
                user14040

                     Using Find and putting 2012 in the cFiscalYear give error no record match.  cFiscalYear has a default zero in the field.

                     I imported all of 2013 records so now I have an up to date file from January 2012 to March 2013

                     When sorting it shows all records

                      

                     I created a script that sorts;

                     Ascending order: Investigator
                     Ascending order: cFiscalYear
                     Descending order: Date

                      

                • 5. Re: How To sort records By Dates
                  philmodjunk

                       cFiscalYear has a default zero in the field.

                       That shouldn't be the case. Is this a field of type calculation or a number field with an auto-entered calculation? It should be a field of type calculation.

                       is date a real date field or did you enter your dates into a field of type text?

                  • 6. Re: How To sort records By Dates
                    user14040

                         The date is auto entered when a new record is added.

                    • 7. Re: How To sort records By Dates
                      user14040

                           here is image 1

                      • 8. Re: How To sort records By Dates
                        user14040

                             image 2

                        • 9. Re: How To sort records By Dates
                          philmodjunk

                               To repeat from my last post. cFiscalYear should be defined as a calculation field, not as a number field with an auto-entered calculation.

                               When you add a field with an auto-entered calculation field, or change the definition on a field to add or change an auto-entered calculation, the value in the field does not update automatically for existing records in your table. Using a field of type calculation avoids these issues.

                               It is, however, possible to force an update on auto-entered calculation fields when adding or changing the defined calculation: Updating values in auto-enter calc fields without using Replace Field Contents

                          • 10. Re: How To sort records By Dates
                            user14040

                                 Yes I had it as a calculated field but it had zero in the field so I changed to number field to see if I did it wrong.  I will read through the link you provided.  Thanks

                            • 11. Re: How To sort records By Dates
                              philmodjunk

                                   The calculation field should work and should not return a zero. The link provided shows how to get auot-entered calculations to update existing records when you install a change to the expression used. It will not resolve the issue as to why you are getting a zero result. That result indicates a problem either with your data, how you defined the calculation or in the data type selected for your date field.