Find records 4 a date range and count and display

[M.A]

Hi everyone,

I am making a report to find all the cases within a date range and count those records and display it.

Here is the problem. I have two global fields, Month and Year, each attached to a value list having months (January, February ...... December) and year (1995, 1996,..... 2007). I wrote a script using If Else statements. Here it is:

GotoLayout["layout1"]

Enter Find Mode[]

If["Month = "January""]

Insert calculated Result[select, "field1", "DateToText("1/1/" & "Year") & "..." & DateToText("1/31/" & "Year")]

Else

...

...

Perform Find[Replace Found Set]

It says "No records modify this search" and When I click on Modify Find, I see the 'field1' (date field) populated with "3/22/0001...4/11/0003" instead of "1/1/2004...1/31/2004".

I have tried different combinations

"DateToText("1" & "/" & "1" & "/" & "Year")

But I get the same thing.

Thanks

[QuinTech]

Hi, M.A. Remove the quotes from around "Year" and see if that helps. What you are doing right now is passing a date of "1/1/Year" rather than passing the contents of the field named Year. Field names should be entered without quotes.

Also, you can probably simplify this script. Instead of having all those If ( Month = "January") statements, use this instead:

Set Field ( field1 , "calculation" )

where "calculation" is:

Case ( Month = "January" , Date ( 1 , 1 , Year ) & "..." & Date ( 1 , 31 , Year) ,

Month = "February" , Date ( 2 , 1 , Year ) & "..." & Date ( 2 , 28 , Year) ,

Month = "March" , Date ( 3 , 1 , Year ) & "..." & Date ( 3 , 31 , Year) ,

...

Month = "December" , Date ( 12 , 1 , Year ) & "..." & Date ( 12 , 31 , Year) )

Two points here:

1. Set Field is preferable to Insert Calculated Result because in order for Insert Calculated Result to work, you must be on a layout which displays the field being inserted into. Set Field works from any layout.

2. The Case statement is a very nice little function, which basically says, "If parameter1 is true, return result1; if parameter2 is true, return result2; ... ; if parameterX is true, return resultX; otherwise, return resultY."

Case ( parameter1 , result1 , parameter2 , result2 , ... , parameterX , resultX , resultY )

HTH.

Jerry

[M.A]

I removed the quotes around the 'Year' and I am getting this "3/11/2224...?" in the field1. Not good.

But Thanks for telling me to use Case, instead of If-Else. It was a lot easier.

After I completed the Case statement and clicked OK, it gave me error about the space between each of the start date of the range and the ampersand [ear ) & "... ]. When I deleted the space, it was happy. And when I created that space again, it did not complain..... Well, when executed the script, it is placing only the start date of the range and nothing after that in "field1".

I wonder whats going on !

[-Queue-]

Jerry is on fire today. But I have a few suggestions also. I don't believe Set Field will work in this situation even if you add DateToText( ) around the Date( ) statements. Unless you are using version 7, I think Insert Calculated Result is necessary. Set Field forces the data to be returned in the same format as the field being set. If you are setting a date field, then the calculation is converted to a date, which doesn't make sense, and the result is a question mark in the field. Also, you would first need to set a global with the calculated result, since calculation fields have no data when in Find Mode. And lastly, I would tweak the calculation a little to make it more efficient and dynamic.

DateToText(Date( (Position( "JanFebMarAprMayJunJulAugSepOctNovDec", Left( Month, 3 ), 0, 1 ) + 2) / 3, 1, Year )) & "..." & DateToText(Date( (Position( "JanFebMarAprMayJunJulAugSepOctNovDec", Left( Month, 3 ), 0, 1 ) + 2) / 3 + 1, 0, Year ))

No Ifs or Cases are necessary, if you use this type of calculation.

[QuinTech]

Thanks for noticing, JT... some days you just hit the ground running.

And thanks for refining my code!

[M.A]

Who is Jerry? .....

Queue, your one line code was just amazing. It worked like a charm !

You dont know how much time and effort I had put to make this working but to no success. But many thanks to you. That was just a blast.

Just out of curiosity, why this line of code (below) never worked ?

Insert calculated Result[select, "field1", "DateToText("1/1/" & Year) & "..." & DateToText("1/31/" & Year)]

Thanks

[-Queue-]

"1/1/" & Year isn't a date, so DateToText won't work on it. You would need a TextToDate first, then a DateToText, like DateToText(TextToDate("1/1/" & Year)). It would probably be cleaner, however, to use DateToText(Date( 1, 1, Year )).

Glad we could help.

[QuinTech]

One bad post and i'm already a nobody.

[Madwolfie]

I have been following this with interest as I wish to do something similar, but am finding it very taxing!

I have a report with a list of invoices and a summary of their total. What I would like is: a button to run a script to produce a summary total of the invoices for a specified date range (this would be either a certain month or a full year between specified dates).

I don't mind using either two user entry fields to be able to enter two dates for a date range or a pop up value list system as in this thread (although that is less precise).

Hope you can help

Cheers

[-Queue-]

You need two global date fields on their own layout (a 'find' layout), with a button set to Resume Script, and a layout containing the date field to be set. Your script will look something like this.

Allow User Abort [Off] 

Set Error Capture [On] 

Enter Browse Mode [ ] 

Set Field [globaldate1, TextToDate("")] 

Set Field [globaldate2, TextToDate("")] 

Go to Layout [one with global date fields and resume button] 

Pause/Resume Script [indefinitely] 

If [not (IsEmpty(globaldate1) or IsEmpty(globaldate2))] <-- ensure both fields are not empty

  Go to Layout [one with invoice date field]

  Enter Find Mode [ ]

  Insert Calculated Result [invoicedate, DateToText(globaldate1) & "..." & DateToText(globaldate2)]

  Perform Find [ ] 

  If [status(CurrentFoundCount)] 

    Go to Layout [invoice summary layout] 

    Sort [Restore, No dialog]

    Print Setup [Restore, No dialog]

    Enter Preview Mode [Pause]

    Enter Browse Mode 

  Else 

    Show Custom Dialog ["No invoices could be found for the supplied date range."]

    Show All Records

  End If

Else 

  Show Custom Dialog ["You did not enter a date range."]

End If

Go to Layout [original]

[Madwolfie]

Hi

Thanks for that - a few teethign probs, the button doesn't yet run the script, so I used the scriptmenu to run it.

I don't have an option to allow me nothing between the brackets in 'Perform Find []', I have ' ..[Replace FoundSet]', is that OK ? Also in the step for "PrintSetup [Restore, No Dialog], I don't have that option only 'Print' or 'PageSetup', so I used ordinary "Print' and that is exactly what it did (surprise) but the records printed were not in the date range that I entered.

I want it to insert the found records into a list report so that a summary of their totals can be seen (or insert the summary of their totalsdirectly into a field on the Invoice Summary Layout) - not to print the results via my laser printer.

I am amazed at the service this Forum performs and find all your help very useful - I just wish I was more adept at Filemaker myself....

Cheers

[Madwolfie]

Thanks for that - but I appear to have some problems - when I ran the script (from the Script menu) as the button didn't run it for me, it printed a record out of my laser printer ! I do not have some of the options in my script menus to follow yours precisely - vis., ' Perform Find [ ] ' my options don't seem to allow nothing between the brackets, so I used 'ReplaceFoundSet', then I do not have a 'Print Setup [Restore, No dialog]' - so I used just 'Print[]'

What I need it to do, is to insert the summary of the invoice totals between a chosen date range into a field on a layout.

I am always impressed by the service this Forum offers, I am just dismayed at my lack of FM ability, still I am learning every day !

Cheers

[-Queue-]

Replace Found Set is an option in version 6 that I don't have in version 5.5. I assume it would be fine, but I'm not really certain what its purpose is. Should any find replace the found set? Or is it an option between replace and constrain?

On Mac, Print Setup is Page Setup. Sorry about that.

If you don't want to print, remove the step.

Use a summary field, based on total of Invoice Amount, on your layout. It will only sum the found set. You can put it in either a subsummary part or a header/footer. It sounds as if you don't want any detailed data, so delete the body part and only use a subsummary or header/footer part, if that is indeed the case.

[Fitch]

Replace, Constrain, and Extend Found Set are options of the Perform Find() step in FM6.

Replace is the default, which replicates the Perform Find step in earlier versions.

[Madwolfie]

Hi

That is good - all seems to work fine - BUT it returns the Sum of the whole year! I entered the dates 01/04/2004 - 30/04/2004 and it gave me the whole year total. There are values for each day in April04, so it should work.

[-Queue-]

You might try making a clone of the file. If it was created using different system settings, then your date fields would be invalid using dd/mm/yyyy format.

You can test this by adding

Exit Record/Request

If [status(CurrentError)]

Show Custom Dialog ["Your dates are invalid."]

Set Field [globaldate1, TextToDate("")]

Set Field [globaldate2, TextToDate("")]

Go to Layout [Original]

Halt Script

End If

after the Pause/Resume Script step.

[Madwolfie]

Thanks again - but I went through all 2000 manually and changed them to dd/mm/yyyy, so I hope they are all correct now. Would it cause a problem to this script if there was already a Summary of the Total on another layout ? As I already have a list of Invoices and a running summary of them.

The idea of this step is to give me more control over what period of invoices is displayed.

Hope this helps