14 Replies Latest reply on Apr 1, 2013 12:38 PM by philmodjunk

    Help with creating a script

    AngeloTee

      Title

      Help with creating a script

      Post

                Can someone kindly help me create a script (including a button to perform the task) to search, find, and create a report of all records that fall within a specific date range on fields RDD1, RDD2 and RDD3. (These fields are renewal dates) 

                The range will always be as follows,

                starting from current date (ie today's Date),

                14 days before the dates displayed on fields  RDD1, RDD2 and RDD3    

                                                           to    

                21 days after  the dates displayed on fields  RDD1, RDD2 and RDD3.

                I have attached 3 screenshot examples of the kind of records we have.

            

      RDates_1.gif

        • 1. Re: Help with creating a script
          AngeloTee

               2nd screenshot

          • 2. Re: Help with creating a script
            AngeloTee

                 3rd screenshot

            • 3. Re: Help with creating a script
              philmodjunk

                   You appear to be using multiple fields within the same records where you should instead have 1 set of fields from a set of multiple related records. Thus, your basic design complicates the type of find you want to perform.

                   Each set of fields for the three suppliers should be records in a related table of suppliers.

                   Another thing not clear to me is whether this is an inclusive (OR) or exclusive (AND) search.

                   Do you want all records where any one of the three RDD is within the specified range? (an OR search)

                   Do you want only those records where all three fields are within the specified range? (an AND search)

              • 4. Re: Help with creating a script
                AngeloTee

                     Thanks for your comments. I know i shoould have 1 set of fileds from a set of multiple related records, however i never quite mastered how to create related records.

                     I would like all records where any one of the three RDD is within the specified range? (an OR search)

                     Thanks once again

                • 5. Re: Help with creating a script
                  philmodjunk

                       I strongly encourage you to learn how to set up and work with data in related tables. Without being able to do this, you are cutting yourself off from much of what Filemaker can do and you will encounter many situations such as this where not using a related table complicates your databse design.

                       For example, this script is three times longer than if your supplier data were in a related table.

                       Step 1: Define two additional calculation fields, cRDDstart, cRDDend for each RDD field.

                       Define cRDDstart1 as:

                       RDD1 - 14

                       Define cRDDend1 as:

                       RDD1 + 21

                       For all 6 of these calculation fields, select Date as the result type.

                       Now this script will find all records where today's date falls within at least one of these date ranges:

                       Enter Find Mode[] ---> clear the pause check box
                       Set Field [YourTable::cRDDstart1 ; "<" & Get ( CurrentDate ) ]
                       Set Field [YourTable::cRDDend1 ;  ">" & Get ( CurrentDate ) ]
                       New Record/Request
                       Set Field [YourTable::cRDDstart2 ; "<" & Get ( CurrentDate ) ]
                       Set Field [YourTable::cRDDend2 ;  ">" & Get ( CurrentDate ) ]
                       New Record/Request
                       Set Field [YourTable::cRDDstart3 ; "<" & Get ( CurrentDate ) ]
                       Set Field [YourTable::cRDDend3 ;  ">" & Get ( CurrentDate ) ]
                       Set Error capture [on]----> This step keeps error dialog from interrupting script if no records are found.
                       Perform Find []

                       For more examples of scripted finds, see this thread: Scripted Find Examples

                  • 6. Re: Help with creating a script
                    AngeloTee

                         Hi Phil, thank you for your response, quick question

                          

                         I already had the following script that helped me find records that fell within a specified date range for field RDD1

                         Set Field [Energy Database::RDD1; GetAsText ( Get ( CurrentDate ) - 14) & "..." & GetAsText ( Get ( CurrentDate ) + 21 )

                          

                         How would i add on to the script to include records that fall within the specified date ranges in RDD2 and RDD3, to end up with a total of all records in RDD1, RDD2 and RDD3?

                         Thanks as always

                          

                          

                    • 7. Re: Help with creating a script
                      philmodjunk

                           Good idea! Don't know why I didn't use that expression in my example script....

                           Enter Find Mode[] ---> clear the pause check box
                           Set Field [YourTable::RDD1 ; Get ( CurrentDate ) - 14 & "..." & Get ( CurrentDate ) + 21 ]
                           New Record/Request
                           Set Field [YourTable::RDD2 ; Get ( CurrentDate ) - 14 & "..." & Get ( CurrentDate ) + 21 ]
                           New Record/Request
                           Set Field [YourTable::RDD3 ; Get ( CurrentDate ) - 14 & "..." & Get ( CurrentDate ) + 21 ]
                           Set Error capture [on]----> This step keeps error dialog from interrupting script if no records are found.
                           Perform Find []

                           or you can:

                           Set Variable [$DateRange ; value: Get ( CurrentDate ) - 14 & "..." & Get ( CurrentDate ) + 21 ]
                           Enter Find Mode[] ---> clear the pause check box
                           Set Field [YourTable::RDD1 ; $DateRange ]
                           New Record/Request
                           Set Field [YourTable::RDD2 ; $DateRange ]
                           New Record/Request
                           Set Field [YourTable::RDD3 ; $DateRange ]
                           Set Error capture [on]----> This step keeps error dialog from interrupting script if no records are found.
                           Perform Find []

                      • 8. Re: Help with creating a script
                        AngeloTee

                             Thank you so much Phil. I used the first option and it worked perfect

                        • 9. Re: Help with creating a script
                          AngeloTee

                               I have another scenario though.

                               If a sale is made to a new customer, we record the sale on the first column of fields directly below Supplier1.

                               If a sale is a renewal, i.e. an existing customer renewing the contract at the first anniversary, we record the sale under Supplier2 and Supplier3 if it’s a second  anniversary.

                               For each sale, we record the date of the first day (Monday) of the week the sale was made in fields Wk Commencing1, Wk Commencing2 and Wk Commencing3.

                               In the examples shown on the screenshots, we have

                               11/03/2013 for Wk Commencing1

                               04/02/2013 for Wk Commencing2

                               11/03/2013 for Wk Commencing3

                               I was wondering if there is a way i would create a script that would help me perform a find for, lets say, all records of sales made on week commencing 11/03/2013, searching in fields Wk Commencing1, Wk Commencing2 and Wk Commencing3.

                               As always, I appreciate your help

                               The Wk Commencing date will vary for each search, ie. In this case i want to search for records for week commencing 11/03/2013, however, i may also want to search for records for week commencing 18/03/2013

                          • 10. Re: Help with creating a script
                            AngeloTee

                                 2nd screenshot

                            • 11. Re: Help with creating a script
                              AngeloTee

                                   3rd screenshot

                              • 12. Re: Help with creating a script
                                AngeloTee

                                     Another quick question, from the earlier scenario

                                     Once a  Find has been performed, how do I add onto the script to only display a select few fields from the found records, particularly for viewing them in a Table Format? And also sort by date order.

                                       

                                • 13. Re: Help with creating a script
                                  philmodjunk

                                       You really, really need to transistion to a related table for this data. What do you do for the 3rd anniversary? Add more fields yet and have to modify all of these scripts to handle the added set of fields?

                                       The find on week commencing would be nearly identical to the previous find script but without the date range expression:

                                       Enter Find Mode[] ---> clear the pause check box
                                       Set Field [YourTable::WeekCommencing1 ; YourTable::GlobalDateField ]
                                       New Record/Request
                                       Set Field [YourTable::WeekCommencing ; YourTable::GlobalDateField ]
                                       New Record/Request
                                       Set Field [YourTable::WeekCommencing ; YourTable::GlobalDateField ]
                                       Set Error capture [on]----> This step keeps error dialog from interrupting script if no records are found.
                                       Perform Find []

                                  • 14. Re: Help with creating a script
                                    philmodjunk
                                         

                                              Once a  Find has been performed, how do I add onto the script to only display a select few fields from the found records, particularly for viewing them in a Table Format? And also sort by date order.

                                         Create a new layout that displays data the way you want it to be displayed.

                                         Put a go to layout step at the end of your script such as:

                                         If [ Get ( FoundCount ) // records were found ]
                                            Go to Layout [....
                                         End IF

                                         To go to this new layout. You can add a button to this layout that returns you to the original layout.