# Help with dates within an idea script

#### Forums

Hi Brian,

I'm hoping you can help with the following please.

I'm building a macro to calculate interest on customs debts. The applicable annual rate is the one on force at the date of import, and currently I am using a very long compif statement to assign the appropriate rate.

Each time there is a rate change, I add the date of change and the rate to the front of the compif statement (e.g. this month the rate changed to 6.25% so I added IMPORT_DATE >= ""20240701"", 0.0625).

This is working fine, but at some point I will be handing this off to someone else to manage and I'm thinking there must be tidier way this can be done, probably involving an array of dates and rates..

Ideally, the person managing the script would be able to add a new date and rate to the bottom of the array rather than editing the equation.

It's far beyond my abilities but wanted to see if the above made sense and if you had any suggestions.

EDIT : I tried posting the script as a separate post and also uploading it but neither worked for me. I am now posting the rate tables below and the equation used to create the field

'01/01/2016 2.00%
'01/08/2022 2.50% Date >= ""20220801"", 0.025
'01/10/2022 3.25% Date >= ""20221001"", 0.0325
'01/12/2022 4.00% Date >= ""20221201"", 0.04
'01/01/2023 4.50% Date >= ""20230101"", 0.045
'01/03/2023 5.00% Date >= ""20230301"", 0.05
'01/04/2023 5.50% Date >= ""20230401"", 0.055
'01/06/2023 5.75% Date >= ""20230601"", 0.0575
'01/07/2023 6.00% Date >= ""20230701"", 0.06
'01/09/2023 6.25% Date >= ""20230901"", 0.0625
'01/10/2023 6.50% Date >= ""20231001"", 0.065
'01/07/2024 6.25% Date >= ""20240701"", 0.0625

field.Equation = "@compif(IMPORT_DATE >= ""20240701"", 0.0625, IMPORT_DATE >= ""20231001"", 0.065, IMPORT_DATE >= ""20230901"", 0.0625, IMPORT_DATE >= ""20230701"", 0.06, IMPORT_DATE >= ""20230601"", 0.0575, IMPORT_DATE >= ""20230401"", 0.055, IMPORT_DATE >= ""20230301"", 0.05,IMPORT_DATE >= ""20230101"", 0.045, IMPORT_DATE >= ""20221201"", 0.04, IMPORT_DATE >= ""20221001"", 0.0325, IMPORT_DATE >= ""20220801"", 0.025, 1, 0.02)"

Phil

### '********************RATES…

pcallan0 Fri, 07/12/2024 - 06:44

### Macro script attached

pcallan0 Fri, 07/12/2024 - 06:46

Macro script attached

### Hi pcallan0,That is strange…

Brian Element Fri, 07/12/2024 - 10:49

Hi pcallan0,

That is strange about not being able to attach a file, I just tried it as a user and it seemed to work.  What type of file where you trying to upload and where you using the Choose Files option below to attach a file?

As for your script I think maybe the easiest way is to have a script that reads a text file with the information and then creates the equation based on the script.  The user would then only have to update a text file instead of changing the iss file.  I will see if I can get an example for you.

### Hi pcallan0,Here is an…

Brian Element Fri, 07/12/2024 - 12:44

Hi pcallan0,

Here is an example of using a text file for your dates and rates and then reading the text file to create the equation.  The text file needs to be in the project folder.

Let me know if you have any questions.

Thanks

Brian

Files
Rate Tables.txt (214 bytes)

### Hi Brian, Thanks a million…

pcallan0 Mon, 07/15/2024 - 09:25

Hi Brian,

Thanks a million. I'm away for a couple of days but will get back to you properly on my return.

All the best,

Phil

### Hi Brian, That works…

pcallan0 Thu, 07/18/2024 - 11:27

Hi Brian,

That works perfectly, and your solution is far better than the one I had envisaged.

It will be far more efficient to have a "rates" file that can be updated when needed, and there will be no need to keep changing the macro.

I'll be incorporating your code into my current macro, and I have a couple of additional questions about working in folders.

The macro is designed to create a folder, import the source file, and move it to the folder. The folder is defined as a string and passed through the macro as foldername (where foldername = "Customs Debts")

