9 Replies Latest reply on May 20, 2014 1:35 PM by philmodjunk

    Issue with Find Date when date feild is a calculation

    NonProfitEducationUser

      Title

      Issue with Find Date when date feild is a calculation

      Post

           Hi~

            

           I have a StartDate field and an EndDate field which are both calculation feilds with the result set as type date. (These two fields are grabbing the min date and the max date from a portal of related records of group session meetings; both of those are also set as type date).

            

           Now I'm trying to set up a script that would find all the EndDates within a particular range (for example; how many groups had end dates in October 2013). I have done enough troubleshooting that I know the issue is within the "Find date" step. Even when I am on the master list of programs, and do a basic find for end dates on a particular day, or in a range, it tells me there are no records that match my criteria; or gives me total wrong results.

           I have been reading through some of the other posts, and I'm afraid this means that a lot of my data is corrupt. (I was reading one suggestion about turning off indexing, but in the StartDate and EndDate fields, I can't uncheck the box under "indexing". I have also checked all the related feild types to make sure they are date types and the calculation results are date types).

           I wanted to see if there were any more suggestions about what I may be doing before I look at how to recover all the corrupt data. (If it is a corrupt file, where can I find instructions on how to deal with that?)

            

           Thanks in advance for your help,

            

            

        • 1. Re: Issue with Find Date when date feild is a calculation
          philmodjunk

               What you have is an unstored unindexed calculation field. This has to be this way when a calculation refers to data in a related record such as your calculation grabbing the min date and the max date from a portal of related records of group session meetings.

               But if your calculations specify a result type of date, this should work for your find even though the find will be performed more slowly due to the need to specify find criteria in an unindexed field.

               Just to make sure, I ran the following test:

               I defined two tables, Parent, Child, linked in this relationship: Parent::__pkParentID = Child::_fkParentID

               I defined a date field in Child and the field, cMaxDate in Parent: Max ( child::Date ) with date selected as the result type.

               I then put a portal to Child on the parent layout and created several related records with different dates for days in the month of May, 2014 in the portal.

               When I enter find mode and specify this criteria in Parent::cMaxDate: 5/1/2014...5/31/2014, It finds the record in my table as it should. If I return to find mode and specify 5/2014, it also finds the record.

          • 2. Re: Issue with Find Date when date feild is a calculation
            NonProfitEducationUser

                 OK, so I checked to make sure I was referring to the correct table occurance and triple checked that everything ends up in date form.

                 Do you have any other suggestions? All the other finds work just fine, so I'm guessing there is something corrupt in some date field somewhere. Do you have any suggestions to how I could find out the issue?

                 Thanks (again!)

            • 3. Re: Issue with Find Date when date feild is a calculation
              philmodjunk

                   I'd double check that the original date fields are of type date as well as checking the calculation fields to make sure that they are really of type date first.

                   If the all specify date, then I'd first try a recover on the file to see if the recover fixes the issue and to see if it reports any problems found and fixed. Test the recovered copy even if no problems are found as this copy has rebuilt indexes. (the calcs aren't indexed, but the original date fields are indexed)

                   PS. did you check this by entering find mode and entering the date range manually? That checks whether your script may have an issue instead of the calculation field.

              • 4. Re: Issue with Find Date when date feild is a calculation
                NonProfitEducationUser

                     I have been running the "finds" from multiple different layouts (not running within the script). I've even tried different layouts and tried deleting the fields and then putting them back to see if that would help. No luck so far.

                      I wanted to ask a few more questions before I run the recovery because I have to get our IT person (who's not onsite) to run the recovery from our app server (It tells me that it can't do the recovery because the program is open).

                     I'm thinking maybe it's somewhere within the date formatting possibly with specific users. Because two things are happening: the issue seems to be primary with two people who are entering the data- their entries never show up in any finds (when I run a find for just 2013, I get most of the results, but none from 2 out of the 5 users). The next weird thing is if I run a find 10/*/2013, it returns finds with the date 12/10/2013; so I tried */10/2013 thinking that maybe it was a mm/dd vs dd/mm issue, but I get the SAME find results (for the event on 12/10/2013). I went back and made sure in the inspector that all the fields were dates formatted as mm/dd/yyyy. And made sure my computer was set that way- could it be something with the computer they are inputting with doesn't default to that? (I'm grabbing at anything right now).

                     Two other things, that I don't think should make a difference, but just incase:

                     Do I want the original date feild indexed? (under the indexing field do I want "none" and "index as needed" checked; or "all"). I have tried running finds with a variety of these boxes checked or unchecked with seemingly no difference.

                     Do I want the min and max date calculations to run if referenced fields are empty?

                     Thanks for helping me troubleshoot.

                      

                • 5. Re: Issue with Find Date when date feild is a calculation
                  philmodjunk

                        I wanted to ask a few more questions before I run the recovery because I have to get our IT person

                       If this is an actively hosted file, I recommend running a recover on a recent back up copy so that this does not interrupt use of the hosted file.

                       

                            The next weird thing is if I run a find 10/*/2013, it returns finds with the date 12/10/2013

                       Are you performing that find on the original date field or the calculation field using the Max function?

                       I think you have an issue with the date field referenced in your calculation--perhaps it was a text field later converted to be of type date? (I've seen some similar issues here in the forum when that was the case.)

                       You may need to define a completely new date field and use Replace field contents to copy the dates from the original field into the new field, then replace all references to the original field with references to the new fields so that you can delete the original date field.

                  • 6. Re: Issue with Find Date when date feild is a calculation
                    NonProfitEducationUser

                         I just wrote this and it errored when I submitted, so I apologize in advance if I forget something when re-writing it.

                         I'm still having this same issue. It IS with the calculation field and not the original fields. I made a copy of the database and ran a recovery, and it didn't fix anything. I deleted and recreated the calcuation, didn't help either. I made the new reference feild as you suggested, and got the same results. But it's being very strange. Here are some searches I have run (on the calculation field) and their results (we have only been using this program since the beginning of our fiscal year in October).

                           
                    •           10/*/* = all programs in October (correct results)
                    •      
                    •           2/*/* = February programs (correct results)
                    •      
                    •           2/*/2014 = Programs on March 2nd. (4/2/2014)
                    •      
                    •           10/*/2013 = a program on Dec 10th. (12/10/13)
                    •      
                    •           11/*/2013 = programs on Dec 11th. (12/11/2013)
                    •      
                    •           22/*/2013 = says invalid entry (correct result)
                    •      
                    •           2013 or */*/2013 = programs up to the 12th of the month. So no programs on any day past the 12th of the month.
                    •      
                    •           */22/2013 = programs on the 22nd of any month in 2013 (correct results)
                    •      
                    •           */4/2013 = programs on the 4th (correct results)

                         I'm so confused and the only pattern I'm seeing is when I want to use the month and the year. But I have to be able to use the date fields to run any of my reports since they are all based on the date of the program. Why do some combinations work and others not work? Any suggestions?

                         I could make my teachers/users re-enter the start and end dates for each program in a non-calculation field, but I just don't think they should have to enter the same information twice if I can help it.

                         Thanks for the help!

                          

                          

                          

                    • 7. Re: Issue with Find Date when date feild is a calculation
                      philmodjunk

                           It looks like your file has issues due to changed locality settings for your computer and/or your database file.

                           Outside the US, most places record dates as DD/MM/YYYY. we do it differently in the US by recording the data as MM/DD/YYYY.

                           By any chance, are you using data formatting in the inspector to change how the date is displayed?

                           If you click or tab into a field, that was showing a date such as 10/12/2014, does it change to 12/10/2014?

                      • 8. Re: Issue with Find Date when date feild is a calculation
                        NonProfitEducationUser

                             That's what I was thinking too. But I can't tell what is in the wrong format. From what I can tell, no, none of the dates change when I click in the date fields (original or calculation fields). I DID go through and make sure all of them were set to MM/DD/YYYY in the inspector. Should I not do that? And I made my sure my computer itself was set properly. I thought that it might have to do with who was entering the information, but it appears that the same issue occurs with all of the users.

                              

                              

                        • 9. Re: Issue with Find Date when date feild is a calculation
                          philmodjunk

                               I suggest reporting this in Report an Issue. You can save typing by posting a link to this thread in your Issue Report.

                               What you report with your find criteria is inconsistent with how the file works.

                               

                                    I just wrote this and it errored when I submitted,

                               Due to a noxious forum bug, please protect yourself with a "Select-All, copy to your clipboard" action just before submitting a private message or comment to this forum. The bug can lose your comment and log you out of the forum--forcing you to sign back in and re-enter the comment or message. By copying to the clipboard before posting, you can re-enter your message by pasting from the clipboard instead of having to retype it all over again.