Finding tax based on a range

[TaiChi56]

Ok, I have been trying to make a script that would work. I just keep confusing myself. I can get somethings to work but not the way it should.

I have to generate a report every month on the taxes that have been collected for that month. This is based on two fields, "Date Sold" and "Total Tax"

How can I write a user friendly script that lets the user put in what month to check for tax? I have tried the custom dialog but cannot seem to figure out how to write the script so the user can put in a range of dates, for example 051101 - 051130, and the user would get all inventory sold on November 1 2005 to November 30 2005 that collected tax.

I need the script to be a user prompt because we may need to go back a year and check a certain month. Any help would be greatly appreciated. Thank you.

Edited December 2, 2005 by Guest

[Raybaudi]

Hi,

this is the basic script you have to start from:

Enter Find Mode

Show Custom Dialog <-- with input field: DateSold

Perform Find

As you want to search a range, into the input field that will appear in the custom dialog, you have to put something like:

01/01/2004..03/31/04

BTW that script is only the start point, you can (have to) make some adjustment to suit your needs !

[TaiChi56]

Thank you. Here is what I have so far.

Enter Find Mode []

Show Custom Dialog["Inventory sold for the month"; "This find will give you all inventory sold for a specific month.;Inventory::Date Sold]

Perform Find/Replace[]

I can find the items sold for one particular day. But it does not work for range. I am thinking that I have to put this in the Perform Find/Replace. But how would I do that and the user can put the dates in. If I put the dates 50101...50102 in the "Date Sold" field during the custom dialog part,it will only find the last one the 50102.

[Raybaudi]

So, your DateSold isn't a date field !

It is a text field !

But the range has to work.

051101..051107

gives me all the records with those text inside

When in the script editor, get a finest look to the input field (TAB 2 of Show Custom Dialog) and don't insert anything into Perform Find[] brachets

[Raybaudi]

Perform Find/Replace[]

Ah...

Not that command (that is between EDITING)

You have to choose Perform Find under FOUND SETS

[mdpres]

Here is a simple date range find file. You must enter as a date or a partial date. I have not tried to do this as text, but I suppose if you changed the date fields to text the script would still work.

[TaiChi56]

Excellent, thank you for the help. I just have one problem. When I put the dates in I get a error message saying:

The value of this field must be a valid date in the range of years 1 to 4000 and should look like "12/25/2003.

The user has the dates as follows:

For January 1st 2005 to January 31 2005 -

50101 - 50131

When I run the script I cannot get past this error message. How can I get it to accept this kind of date format?

[LaRetta]

I highly discourage allowing Users to enter incorrect date formats 51130 etc. I promise you that you will forever (throughout your solution) be required to play games and jump through hoops converting these text dates to proper date format. And your attempts will fail at one time or another... I promise you that a User will enter a character that you haven't anticipated. If a User types 5111, what month and day do they mean (November 1 or January 11)? It will only work if you insist on 2-digit month and 2-digit day. It can be done (with a heavy validation calc) but again, it is a very bad habit. In today's world of computers, ALL Users understand correct date entry. To allow anything else is cutting your own throat. And when your Users have to remember to type correct dates in all other programs but revert to the special date entry in YOUR program, they will resent you for it eventually. Make your date fields DATE.

[color:blue]BTW, you have another post asking the exact same question here [color:blue]. I had a nagging feeling that this question sounded familiar ...

Dean and Daniele have just wasted their time showing you the exact SAME thing again! Everything covered in this thread is already covered in DETAIL in that other thread!! If you don't get results on a thread, post BACK on it until you understand, okay? We are more than willing to help but covering the SAME ground sucks.

BTW, using Custom Dialog doesn't work well for dates ... FM can't validate them. It is better to provide two global date fields, gFrom and gThrough. I had spent quite some time describing the process of using global dates (and giving you a script on taking those globals and performing your find range from them) but I deleted it out of frustration when I realized VICH had already told you about using globals in that other thread!

LaRetta

[TaiChi56]

Thank you for responding. Did not realize that I had posted twice. Believe me it is not out of trying to force an answer. Apparently it is either stupidy or just did not realize it. I would greatly appreciate the script you were writing.

I am new at this and trying to learn. So there will be times that I make mistakes, as other people will. Sorry you got so upset about it. Again thank you for responding.

[LaRetta]

Hi TaiChi56,

The script you posted above, your prior post about using Set Field[], your prior post about this specific problem (in fact all of your posts) indicate that you still lack some critical basic understandings. I suggest you refer to the manual and FM Help whenever you have a problem and create a test file to create what the book (and Help) is showing. This will teach you the basics. Then when you post your question and we explain a process you'll be able to duplicate it. It is a lack of understanding the BASICS that causes frustration. We certainly don't expect you to be perfect. Lord knows, I probably make more mistakes than anyone here.

So I've started a test file for you. I modified Patrick's because: 1) I prefer to leave the global dates instead of resetting them to blank each time. In this way, the next time Custom Dialog opens the User can see what they had entered last time (and just modify their prior entry). And since globals clear in multi-user, they start fresh every day anyway. Leave the globals blank when you design the process so they'll come up blank each day for the User. This is a personal choice which I've found Users like. 2) I used Set Field[] instead of Insert Calculated Result[]. It is possible in vs. 7/8 and is preferred. And by using TWO Insert Calculated Results[], the second was replacing the first anyway. 3) I test to be sure both dates are filled in and 4) I test no records found using Found Count instead of an Error 401 (again, personal preference). And I've added comments in the script to further assist.

You will see the global dates on the layout. But this is only so you can see it working. They should not be on any layout. And you must use real dates, okay? This will sound funny, but dates are sacred. Trust me on that ... leave your dates as dates and require that Users enter correct dates.

Now ... you post back here after you've played with this test file and read FM Help (type F1 to bring up help). In the search box, type Set Field script step and Show Custom Dialog script step and read their example scripts and CREATE them in your test file so you understand what they do and watch them in action!

And keep in mind that I am posting because I care and NOT to belittle, pick or make you uncomfortable. FileMaker is wonderfully fun. And your frustration will diminish greatly once you have these basics implanted. I will help you through this and I will keep on helping you ... no matter what it takes, okay? I am the last person on earth anyone should feel intimidated by.

LaRetta

Date_RangeREV.zip

[LaRetta]

Hello everyone!

BTW, using Custom Dialog doesn't work well for dates ... FM can't validate them.

Well I'll be snockered!!! FileMaker changed Custom Dialog behavior in 7/8 and I missed it! In prior versions, using a global date would NOT be validated 'in the moment' and would accept invalid dates!! We can now use Custom Dialog with dates safely and FM's standard date validation will respond immediately!

I had noticed they removed the X so Users couldn't close the Dialog window but I missed the added 'instant' date validation. If this enhancement was published anywhere, I missed it. What a wonderful improvement!

Sorry for my mis-speak above about Custom Dialog and dates ...

LaRetta

[TaiChi56]

Thank you very much. I will work with this and get it to work with my program. When I do I will post back and let you know.

Yes I agree I need to work more on the basics. Guess I am getting a little impatient. I will read the examples and try to work on my problems more before posting. Thank you again.