I do this for tidiness, as folks might run the macro within an existing audit project and it keeps the debt calculation element separate.

However, working in folders can cause a problem which I've never been able to satisfactorily sort out.

Say for example my working directory at the beginning is my IDEA project directory e.g. C:\Phil\Idea Project 1

However, when I create the folder the working directory becomes C:\Phil\Idea Project 1\Customs Debts

This means I am unable to code paths to folders or files in the original project

For example wd &"Exports.ILB\Output file.xlsx" becomes C:\Phil\Idea Project 1\Customs Debts\Exports.ILB\Output file.xlsx

I'd love a way to define and "lock in" the main project directory path as a string so that I call on it whenever I need to.

I'd say this is one of those questions where the answer is very obvious to those who know, but unfortunately I'm not one of them :D

If you could advise you'd fix something that has puzzled me for several years.

Kind regards,

Phil

### Hi Phil, this is a known…

Brian Element Fri, 07/19/2024 - 07:48

Hi Phil, this is a known issue that I have asked many times that Caseware fix or at least add another variable that always holds the project folder variable but it is still outstanding.

The workaround is to do a client.closeall, this closes all the IDEA files and it seems to reset the working directory to the same as the project folder.  Here is a short demo showing what is going on.  It creates a folder and copies a file into it and then opens the file, when the file is opened that is when the working directory changes, after that I do a closeall and the working directory changes back to the project folder.  Hopefully this solves your problem.

``````Dim wd As String

Sub Main
wd = Client.WorkingDirectory()
MsgBox "Start of script: " & wd

'create a new folder
On Error Resume Next 'need this in case the folder already exists
Client.RefreshFileExplorer

wd = Client.WorkingDirectory()
MsgBox "After creating the folder: " & wd

wd = Client.WorkingDirectory()
MsgBox "After copying the file to the new folder: " & wd

Client.OpenDatabase("IDEATest\Sample-Customers.IMD")

wd = Client.WorkingDirectory()
MsgBox "After opening the file in the new directory, this changes the working directory: " & wd

client.closeall

wd = Client.WorkingDirectory()
MsgBox "After performing a closeall, this resets to the project folder: " & wd

End Sub``````

### Hi Brian, Thank you, this…

pcallan0 Fri, 07/19/2024 - 09:02

Hi Brian,

Thank you, this does solve the problem.

I typically open the final "results" file so it is displayed to users at the end of the macro run.

However, I know see this is changing the workingdirectory path which is what causes problems if the macro is rerun.

Your solution does fix my problem so I guess the users will just have to navigate to the file.

I had tried to fix this by adapting this snippet from the IDEA Script Editor help:

Sub Main

' Get the current Desktop project folder.

Current = Client.WorkingDirectory()

' Display the Desktop project folder on screen.

MsgBox Current

' Set a new Desktop project location.

Client.WorkingDirectory = "C:\TEST"

' Show the result.

MsgBox Client.WorkingDirectory

' Restore the original Desktop project location.

Client.WorkingDirectory = Current

MsgBox Current

End Sub

When I tried this as a stand alone script it seemed to work.

When I incorporated it into my script the message box at the end of the macro showed the original working directory.

I thought I was onto a winner, but I thought too soon because when I reran the macro the initial message showed the changed working directory.

Very confusing but your client.closeall does give me the workaround.

Thank you once again and have a lovely weekend,

All the best

Phil

### EUREKA Hi Brian, Thank once…

pcallan0 Wed, 07/24/2024 - 07:25

EUREKA

Hi Brian,

Thank once again for your advice. I've been playing around with the client.closeall and found something that might be of interest.

If I put the client.closeall as the first command in submain, before I define the working directory, this resets everything.

Then when the working directory is defined (intially) it will be the project folder.

So no matter what I do in the macro, if I need to rerun it on the same project it will always work and my paths will make sense.

This also allows me to run my macro and open the results file at the end to display to the user.

In addition, if the macro crashes for any reason, it doesn't affect the ability to rerun it as the directory is reset at the start and not the end.

A win win for me and I would never have figured this out without your advice.

Thanks a million,

Phil

### Thanks Phil, that makes…

Brian Element Wed, 07/24/2024 - 14:44