Date Extraction

Brian Element
Background

This script extracts a maximum of 24 specified dates entered (Enter Dates button) or loaded from a text file (Load Dates File button).  Days of the week can also be extracted into separate files. Specified dates or days of the week selection can be inverted by checking the "Output all dates NOT selected" box. Dates must be entered using the DDMM format. Text files with dates can be created for subsequent reuse or loading by selecting the Save Button after inputting dates via the Enter Dates button.

The scripts were updated on April 10, 2013.  I changed how the dialogs work so they will stay open as you perform different functions within them.

The script has been further updated on September 12, 2014.  There was a possible problem when selecting days of the week that it would throw an error, this has been fixed.  I also added code that it will default to the active database when running the script.

Documentation

Date Extraction

Background

This script allows you to extract up to 24 individual dates and / or individual days of the week.  The dates are not year dependent, meaning that if you enter 2512 you will have all dates that fall on December 25 for any year.  The same for the Days of the Week, this will create a database for all dates that fall on the date of the week.

Main Menu

The Main Menu has the following options:

  • Select File – this allows you to choose the IDEA database that the extract will be performed on.
  • Select Date for Extraction Dropdown – once you have selected the file you will be able to select the date field that you will perform the extraction on.
  • Output all dates not selected checkbox – If you check this checkbox your extract will be based on all dates or days of the week that you have not selected.  Performing two extractions, one with the checkbox selected and one without will give you all valid dates in your database.
  • OK Button – Once all the information has been selected you select the OK button to start the extraction.  The OK button will also validate that you have selected a file, selected a date field and have selected at least one date or one day of the week.
  • Cancel Button – This will exit the script.
  • Enter/Edit dates button – this will take you to another dialog in which you can enter or edit the dates you have selected.
  • Load Dates button – if you have previously saved your dates from the Enter/Edit dialog you can load them by using this button.  It will bring up an open file dialog where you can select the appropriate text file.
  • Help button – this will open the help dialog which will give you some basic help.
  • User File – if you have loaded a file using the Load Dates button the file name will be listed here.
  • Date 1 to Date 24 – This is the list of dates that you will be extracting on.  The dates are in the format DDMM, years are not used.
  • Days of the Week – This allows you to select one or more days of the week.  Each day of the week will be selected in its individual file with the name of the added on to the database name.

 Dailog 1

Add Edit Dates

This dialog is opened when you select the Enter/Edit Dates button from the previous dialog.  This dialog allows you to input up to 24 dates.  The dates are in the DDMM (Day – Month) format.  You can also load or save the dates entered.  The list is saved in a text file.  It is possible to have multiple text files with different dates that you can load in and perform the extraction.

 Dailog 2

 Help Dialog

Selecting the help button will bring up the following dialog.

 Dailog 3

Uses

The script can be used to extract transactions that were recorded on a holiday or certain of the week such as looking for transactions recorded on Dec 25 or Jan 1 or on a Sunday.

easlan Mon, 07/11/2016 - 06:19

Line 733
"task.PerformTask 1, db.Count"
!Bad equation provided!

Brian Element Wed, 07/13/2016 - 09:48

Hi Easlan,

I had a look at it last night and this is the part that performs the extraction.  I haven't played with it yet but one possibility is that it doesn't handle the German date format.  Can you confirm if you are running a German version of IDEA or that your region setting is for Germany.  When I have a chance I will try it out in another region as that could be causing the problem.

Brian

easlan Mon, 07/18/2016 - 07:50

Hi Brian,
We are using the englisch version. i had to change Line 681 {field.Equation = "@Replace(@str(@Day(" & dateField & "); 2; 0); """";""0"") + @Replace(@Str(@Month(" & dateField & "); 2; 0);""""; ""0"")"} and Line 721 {eqn = eqn & "; """ & thisArray & """"} from a comma to a semicolm. the script runs than very well, thank you for your effort writting this script.

Brian Element Tue, 07/19/2016 - 20:43

Hi Easlan,

Even though you have the English version your region settings are probably set to a non Enlgish country, that is why this problem is happening.  In some countries like Canada (where I live) and the IS the separator is a "," but in many other countries it is a ";", that was your problem and why changing it fixed the problem.  When I created these script I didn't know how to develop them to be more international, I do know I just have to find the time to update the scripts.

Thanks

Brian