Trouble with script - searching on a date

[MoxieGirl]

Hi,

 

I'm new here, my first post actually. I'm a pretty experienced developer and know my way around a variety of languages (VBA, HTML, CSS, etc.) and databases (SQL, Access, etc.). Have been working with FileMaker in my spare time for a number of weeks now, and I have to say, if I had to choose between MS Access and FileMake, I'd pick FM without a second thought. I'm finding it SO much easier to work with, once you get your head around a few key concepts.

 

So my question, and reason for banging head on wall.

 

I have a small file for tracking a balance book. Has fields like EventDate, Client, Amount, Net, etc. My housemate has a massage business and needs to easily track her incomings and outgoings. The Table and data input Layout are set up and working fine.

 

What I want to give her is a screen where she can see her total Net Profit/Loss for any given day, any given month, any given year. I'm trying 2 approaches, but both are generating errors that they shouldn't be generating.

 

Approach 1: On the main form there is a button (so far I have 2 buttons) - This Year and This Month. Click on one of the buttons opens a new layout (RunningTotal) and filters it for either This Year's records or This Month's records, depending on which button you click. The 'This Year' script works fine, but when you click on the 'The Month' button, that script populates the date field with = $ThisYear (a variable that doesn't even exist in the script being run). 

 

Here's the scripts:

This Year

 

Go to Layout ["Runningtotal" (Balance Book)]

Enter Find Mode []

Set Variable [$ThisYear; Value: Year(Get(CurrentDate))]

Perform Find [Restore]          - The restore code is:  Find Records: Balance Book::EventDate: [= $ThisYear]

 

This Month code is:

 

Go to Layout ["Runningtotal" (Balance Book)]

Enter Find Mode []

Set Variable [$ThisMonth; Value: Month(Get(CurrentDate)) & "/" & Year(Get(CurrentDate))]

Perform Find [Restore]          - The restore code is:  Find Records: Balance Book::EventDate: [= $ThisMonth]

 

Like I say, when I run the 'This Year' script, it works as expected. When I run 'This Month', it enters = $ThisYear into the EventDate field.

 

So, I tried approach number 2:

A button takes you to the RunningTotal layout where there is a Popup box linked to the global field GBL_Year. The value list has 2 options: 2014 and 2015. I have records with event dates in both years.

I have an OnObjectModify script trigger 'YSort', which does this:

 

Enter Find Mode []

Set Variable [$SetYear; Value:Balance Book::GBL_Year]

Perform Find [Restore]            - the restore code is:  Find Records:  Balance Book::EventDate [= $SetYear]

 

 

When I set the value to 2015, it works fine, filtering the results to 2015 records. When I change it to 2014, it shows all records in both years. 

 

In an effort to debug, I added a 'Show Custom Dialog' command, displaying the $SetYear value, and it populates correctly (2014 or 2015).

 

I cannot figure out why these scripts are not working as expected.

 

Please help.

Thanks,

Renée

 

[bruceR]

A couple of items. Personally, I really dislike Perform Find [ Restore ].

This gets discussed very often here.

I prefer explicit scripted finds, they are MUCH easier to read and troubleshoot.

 

Secondly: your variable is not creating a valid date.

 

Set Variable [$Year; year( get( currentDate )) ]

Set Variable [$month; month( get( currentDate ))]

Set Variable [$search; $month & "/*/" & $year ]

Enter Find Mode [ do not restore ]

 Set field [ Balance Book::EventDate; $search ]

Perform Find [ do no restore ]

[eos]

Hi Renée,

 

but when you click on the 'The Month' button, that script populates the date field with = $ThisYear (a variable that doesn't even exist in the script being run). 

 

Are you sure that the Month script is attached to that button, and not the Year script? I would check that first; if the problem persists, use the Script Debugger (your profile says "Advanced") to see where the variable comes from.

 

Also, as you can see, the Restore options are a bit finicky about the values they accept. If you're searching for that year, you don't need the equal sign. (But if did need it, you'd have to calculate the complete string in the variable declaration as "=" & Balance Book::GBL_Year, before passing the $var to Restore.) And its presence is probably the reason why the script step interprets the expression as a literal value "=$SetYear", instead of evaluating it.

 

Also, if you use Perform Find [ restore ], you don't need Enter Find[].

 

Considering all these pitfalls of the somewhat cumbersome restore options dialog, why not go the more more explicit route of

Enter Find Mode
Go to Layout ["Runningtotal" (Balance Book)]
Set Field [ Balance Book::EventDate ; Balance Book::GBL_Year ]
Set Error Capture [ on ]
Perform Find [ ]

which allows you to freely reference the global field directly, and do other stuff without (necessarily) having to declare a variable (and see at glance what is happening in your script, without opening dialogs).

 

Similar for the Month script.

 

PS: For you next post, please find a better title, i.e. one that tells something about the technical issue at hand, rather than your state of mind. I guess the former would be of more interest to a larger number of people.    

 

That being said: welcome to FileMaker, and this great forum!

 

Oops: scooped by His Bruceness!    

[MoxieGirl]

Thanks guys for your quick response. I'm doing more testing now.

 

Good call on the title. Have made it more relevant.

 

I love it! Thank you guys SO MUCH. I knew it was something simple, always is once you have the answer.

 

So, putting this file out for testing, then returning to my larger, client records, db. All good fun.

Edited January 5, 2015 by MoxieGirl

[bruceR]

Regarding EOS point about not requiring variables to accomplish this; that's perfectly true; and useful.

 

A point in favor of the variable method is that, while troubleshooting using script debugger, you can step through the script and examine or even change the value of variables such as $search in my example; and then continue the script.

[Wim Decorte]

+1 on what Bruce is saying:

 

- using the Restore just hides code; avoid it when you can and be explicit

- use variables: easier to troubleshoot and you can even change the value in the Data Viewer if you want to test another scenario without having to change the underlying data in the fields