1 2 Previous Next 21 Replies Latest reply on Jun 11, 2009 12:29 PM by comment_1

    Report limited to date field

    truckerclock

      Title

      Report limited to date field

      Post

      I have a call reports database where salespeople enter sales contacts, comments, call types and call date. I want to create a report that will show me the contacts that have not been contacted in the last x number of days. I have created a report that has all of the fields I want and currently displays all of the data in the table. How can I specify to show only the records that the date field is x number of days before today? Thanks in advance for all of your help!

        • 1. Re: Report limited to date field
          truckerclock
            

          I found the script below in another post that looks like it will do what i want it to do. In the script editor I do not have an option to insert "Let" or Get Current Date. Is there not a way to copy and paste this script?

           

          #
          Set Error Capture [ On ]
          Enter Find Mode [] // Deselect Pause
          Set Field [ TABLE::ContactDate ;
           

          Let (
          Current = Get ( CurrentDate ) ;

          " ≤ " & Date ( Month ( Current ) - 6 ; Day  ( Current )  ; Year  ( Current ) )

          )
           

           ]
          Perform Find []
          #
          If [ Get ( FoundCount ) = 0 ]
          Show Custon Dialog [ "No record found" ]
          End If
          #



          • 2. Re: Report limited to date field
            Orlando
              

            Hi truckerclock,

             

            The part of the script:

             

            Let (
            Current = Get ( CurrentDate ) ;
            " ≤ " & Date ( Month ( Current ) - 6 ; Day  ( Current )  ; Year  ( Current ) )
            )

             

            is the calculated value, to input this you need to specify a Set Field step into your script, and then click on 'Specify...', bottom right, for 'Specify target field' and choose the date field you want to input the date into, and then click on the 'Specify...' below this for the 'Calculated result'. When the Calculation dialog pop's up paste the calculation above and click OK.

             

            This should do the trick, if you have any more trouble with this then let me know. 


            • 3. Re: Report limited to date field
              truckerclock
                

              When i insert the IF statement

               

              [Get ( FoundCount ) = 0 ]
              Show Custon Dialog [ "No record found"]

               

              I get an error saying a number, text constant or field name is expected. I am assumign it has something to do with the (FoundCount) becayse it is not defined anywhere?

               

               

              • 4. Re: Report limited to date field
                comment_1
                   Which version are you using?
                • 5. Re: Report limited to date field
                  Orlando
                    

                  Hi truckerclock, are you including the square brackets '[]' in the calculation? If so remove them so the calculation you input into the If script step is only 

                   

                  Get ( FoundCount ) = 0

                   

                  Let us know how you get on with this. 

                  • 6. Re: Report limited to date field
                    truckerclock
                       I am using Version 10. Orlando, I changed what you said and it allowed me to save the script and it runs without errors but does not return any records and I know it should. How can I copy the script into an email so you can see what I have, because I can't seem to figure that out?
                    • 7. Re: Report limited to date field
                      Orlando
                        

                      If you print to PDF the script you can then copy the text from the script and paste it into your message. 

                      • 8. Re: Report limited to date field
                        truckerclock
                          

                        Ok the script is below. When I  entered the [ Get ( FoundCount ) = 0 ] into the specify box I did not add the brackets, but it looks like it put them back in automatically? The Date field in the Call Reports table is the field I want searched and I have a CurrentDate field to hold the value generated from the Get CurrentDate part of the script. 

                         

                         

                        #

                        Set Error Capture [ On ]

                        Enter Find Mode [ ]

                        Set Field [ Call Reports::Date; Let (

                        Current = Get ( CurrentDate ) ;

                        " = " & Date ( Month ( Current ) - 14 ; Day ( Current ) ; Year ( Current ) )

                        ) ]

                        Perform Find [ ]

                        #

                        If [ Get ( FoundCount ) = 0 ]

                        End If

                        • 9. Re: Report limited to date field
                          Orlando
                            

                          OK the issue may be the " = ", this will mean you are searching for a specific date, you wanted to find all dates before 14 months ago, so change the "=" to "<=" [greater than or equals to] and this should do the trick.

                           

                          Also between the If [ Get ( FoundCount ) = 0 ] and End if you can put a Show Custom Dialog, to alert you if no records have been found. So it woudl read:

                           

                          If [ Get ( FoundCount ) = 0 ]
                          Show Custom Dialog [ Message: "No records have been found" ] 
                          End If 

                           

                          If you don't want a message then you can get rid of those lines altogether. 

                           

                          Let us know how you get on. 

                          • 10. Re: Report limited to date field
                            truckerclock
                               Ok that change returned some records. I also want it to return records when the field is null and I dont see an "or is null" option. Do I have to add another set field line to accomplish this?
                            • 11. Re: Report limited to date field
                              Orlando
                                

                              Yes you do, along with a New Record/Request step, and your Set Field step will simply have an equals in brackets as the calculated value.

                               

                              So you will have:

                               

                              Enter Find Mode [ ]
                              Set Field [ Call Reports:: Date ; Let ( Current = Get ( CurrentDate ) ; " = " & Date ( Month ( Current ) - 14 ; Day ( Current ) ; Year ( Current ) ) ) ]
                              New Record/Request
                              Set Field [ Call Reports:: Date ; "=" ]
                              Perform Find [] 

                               

                              • 12. Re: Report limited to date field
                                truckerclock
                                   When I try to specify the additional specified field line with the Date field and = symbol I get an error saying a number, text constant or field name is expected.
                                • 13. Re: Report limited to date field
                                  Orlando
                                    

                                  Sorry, I wrote brackets instead of quotations in my original post.

                                   

                                  When you click on 'Specify...' to input the calulcation for the Set Field step you need to put the equals symbol in quotation marks, so the calculation will simply be as follows

                                   

                                  "="

                                   

                                  This should do it. 

                                  • 14. Re: Report limited to date field
                                    truckerclock
                                      

                                    Ok when I changed that it let me save it and it runs without errors. It is still just finding the one record that the date is older than specified but the majority of the 900 records do not have anything in the Date field and it is not returning those. The updated script is below.

                                     

                                    # Set Error Capture [ On ] Enter Find Mode [ ] Set Field [ Call Reports::Date; Let ( Current = Get ( CurrentDate ) ; "<=" & Date ( Month ( Current ) ; Day ( Current ) -14; Year ( Current ) ) ) ] New Record/Request Set Field [ Call Reports::Date; "=" ] Perform Find [ ] If [ Get ( FoundCount ) = 0 ] Show Custom Dialog [ Message: "No Records Were Found" End If #

                                     

                                     
                                    1 2 Previous Next