10 Replies Latest reply on May 26, 2011 7:42 PM by TraceyLillis

    Report on records going to expire within next two months, sorted

    TraceyLillis

      Title

      Report on records going to expire within next two months, sorted

      Post

      I am trying to get a report to run on records that are going to expire within the next two months.  Currently my table has a field for date of certficate completion (e.g. First Aid) and the calculated field First Aid Expiry (which adds 3 years). I also have another 3 or 4 fields (other certificate expiry data) that also expire, and I woud like it all to show on one report.  Is this possible?

      Can someone please help guide me through the process of creating a report (I am used to MS Access here, however I have watched a few tutorials on creating reports (or report layout) in FM).  I am not confident in scripting at all, nor formula writing, so any help in 'baby' steps would be greatly appreciated.

      TIA

      Tracey

        • 1. Re: Report on records going to expire within next two months, sorted
          philmodjunk

          I also have another 3 or 4 fields (other certificate expiry data) that also expire, and I woud like it all to show on one report.

          Please describe your fields and the report you want in more detail. This sentence suggests the possibility that you may need a related table in order to get the report you want.

          To find all records where a specific date field will "expire" in the next two months, I'll assume you want find any records where the date expires in the next 60 days.

          In a script, you could find such records like this:

          Go to layout [//select layout based on records where date field is defined]
          Enter Find Mode [] // clear check box
          Set Field [YourTable::ExpirationDateField ; "< " & Get (CurrentDate) + 60]
          Set Error capture [on]
          Perform Find []

          Finding records where such an expiration date is located in any one of several fields would require adding more steps. This example searches two such fields, you can add additional blue lines to this script to search more:

          Go to layout [//select layout based on records where date field is defined]
          Enter Find Mode [] // clear check box
          Set Field [YourTable::ExpirationDateField1 ; "< " & Get (CurrentDate) + 60]
          New Record/Request
          Set Field [YourTable::ExpirationDateField2 ; "< " & Get (CurrentDate) + 60]
          Set Error capture [on]
          Perform Find []

          • 2. Re: Report on records going to expire within next two months, sorted
            TraceyLillis

            I am getting the error message "An operator (e.g. +, -, *, …) is expected here" when I have gone into the set field.

            The other expiry fields are

            • C.P.R Expiry
            • Police Check Expiry
            • Bluecard Expiry

            Thankyou for your help.

            • 3. Re: Report on records going to expire within next two months, sorted
              philmodjunk

              Make sure that you are using both Specify buttons to set up the parameters for set field. If you see an extra pair of [], you are using the repetition number box instead of clickding the second specify... button in lower right of the script editor.

              When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Click OK. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

              • 4. Re: Report on records going to expire within next two months, sorted
                TraceyLillis

                Okay, now my DB is playing up big time since trying this.  Does the  script change the field data when 'set field' is used?  For some reason  it has changed the expiry field calculation which calculate 3 years  ahead (or 1 year for  CPR expiry).  The dates now do not work on my  expiry fields!

                I thought some of my records might be corrupted, so I tried deleting some and now  I  am getting an error message "This operation cannot be performed because  one or more of the relationships between these tables are invalid."

                OMG,  I must learn to backup before trying anything as now I cannot revert my  database and everything has begun to fall apart.  My boss is going to  kill me.  Cry

                I would like to upload my database to this in the hope that someone can help me  fix the date fields and also help with the report script of records  going to expire in 2 months, but I can't work out how to do this. 

                TIA

                Tracey

                • 5. Re: Report on records going to expire within next two months, sorted
                  philmodjunk

                  You can upload a file to a share site and then post the download link here.

                  If your script is entering find mode like it should, then set field does not modify any data in your system.

                  • 6. Re: Report on records going to expire within next two months, sorted
                    TraceyLillis

                    I have uploaded my file http://www.mediafire.com/file/5fyp8b313q4p93j/preclinicals.fp7

                    Really, really appreciate any help here. :)

                    Thankyou.

                    Tracey

                    • 7. Re: Report on records going to expire within next two months, sorted
                      philmodjunk

                      You have this as your set field step:

                      Set Field [ preclinicals::First Aid ; preclinicals::First Aid  ≤  Get ( CurrentDate ) + 60 ]

                      You should have:

                      Set Field [ preclinicals::First Aid ; "≤ " & Get ( CurrentDate ) + 60 ]

                      • 8. Re: Report on records going to expire within next two months, sorted
                        TraceyLillis

                        Thankyou very much!Smile  I can't belive I was missing such a small step.  I really need to practice scripting, do you have any recommendations on websites for tutorials at all please?

                        Also, any idea why my formula for the date calculations (add 3 years and 1 year to CPR Expiry) are now not working?  They were working very well before I started playing around with my scripting (I know you said that it wouldn't have affected them).  My date formats are all the same dd/mm/yyyy, but it has converted them to mm/dd/yyyy and doing the calculation wrong too.  E.g 30/6/2008 should have an expiry of 30/6/2011 but the DB is calculating it as 6/6/2013.  Also, the 1 year expiry starts at 30/6/2010 and should expire on 30/6/2011 but appears as 6/6/2013?

                        Thanks again

                        Tracey

                        • 9. Re: Report on records going to expire within next two months, sorted
                          philmodjunk

                          Even though you are using european date formats (DDMMYYY), the Date function still requires that you enter the parameters as Date ( Month , Day, Year ) so you need to change : Date ( Day ( C.P.R ) ; Month ( C.P.R ) ; Year ( C.P.R ) +1 ) to Date ( Month ( C.P.R ) ; Day ( C.P.R ) ; Year ( C.P.R ) +1 )

                          • 10. Re: Report on records going to expire within next two months, sorted
                            TraceyLillis

                            Perfect, thankyou so much for your help.