Hi

I have created a sales report that takes data from a sales line item table.
I need to know how to show the date range on the report.

example: I search for all items in September by typing
01/09/2005...30/09/2005 in the date field of the sales line item table, then
I view the report. I need the report to show the above date range.

Any help would be greatly appreciated.

Nick

--



www.pop-culture.biz

# 1 for Movie, Music & Sport
Posters - Prints - Photographs - Collectables

Nick,

FileMaker does not offer a way to present "the current Find criteria"
automatically. For one thing, Finds can be quite complex and the narrative
of those would be quite verbose.

My recommendation would be to script the Find process:

1) User clicks a "Find Date Range" button
2) Enters their date range into a field or custom dialog and clicks "Go"
3) Your script
a) copies what was typed
b) uses it to perform the Find
c) uses it as the heading of the report
4) User sees the report with what they typed as the heading

You could get fancy with this in order to ensure correct entry of the Find
criteria, but this is the basic approach.

Bill

"Nick Talley" <nick (AT) pop-culture (DOT) biz> wrote

Thanks Bill

Guess its time to learn scripts LOL


"Bill Marriott" <wjm (AT) wjm (DOT) org> wrote

To twist your question a bit:

You could add a sub-summary to diplay calc fields om min_date and
max_date. That does not equal yor criteria, but would still be somewhat
informative.

In article <1h4aklz.1veitw0k535dsN%no (AT) way (DOT) dk>, no (AT) way (DOT) dk says...
But possibly counter productive... do you really want your boss/client
to pester you because he pulled a report from Jan 1 to March 31, but the
report is titled Jan 3 to March 30...

Nick,

Thought I would give you a leg up on things. The script below was designed
with FileMaker 8 Advanced. It uses a custom dialog to request a date range
for a report, based on "TransactionDate." It also has some basic error
handling.

Given:

TransactionDate (Date)

Add:

ReportStart (Date, Always Validate, Strict validation, Global)
ReportEnd (Date, Always Validate, Strict validation, Global)

And create this script:

Loop
Loop
Show Custom Dialog [ Title: "Select Date Range";
Message: "Please indicate the starting and ending dates for the
report.";
Buttons: "Find", "Cancel";
Input #1: YourDB::ReportStart, "Start Date";
Input #2: YourDB::ReportEnd, "End Date" ]
If [ Get(LastMessageChoice) = 2 ]
Set Field [ YourDB::ReportEnd; ""]
Set Field [ YourDB::ReportStart; "" ]
Exit Script [ ]
End If
Exit Loop If [ YourDB::ReportStart <= YourDB::ReportEnd ]
Show Custom Dialog [ Title: "Error";
Message: "The start date of the report must be less than or equal to
the end date of the report.";
Buttons: "Retry" ]
End Loop
#
Enter Find Mode [ ]
Set Field [ YourDB::TransactionDate; YourDB::ReportStart & "..." &
YourDB::ReportEnd ]
Set Error Capture [ On ]
Perform Find [ ]
Set Variable [ $MyLastError; Value:Get ( LastError ) ]
If [ $MyLastError = "0" ]
Exit Script [ ]
End If
If [ $MyLastError="401" ]
Show Custom Dialog [ Title: "No Records Found";
Message: "There are no records available in the range¶from " &
YourDB::ReportStart & " to "
& YourDB::ReportEnd & ".¶¶Would you like to modify the range, or
show all records?";
Buttons: "Modify...", "Show All", "Cancel" ]
If [ Get(LastMessageChoice) = 3 ]
Exit Script [ ]
End If
If [ Get(LastMessageChoice) = 2 ]
Show All Records
Sort Records [ Specified Sort Order: YourDB::TransactionDate;
ascending ] [ Restore; No dialog ]
Set Field [ YourDB::ReportEnd; GetNthRecord ( YourDB::TransactionDate;
Get ( FoundCount )) ]
Set Field [ YourDB::ReportStart; GetNthRecord (
YourDB::TransactionDate; 1) ]
Exit Script [ ]
End If
End If
End Loop

Note: the Always Validate option specified with the ReportStart and
ReportEnd fields enables FileMaker to manage error handling when users try
to enter values that are not dates into the dialog prompt during script
execution.

Once you create this script, you can put a button anywhere in your solution
which links to it. (The TransactionDate field does not even need to appear
on the layout).

Wherever you would like to display the date range, put the following text on
the layout:

Transactions in the range <<ReportStart>> to <<ReportEnd>>.

You can right-click this text label and choose "Date format..." to have the
dates appear any way you would like. For example, "Transactions in the range
October 1, 2005 to October 14, 2005."

English translation of this:

Loop A
Loop B (Keep asking for a date range until they give up or get it right)
Ask the user for two dates [FileMaker's Always Validate option makes
sure real dates are used]
Clear the report range values and exit the whole script if they click
Cancel
Exit Loop B (stop asking for a date) if StartDate is less than or equal
to the EndDate (i.e., a valid range)
[otherwise] Tell the user they screwed up [and go back to the start of
Loop B]
End Loop B
#
Enter Find Mode
Put "<StartDate>...<EndDate>" into the TransactionDate field...
Stop FileMaker from handling any errors, because we want the script to
control that
...now "click" Find
Put what happened into $MyLastError
If $MyLastError is zero then it worked okay and we're done. Exit the whole
script.
If $MyLastError is 401 then,
Tell the user there wasn't anything in that range, and ask them what
they want to do.
If they want to Cancel then exit the whole script.
If they want to show all records, then,
Go ahead and show all the records, then sort them ascending by the
TransactionDate
Put the date of the last record into ReportEnd
Put the date of the first record into ReportStart
[Just so the heading is accurate should someone try to print]
...and exit the whole script
End If
[Looks like they clicked Modify, so the whole thing will start over
again]
End If
End Loop A

Hope that is a helpful introduction to the world of scripts!

Bill

"Nick Talley" <nick (AT) pop-culture (DOT) biz> wrote