1 2 Previous Next 19 Replies Latest reply on Nov 15, 2012 1:51 PM by TheScarecrow

    Script Date Range

    TheScarecrow

      Title

      Script Date Range

      Post

           I have a resource table with end_dates for each item.  I want to run a monthly script that would for the current month generate a list of all the resources that expire that month and would email a list of all the titles and the experation dates to someone.  I am kinda of a noobie and not sure how or where to start.  Can anyone help? Thanks.

        • 1. Re: Script Date Range
          philmodjunk

               You might want to read this: Please Help Us to Help You...

               There are a number of ways to do what you want depending on the design and set up of your database.

               Is this a single user database on your machine that you open every working day?

               Is this a database that is hosed over a network?

               That can make a difference in how you set up your script to do this on a monthly basis.

               To start, see if you can get this script to work. It finds all records with expiration dates that fall in the current month:

               Go To Layout [Specify a layout based on the table where you have these titles and expiration dates]
               Enter Find Mode[]
               Set Field [YourTable::Expirationdate ; Let ( T = Get ( CurrentDate ) ; Month ( D) & "/" & Year ( D ) ]
               Set Error capture [on]
               Perform Find[]

               once you can get that working we can add to the script to either attach a PDF of this list to an email or list the titles and dates as plain text inside the body of the email.

               Then the final step is selecting and implementing a way to "schedule" the script so that it is performed on a regular basis.

          • 2. Re: Script Date Range
            TheScarecrow

                 I apologize.  This is a multi user database that is on a network for a website. Its Filemaker 11 and the other users are using Filemaker to access it.  

            • 3. Re: Script Date Range
              philmodjunk

                   Ok, that rules out ExecuteSQL--a simpler way to go for things like this. Is the file hosted by Server or server advanced?

                   In the two examples you have shown, is the only criteria gender for the first and age group for the second?

                   Or could their be other criteria limiting what records are being counted/totaled? (Example: perhaps you want this for all records in a given date range.)

              • 4. Re: Script Date Range
                TheScarecrow

                     Its hosted on a server I believe advanced.  Yes a date range so for example say the month is November.  I want it to retrieve all files between Novermber 1 and November 30.

                • 5. Re: Script Date Range
                  philmodjunk

                       Sorry, my previous post was intended for another recipient. I've got more than one tab open to the forum and thought I was responsing to a different question.

                       Can you get the sample script that I posted to work?

                       When you run it, you should get a found set of every record that expires in the Month of November, 2012.

                       That's step one and we can build from there.

                  • 6. Re: Script Date Range
                    TheScarecrow

                         I am getting An operator is expected here error... am I doing something wrong?

                    • 7. Re: Script Date Range
                      philmodjunk

                           In which step?

                           Set Field?

                           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. Do not click the specify button next to the repetition box. Click OK to close this dialog box. 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.

                      • 8. Re: Script Date Range
                        philmodjunk

                             Please read the paragraph at the end of my last post.

                        • 9. Re: Script Date Range
                          TheScarecrow

                               ok I did that.  When I open that calculation window and enter that right of the semincolon without selecting the table and the field  I get The specific table cannot be found error. 

                          • 10. Re: Script Date Range
                            philmodjunk

                                 And what text is highlighted when you get that error message?

                                 You should have this entered:

                                 Let ( T = Get (CurrentDate ) ; Month ( T ) & "/" & Year ( T ) )

                                 Basically, everything highlighted in Blue in your last screen shot, EXCEPT for the semi-colon.

                            • 11. Re: Script Date Range
                              TheScarecrow

                                   Ok I got that working.  Thank you.  Now what.

                              • 12. Re: Script Date Range
                                philmodjunk

                                     So the script finds all records with an expiry date falling in this month?

                                     Next step is to get it to email the list.

                                     I suggest finding a place in your database to enter an email address for the recipient of this list. You can specify the address directly in the Send Mail script step, but then a change in email address requires a developer to change the script where using a field to record the email address means that changing the address is a data entry task.

                                     The list can be an attached PDF or plain text in the email body. I'll use plain text in the email body for this example.

                                     Add these script steps:

                                     Go To Record/Request/Page [First]
                                     Loop
                                        Set Variable [$ExpiredList ; value List ( $ExpiredList ; YourTable::TitleField & Char ( 12 ) & YourTable::ExpirationDate ) ]
                                        Go to Record/Request/Page [exit after last ; next ]
                                     End Loop
                                     Send Mail

                                     For now, enter the email address directly into the To box. Later we can take a look at how to refer to a field in a related table for the address.

                                     Enter an appropriate subject, select an email client or SMTP email server, then click the arrow button next to the Body box and select the "calculation" Option.

                                     Enter text into this specify calculation dialog like this:

                                     "Here is the monthly list of expired Titles:¶¶" & $ExpiredList

                                     Test this and make sure that you can successfully send the email and that the results are acceptable. (You may have to go with an attached PDF instead of plain text in the body of the email.)

                                     It's also a good idea to look up Send Mail in FileMaker Help to make sure that you have a compatible email client program and that it is correctly configured for FileMaker.

                                     Once you have that working, you'll need to decide whether to use an SMTP email server to send the email and thus be able to use a server schedule to perform this script or if you will use your email client software from FileMaker pro--using a "robot file" to perform the script at the correct time.

                                • 13. Re: Script Date Range
                                  TheScarecrow

                                       What does value List refer to?

                                  • 14. Re: Script Date Range
                                    philmodjunk

                                         There's a typo It should read:

                                         Set Variable [$ExpiredList ; value: List ( $ExpiredList ; YourTable::TitleField & Char ( 12 ) & YourTable::ExpirationDate ) ]

                                         Like the semicolon in set field, you don't enter value: List is the first part of what you enter as the expression for this variable.

                                    1 2 Previous Next