1 2 Previous Next 15 Replies Latest reply on Nov 20, 2012 1:01 PM by philmodjunk

    Filtering records by date



      Filtering records by date


           I am building a monthly commission report. I have created a relationship on the historical data records sorted by date. The commission is to be calculated on the date. I have also created a layout that displays the records related with the commission.

           The view of the table is called RequestArrivalMonth and it contains the relevant date field.

           The layout is called Commission of the Month. I have also developed a script that is triggered once the layout is loaded. The script is also called CommissionOfTheMonth. In the attached file you can see the contents of the script.

           Basically the idea is that the user selects a beginning-of-month date in the dialog. This date is assigned to $tmpVarCurrentDate. I need now to define an end-of-month date in the dialog in order to limit the records to be used for calculating the commission. How do I do it ?

           The one option that occurs to me is that I include in the relationship RequestArrivalMonth the start and end dates relevant for the commission ... but then this needs to happen only after the user has selected the month in the layout. Please advise.


        • 1. Re: Filtering records by date

               Method 1: Define a calculation, cMonth in your portal's table as: Datefield - Day ( Datefield ) + 1 with Date as the return type. This takes any date and returns the date for the first day of the month so you can match it against your Firsta day of month variable without needing to compute the date for the last day of the month.

               Method 2:

               Date (  Month ( $tmpVarCurrentDate ) + 1 ; 0 ; Year ( $tmpVarCurrentDate ) )

               will return the date of the last day of the month of the same month as found in $tmpVarCurrentDate.

          • 2. Re: Filtering records by date

                 Hi Phil,

                 thanks for the quick response. I notice my question was not clear enough. What I need to know is: how, where and when do I filter the records for all records between first of the month and last of the month ?

                 The calculation you mentioned further above needs to be done of course ... but how do I set the filter for the records ?

            • 3. Re: Filtering records by date

                   Use this information as part of your find criteria.

                   If you use the cMonth approach, you can just use your variable as criteria entered into that field in your stored find.

                   If you use a date range from first of month to end of month, use this type of scripted find:

                   Enter Find Mode [] --> clear the pause check box, no stored criteria
                   Set Field [YourTable::DateField ; Globals::ThisMonth & ".." & Globals::ThisMonthEnd ]  --> This only works for fields with global storage specified
                   #Put additional set field steps here to specify any other find criteria as needed.
                   Set Error Capture [on]
                   Perform find []

              • 4. Re: Filtering records by date

                     Hi Phil,

                     working once again on filtering records. Here where I am based on the description at the top of this post:

                •           In the table Request I have created a field called Request::ArrivalMonth, which is calculation of the first day of the month based on the arrival date with result Date.
                •           On the other hand I had a global storage field which contains the date of the month of the commission to be calculated, the type of the field is date; GlobalVarsConsts::MonthOfCommission
                •           Also, I am using Germany as regional setting, where the correct date format is DD.MM.YYYY. 
                •           Theoretically it should be just a matter of matching the  GlobalVarsConsts::MonthOfCommission in all records of Request::ArrivalMonth. I am trying to include this comparison in the Perform Find filtering but get the error as you see on the screenshot.

                     Can you advise what is wrong ? Can the regional setting be the issue or do I need to use a function to compare two dates ?

                • 5. Re: Filtering records by date

                       You can't use calculation expressions inside a stored find request. That's why my example uses set field to enter the criteria instead of a stored find request.

                       The date format will not matter in my example script as long as the fields are of type date and you use the correct format for the date fumction--it must use parameters in the Month ; Day ; Year order regardless of your date format settings. (Dates are stored as integers and the regional formats are applied during display and data entry.)

                  • 6. Re: Filtering records by date

                         Here's what I did now: 

                         1- deleted the stored find conditions in the Perform Find command

                         2- added a Set Field line: Request::ArrivalMonth, type = calculation of date is the field to be filtered. The filtering criteria is in GlobalConstsVars::MonthOfCommission. Both fields are of type date and their contents are ok

                         When I execute Perform Find, I see in the data viewer that GlobalVarsConsts shows as "unrelated table". GlobalVarsConsts is a one record table with global storage fields. When I execute the Find I get error 400 returned = Criteria empty. Why, have I not stored the criteria in the Set Field command ?

                         Do I need to related it to Request:: ? And if yes, what is the most appropriate way ?

                         Is the mistake elsewhere ?

                    • 7. Re: Filtering records by date

                           The field must have global storage specified and for global fields, the value in the field is always accessible and from any layout in your file whether or not it is defined in a related table. If it were truly a field with global storage specified in field options, you'd see a value in the data viewer not <unrelated table>.

                      • 8. Re: Filtering records by date

                             Clear ! Works now. The last step is to ask for the date field to be not empty. I assume I use another Set Field (as it is a different date field. I tried this expression but it doesn't work:  ≠ IsEmpty ( Request::Arrival ). What would be the appropriate expression ?

                        • 9. Re: Filtering records by date

                               Set Field [Request::Arrival ; "*"]

                               A useful thing to keep in mind is that your set field steps enter criteria according to the same rules you would use when performing your find by hand. Thus, you may want to test an idea for search criteria by performing the script manually to see if it works before incorporating it into your script as a set field step.

                          • 10. Re: Filtering records by date

                                 Cool, it's working now. Polishing my solution and have three questions:

                            •           based on my script from further above. Is there any "clear" instruction required in the script ? When I call other scripts, sometimes the filter from this script is apparently still active and I get less records. Not sure if this is a bug or if I need some type of clear command when I close the layout.
                            •            Set Field [Request::Arrival ; "*"] is perfect and works. If I want to use several criteria for the same field, do I use several Set Fields with the same field, or is there any other way of refining the search with some type of AND ?
                            •           ... and last, I have a global storage variable that should contain always today's date, anytime it is used. What is the best way of implementing this: use a script at startup of the application for instance ?
                            • 11. Re: Filtering records by date

                                   1) This script simply produces a found set of record and then has no further influence on what records you see unless the script is performed again. If this script is performed by a script trigger, check and see if these other scripts are tripping that trigger and thus performing that script as well.

                                   2) Several criterai in the same field? Can you post an example? I think you are confusing AND and OR type finds again.

                                   3) Why do you need such a variable? Today's date is readily available to your database through a variety of other sources. An Unstored calculation field can display today's date. You can use the Insert menu while in layout mode to insert layout text that will automatically display today's date. Fields can auto-enter today's date whenever a record is created or modified.

                              • 12. Re: Filtering records by date

                                     thanks, Phil, on

                                     3) I need the year of today in a Set Field operation in the script. In the list of functions in the Specify-dialog for scripts there is no related Today() function. What would you recommend ?

                                     On 1) and 2) and only as an FYI for other users 

                                     1) I'll check on this one and maybe it's a bug and then I won't pursue

                                     2) I answered myself this question by testin it. Nevertheless, I would like all records with a not empty arrival date and where the arrival date corresponds to a certain year. So all I need to do in the script is:

                                Set Field [Request::Arrival ; "*"]
                                     Set Field [Request::Arrival ; GlobalVarsConsts::MonthOfCommission]


                                • 13. Re: Filtering records by date

                                       3 ) Get ( CurrentRecord )

                                       2) all you need is:

                                       Set Field [Request::Arrival ; GlobalVarsConsts::MonthOfCommission]

                                  The second script step overwrites the first.

                                  • 14. Re: Filtering records by date

                                         3) the context is: I need all records where Request::Arrival contains the year of today. Request::Arrival is a field which contains several different dates. I need to filter out the ones for this year. I list then all the records of this year in a layout. This is why:

                                    Set Field [Request::Arrival ; (... what function do I use here to qualify records of this year ? It would need to be the year of today's date) 


                                         2) Ack. 

                                    1 2 Previous Next