1 2 Previous Next 21 Replies Latest reply on May 29, 2009 10:35 AM by philmodjunk

    Script to find records within a given month

    lkn4atlantis

      Title

      Script to find records within a given month

      Your post

      How would I create a script so if I entered "May" it would give me all the records with an experation date between 05/01/09 - 05/31/09? If I entered "June" it would give me all the records with an expiration date between 06/01/09 - 06/30/09, same with July, August... ect.

      Thanks

        • 1. Re: Script to find records within a given month
          philmodjunk
            

          Here's a fairly simple way, there are many other approaches possible.

           

          Define a calculation field: MonthName(Datefield) & " " & Year(datefield) in the same table as your date field. (Put the name of your date field in place of DateField in this calculation.) Set the calculation to return text. Name it cMonthYear.

           

          Let's further assume you have one global text field, gMonth for the user to select a month name and a second global field, gYear for them to select a year.

           

          Now your script works like this:

           

          Enter Find Mode []

          Set Field [YourTable::cMonthYear, YourTable::gMonth & " " & YourTable::gYear]

          Set Error Capture [on]

          Perform Find []

          Set Error Capture [off]

           

          That should do it.

          • 2. Re: Script to find records within a given month
            lkn4atlantis
               I really appreciate your reply, but I am extremely new to FM, could you please simplify your answer, maybe more step by step directions? I'm assuming I start my goin to scripts;scriptmaker; create new script;  then???   Thank you very much  :smileyhappy:   i really aprreciate any help.
            • 3. Re: Script to find records within a given month
              philmodjunk
                

              Not a problem. It's always something of a guessing game to judge the skill/experience lever of another forum member.

               

              Overview:

              Once you've selected Scripts | Manage Script... , click the New button to start a new script in the script editor.

              Enter a descriptive script name in the script name box.

               

              Now to build a script, you select script steps from the left hand column by double-clicking them or by clicking them once and then clicking the "move" button at the bottom of the window. WHen you are through, close the window by clicking its control (a red "x" in the upper right corner in windows). If you are asked to save the script, click the save button.

               

              Now for step by step:

              1. Find the "Enter Find Mode" script step and double-click it. You'll find a check box labeled "Pause" that appears in the script options area at the bottom of the Edit Script window. Clear that check box.
              2. Add the set field step in the same way. You'll see two Specify... buttons. Click the upper button and select the cMonthYear table I described and which you should have already defined. Click the second Specify button and enter the calculated expression. Add the gMonth and gYear fields to your expression by finding them in the field list pane at the top of the Specify Calculation dialog. (Select the table from the drop down list of tables, then double click the field's name.)
              3. Add the remaining script steps the same way. Use the Script options area to set the Set Error capture parameters to "Off" and "On" as shown in my previous post.

               

              To perform your script, either select it from the scripts menu or attach it to a button.

               

              If you'd rather perform you find by hand, you can just define the cMonthYear field, place it on your layout, enter find mode, enter a month name and a year in this field, and click perform find.

              • 4. Re: Script to find records within a given month
                lkn4atlantis
                  

                Thanks again for your help, I don't think I specified what I wanted correctly, I apologize for that. I have a main screen with buttons, one button is leases due. When I click that button it opens a new template. What I want to be able to do is type in a month (January, February....) and then it will bring up the list of everyone who's lease expires during that month. I already have the main page and the button set up to open up the other template, I just don't know how to set up the script and what exactly I should have on the other page to enter the month.  Please help again lol  ???

                • 5. Re: Script to find records within a given month
                  philmodjunk
                    

                  The script example should work for that, You'd just add one more step at the beginning:

                   

                  Go to layout [TemplateName]

                   

                  The other issue is the year information. Your example doesn't specify the year. Do you want all records with the same month regardless of year? Otherwise, we've got to specify a year in some fashion or other. Since "leases due" indicates you may not care what year is specified, we can adjust the script for that also.

                   

                  Let me know what you need to specify for the year and then we can fine tune a final version of your script.

                  • 6. Re: Script to find records within a given month
                    lkn4atlantis
                      

                    To answer the year question, what it should be is if I type in "May" it would actually give all the lease due up to may 30 of this year. That way if I skipped renewing someone's lease in an earlier month it would give me that as well as all the leases currently expiring.

                    Thanks.

                    Amy

                    • 7. Re: Script to find records within a given month
                      philmodjunk
                        

                      That's a new wrinkle. Date fields in FMP can be treated as numbers. So if the user selects "May", we can use a script that finds all records up to 5/31/2009 by writing a script that puts < 5/31/2009 in a field while in find mode. That takes a little different approach since we need to find all records up to and including the specified month.

                       

                      Let's use a different calculation field. Another forum member, Comment, taught me this calculation:

                       

                      Date(1; Month(datefield); Year(datefield))  Where datefield is the name of your lease due date field. Set this calculation to return "Date".

                       

                      Let's Name this field cMonth.

                       

                      Now we need to translate the name of the month into its matching Month number.

                      Case (gMonthName = "January"; 1; gMonthName = "February"; 2; gMonthName = "March"; 3; ... ; gMonthName = "December"; 12) 

                       

                      (In place of ... in the above expression you'll need to continue to spell out all 12 months by name in the same pattern)

                       

                      Now we can write our script like this:

                       

                      Go to Layout [Specify layout where you can search by date and display the results you want]

                      Enter Find Mode []

                      Set Variable [$mnth, Case (gMonthName = "January"; 1; gMonthName = "February"; 2; gMonthName = "March"; 3; ... ; gMonthName = "December"; 12) ]

                      Set Field [YourTable::cMonth, "< " & date(1; $mnth; Year(get (Currentdate)))]

                      Set error capture [on]

                      Perform Find []

                      Set error capture [off]

                       

                      How's that work for you?

                       

                      • 8. Re: Script to find records within a given month
                        LaRetta_1
                          

                        I would not use month name at all!  If you search a date field for 5/2009, you will get all date records for the month of May, 2009.  You can script this as well.  For example, if you want all records which belong to the month/year you are currently in, it would be similar to:

                         

                        Enter Find Mode [ uncheck pause ]

                        Set Field [ YourDateField  ; Month ( Get ( CurrentDate ) ) & "/" & Year ( Get ( CurrentDate ) ) ]

                        Perform Find [ ]

                         

                        FileMaker is quite capable of using month numbers appropriately ... why convert them to text names first?

                        • 9. Re: Script to find records within a given month
                          lkn4atlantis
                             I'm still working on the script for finding the expired leases, but before I do that, I need to transfer that database to another database I created with other information. How would I do that.  The first database has Customer name, total rentals, total leases, then about 50 differant fields. The database I'm working on now that I want to move to the first database has Customer name Lease type, Lease expire date and a few other fields. Can you please tell me how to create a new database (chart) in a current database? Thanks much, Amy
                          • 10. Re: Script to find records within a given month
                            mrvodka
                               You have to copy the tables and data over. The file is the database.
                            • 11. Re: Script to find records within a given month
                              lkn4atlantis
                                

                              I still pretty new to FMP, can you please tell me how to do that. I have version 5.

                              Thank you

                              • 12. Re: Script to find records within a given month
                                mrvodka
                                   Oh a pre-7 version is a totally different animal. You have multiple files instead of tables. What is the purpose of trying to copy your fields? You should be able to just related to that other file.
                                • 13. Re: Script to find records within a given month
                                  philmodjunk
                                    

                                  I agree with Mr. Vodka, I'd keep the two files and link them via a relationship if at all possible. Otherwise, you have to manually add the field definitions one at a time and then use Import Records to pull in the data. Simple typos in your field name can drive you crazy when you try to do this.

                                   

                                  I'd recommend an upgrade to the latest version of FMP if you can afford the cost. The new features available will be very useful.

                                  • 14. Re: Script to find records within a given month
                                    lkn4atlantis
                                      

                                    We can't upgrade to a newer version, something to do with our server. The file I'm making is for work, and once it's done, it will be used on a daily basis, so I think it would be easier to have all the data in one place so I don't have to open 5 different databases. Like I said, I'm still new to FMP so if I can do the relationships and be able to use the database without having to open all the databases, I guess that would work. Problem with that tho is I don't know how to do relationships yet either  lol. I'll see what my book all says about it tho.

                                    Thanks

                                    1 2 Previous Next