Finding Variable Date Ranges

My dilemma is as follows: I've created a client database which tracks
when a client checks in (via "start date") and when a client checks
out (via "end date"). This was working fine for me until my boss
asked me to generate a report on how many clients were in the building
today. You probably know where this is going, as I'm not sure how to
generate a report on clients in the building today based on the
"Start" and "End" dates.

The other problem I'm having is how to include clients that may have
been in the building prior to a set date range. For example, if I
need to generate a report on clients who were in the building for the
month of September, it will not include clients who may have come here
on August 31st and who may have left on October 1st, because I'm doing
a multiple Find on "Start Date" being on or after September 1st and my
second request is for a "End Date" being on or before September 30th.

What I need is a way to generate a client count report based on one
day, one month, quarterly, and yearly.

Can you help me find a solution to this problem?

Thank you

--

Re: Finding Variable Date Ranges

Are you familiar with building relationships via multiline keys?

Then all you need is a function with generates a date range like
in this example

start [date]
2/1/2008

end [date]
2/3/2008

result [text]
2/1/2008
2/2/2008
2/3/2008

If you are working with FileMaker Advanced, you can use a custom function
for this job. Search at briandunning.com.

-jens

--
http://jensteich.de

Re: Finding Variable Date Ranges

On Jan 9, 6:06 pm, Jens Teich wrote:
> Are you familiar with building relationships via multiline keys?
>
> Then all you need is a function with generates a date range like
> in this example
>
> start [date]
> 2/1/2008
>
> end [date]
> 2/3/2008
>
> result [text]
> 2/1/2008
> 2/2/2008
> 2/3/2008
>
> If you are working with FileMaker Advanced, you can use a custom function
> for this job. Search at briandunning.com.
>
> -jens
>
> --http://jensteich.de

Jens,

I am not familiar with building relationships via multiline keys. I
am using Filemaker Pro 9.0v1. Thanks for the search tip . . .

Re: Finding Variable Date Ranges

>> start [date]
>> 2/1/2008
>>
>> end [date]
>> 2/3/2008
>>
>> result [text]
>> 2/1/2008
>> 2/2/2008
>> 2/3/2008
>>
>> If you are working with FileMaker Advanced, you can use a custom function
>> for this job. Search at briandunning.com.
>>
>> -jens
>>
>> --http://jensteich.de
>
> Jens,
>
> I am not familiar with building relationships via multiline keys. I
> am using Filemaker Pro 9.0v1. Thanks for the search tip . . .

A multiline field is a textfield with multiple lines separated by
linefeed.

If you use this field as a key in a relationship FileMaker will treat
this a bit special. Other databases, SQL-databases for example, would
try to match the whole field with all the lines.

FileMaker treats every line as a key of its own. So you can establish
a relationship to the example result field above with text fields
with the content

2/2/2008

or even

2/3/2008
2/4/2008

and so on.

-Jens

--
http://jensteich.de

Re: Finding Variable Date Ranges

On Jan 9, 6:26 pm, Jens Teich wrote:
> >> start [date]
> >> 2/1/2008
>
> >> end [date]
> >> 2/3/2008
>
> >> result [text]
> >> 2/1/2008
> >> 2/2/2008
> >> 2/3/2008
>
> >> If you are working with FileMaker Advanced, you can use a custom function
> >> for this job. Search at briandunning.com.
>
> >> -jens
>
> >> --http://jensteich.de
>
> > Jens,
>
> > I am not familiar with building relationships via multiline keys. I
> > am using Filemaker Pro 9.0v1. Thanks for the search tip . . .
>
> A multiline field is a textfield with multiple lines separated by
> linefeed.
>
> If you use this field as a key in a relationship FileMaker will treat
> this a bit special. Other databases, SQL-databases for example, would
> try to match the whole field with all the lines.
>
> FileMaker treats every line as a key of its own. So you can establish
> a relationship to the example result field above with text fields
> with the content
>
> 2/2/2008
>
> or even
>
> 2/3/2008
> 2/4/2008
>
> and so on.
>
> -Jens
>
> --http://jensteich.de

Jens,

So now my question is how do I use a multiline field to let me know
that a client that began his/her stay here on 31AUG07 and ended his/
her stay on 01OCT07 when I query all persons in the building let's say
September? If I do a "FIND" on clients who came between 01SEP07 and
30SEP07 any client who came prior to 01SEP07 and who may have left
after 30SEP07 would not show up.

Thanks.

Re: Finding Variable Date Ranges

Eric writes:

> So now my question is how do I use a multiline field to let me know
> that a client that began his/her stay here on 31AUG07 and ended his/
> her stay on 01OCT07 when I query all persons in the building let's say
> September? If I do a "FIND" on clients who came between 01SEP07 and
> 30SEP07 any client who came prior to 01SEP07 and who may have left
> after 30SEP07 would not show up.

Eric,

Doing a FIND is the wrong idea, you need a relationship and a
GoToRelatedRecord scriptstep.

The required relationship has multilinekeys on both sides. On the
left side a global field with the FIND data, for example if you
search September it contains

01SEP07
02SEP07
....
30SEP07

[I switched the date formatting to you format, both keys need to
have the same formatting because you compare text not date data]

and you can establish a relationship to every date range which touches
this search range.

The selection of the records you intend to find actually is done via
GTRR.

-jens

--
http://jensteich.de

Re: Finding Variable Date Ranges

On Jan 9, 3:51 pm, Eric wrote:
> My dilemma is as follows: I've created a client database which tracks
> when a client checks in (via "start date") and when a client checks
> out (via "end date"). This was working fine for me until my boss
> asked me to generate a report on how many clients were in the building
> today. You probably know where this is going, as I'm not sure how to
> generate a report on clients in the building today based on the
> "Start" and "End" dates.
>
> The other problem I'm having is how to include clients that may have
> been in the building prior to a set date range. For example, if I
> need to generate a report on clients who were in the building for the
> month of September, it will not include clients who may have come here
> on August 31st and who may have left on October 1st, because I'm doing
> a multiple Find on "Start Date" being on or after September 1st and my
> second request is for a "End Date" being on or before September 30th.
>
> What I need is a way to generate a client count report based on one
> day, one month, quarterly, and yearly.
>
> Can you help me find a solution to this problem?
>
> Thank you
>
> --

You just need to construct your Find properly.

To find who's in the building today, your Find would be a SINGLE
request:

Start Date: <=//
End Date: >=//

To find who was in the building during ANY part of the month of
September, create this SINGLE request:

Start Date: <=9/30/2007
End Date: >=9/1/2007


To find who was in the building during the ENTIRE month of September,
create this SINGLE request:

Start Date: <=9/1/2007
End Date: >=9/30/2007


To generate reports, you'll need to put these find requests into
scripts, using global fields to tell the script what dates you want to
plug in.

Re: Finding Variable Date Ranges

In article
<384a5a2b-a895-4643-bcde-8cb6007c7db4@v29g2000hsf.googlegroups.com>,
Eric wrote:

> My dilemma is as follows: I've created a client database which tracks
> when a client checks in (via "start date") and when a client checks
> out (via "end date"). This was working fine for me until my boss
> asked me to generate a report on how many clients were in the building
> today. You probably know where this is going, as I'm not sure how to
> generate a report on clients in the building today based on the
> "Start" and "End" dates.
>
> The other problem I'm having is how to include clients that may have
> been in the building prior to a set date range. For example, if I
> need to generate a report on clients who were in the building for the
> month of September, it will not include clients who may have come here
> on August 31st and who may have left on October 1st, because I'm doing
> a multiple Find on "Start Date" being on or after September 1st and my
> second request is for a "End Date" being on or before September 30th.
>
> What I need is a way to generate a client count report based on one
> day, one month, quarterly, and yearly.
>
> Can you help me find a solution to this problem?
>
> Thank you

What you need is to use a "date range" Find in conjunction with an "OR"
Find. You can perform the Find manually by:

- Enter Find mode and FileMaker will as usual display the
blank "record" waiting for your Find Request.

- Click in the Start Date field and type in you Report's
Start Date, followed by three full stops / periods
(ie. "..." without quotes) and then the Report's End
Date.
eg. 9/1/2007...9/30/2007
The "..." is the "date range" Find symbol which tells
FileMaker that you want all the dates between these
two. The two dates can be anything - days, weeks,
months, years, decades apart. For a 'One Day' report
you only need to type the single date itself: 9/1/2007

- Next choose "Add New Request" from the Requests menu.
FileMaker will add another blank "record" waiting for
a second Find Request. You can move up and down the
Find Requests using the same method as moving through
records when in Browse mode (eg. the "book" icon in
FileMaker's side Status Bar).

- Click in the End Date field and type in the same date
range used above.
ie. 9/1/2007...9/30/2007 or 9/1/2007

- Finally, tell FileMaker to perform the Find.

Using the two Find Requests tells FileMaker you want any records which
match the first Request OR match the second request (which also
includes records matching both requests). The resulting Found Set of
records will be those clients that have their Start Date within the
date range OR their End Date within the date range OR both dates within
the date range.

As an aside, putting the date range into both the Start Date and End
Date fields of the same Find request is an "AND" Find. It tells
FileMaker that you want to Find the records which match the data in
both fields at the same time, ie. clients that have both the Start Date
AND the End Date within the date range.

As a second aside, there are a few special sumbols like this that can
be used in Find mode. You can find a list of them in the FileMaker Help
or by simply going into Find Mode and looking in the pop-up menu of
sumbols that is in the side Status Bar.


To "count" the number of records found, you can use a Summary field:
eg.
s_NumReportClients Summary Count of ClientName

where "ClientName" can be any field, as long as it ALWAYS has data in
it - you could use the Start Date field here instead.

Put this field into a Sub-summary or Leading / Trailing Grand Summary
part of the Report layout.



You can automate this Find process by using a script to perform exactly
what you do manually above via two Global Date fields into which the
user can enter the Report_StartDate and Report_EndDate, and then have a
button which performs a Script to Find the records and print the
report. The only hiccup is that FileMaker is picky about date
formatting so the Set Field command doesn't work and has to be a
replaced by the Insert Calculated Result command and changing the
user-entered dates into textual values. The script would be something
like:

Enter Find Mode []
Insert Calculated Result[Select, StartDate,
GetAsText(Report_StartDate) & "..." &
GetAsText(Report_EndDate)]
New Record/Request
Insert Calculated Result[Select, EndDate,
GetAsText(Report_StartDate) & "..." &
GetAsText(Report_EndDate)]
Set Error Capture [On]
Perform Find []
If [Get(CurrentFoundCount) = 0]
Beep
Message ["There are no records matching those dates"]
Else
Go To Layout [Report Layout]
Page Setup [Restore, No dialog]
Print []
Go To Layout [Data Entry]
End If

You didn't say which version of FileMaker, so some of the above Script
commands and functions may need changing.

One extra bonus of doing it this way is that you can easily use the
user-entered Report_StartDate and Report_EndDate fields as Merge Fields
inside a block of Text in the Header of the Report Layout to print
something like:

Clients for <> to <>

You can change the Date format of the text to print in whatever style
you like.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Finding Variable Date Ranges

On Jan 9, 10:46 pm, Helpful Harry
wrote:
> In article
> <384a5a2b-a895-4643-bcde-8cb6007c7...@v29g2000hsf.googlegroups.com>,
>
>
>
> Eric wrote:
> > My dilemma is as follows: I've created a client database which tracks
> > when a client checks in (via "start date") and when a client checks
> > out (via "end date"). This was working fine for me until my boss
> > asked me to generate a report on how many clients were in the building
> > today. You probably know where this is going, as I'm not sure how to
> > generate a report on clients in the building today based on the
> > "Start" and "End" dates.
>
> > The other problem I'm having is how to include clients that may have
> > been in the building prior to a set date range. For example, if I
> > need to generate a report on clients who were in the building for the
> > month of September, it will not include clients who may have come here
> > on August 31st and who may have left on October 1st, because I'm doing
> > a multiple Find on "Start Date" being on or after September 1st and my
> > second request is for a "End Date" being on or before September 30th.
>
> > What I need is a way to generate a client count report based on one
> > day, one month, quarterly, and yearly.
>
> > Can you help me find a solution to this problem?
>
> > Thank you
>
> What you need is to use a "date range" Find in conjunction with an "OR"
> Find. You can perform the Find manually by:
>
> - Enter Find mode and FileMaker will as usual display the
> blank "record" waiting for your Find Request.
>
> - Click in the Start Date field and type in you Report's
> Start Date, followed by three full stops / periods
> (ie. "..." without quotes) and then the Report's End
> Date.
> eg. 9/1/2007...9/30/2007
> The "..." is the "date range" Find symbol which tells
> FileMaker that you want all the dates between these
> two. The two dates can be anything - days, weeks,
> months, years, decades apart. For a 'One Day' report
> you only need to type the single date itself: 9/1/2007
>
> - Next choose "Add New Request" from the Requests menu.
> FileMaker will add another blank "record" waiting for
> a second Find Request. You can move up and down the
> Find Requests using the same method as moving through
> records when in Browse mode (eg. the "book" icon in
> FileMaker's side Status Bar).
>
> - Click in the End Date field and type in the same date
> range used above.
> ie. 9/1/2007...9/30/2007 or 9/1/2007
>
> - Finally, tell FileMaker to perform the Find.
>
> Using the two Find Requests tells FileMaker you want any records which
> match the first Request OR match the second request (which also
> includes records matching both requests). The resulting Found Set of
> records will be those clients that have their Start Date within the
> date range OR their End Date within the date range OR both dates within
> the date range.
>
> As an aside, putting the date range into both the Start Date and End
> Date fields of the same Find request is an "AND" Find. It tells
> FileMaker that you want to Find the records which match the data in
> both fields at the same time, ie. clients that have both the Start Date
> AND the End Date within the date range.
>
> As a second aside, there are a few special sumbols like this that can
> be used in Find mode. You can find a list of them in the FileMaker Help
> or by simply going into Find Mode and looking in the pop-up menu of
> sumbols that is in the side Status Bar.
>
> To "count" the number of records found, you can use a Summary field:
> eg.
> s_NumReportClients Summary Count of ClientName
>
> where "ClientName" can be any field, as long as it ALWAYS has data in
> it - you could use the Start Date field here instead.
>
> Put this field into a Sub-summary or Leading / Trailing Grand Summary
> part of the Report layout.
>
> You can automate this Find process by using a script to perform exactly
> what you do manually above via two Global Date fields into which the
> user can enter the Report_StartDate and Report_EndDate, and then have a
> button which performs a Script to Find the records and print the
> report. The only hiccup is that FileMaker is picky about date
> formatting so the Set Field command doesn't work and has to be a
> replaced by the Insert Calculated Result command and changing the
> user-entered dates into textual values. The script would be something
> like:
>
> Enter Find Mode []
> Insert Calculated Result[Select, StartDate,
> GetAsText(Report_StartDate) & "..." &
> GetAsText(Report_EndDate)]
> New Record/Request
> Insert Calculated Result[Select, EndDate,
> GetAsText(Report_StartDate) & "..." &
> GetAsText(Report_EndDate)]
> Set Error Capture [On]
> Perform Find []
> If [Get(CurrentFoundCount) = 0]
> Beep
> Message ["There are no records matching those dates"]
> Else
> Go To Layout [Report Layout]
> Page Setup [Restore, No dialog]
> Print []
> Go To Layout [Data Entry]
> End If
>
> You didn't say which version of FileMaker, so some of the above Script
> commands and functions may need changing.
>
> One extra bonus of doing it this way is that you can easily use the
> user-entered Report_StartDate and Report_EndDate fields as Merge Fields
> inside a block of Text in the Header of the Report Layout to print
> something like:
>
> Clients for <> to <>
>
> You can change the Date format of the text to print in whatever style
> you like.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Harry,

I understand your examples fully, however, I still need to find
Clients that are in the building in the month of September who may
have entered at the end of August and may have left at the beginning
of October. If I use your examples, this Client will not be counted
because his/her start and end dates were outside of September and
therefore their record would not be found if I used Start and Ending
dates within September. Example Find: Start Date 9/1/2007 ...
9/30/2007; 2nd Request Find End Date 9/1/2007 ... 9/30/2007, will not
find the person who came 8/31/2007 and left 10/1/2007.

Thanks.

Re: Finding Variable Date Ranges

In article
<5d11e2f8-2477-4945-b1df-cac3b44f553c@y5g2000hsf.googlegroups.com>,
Eric wrote:

> On Jan 9, 10:46 pm, Helpful Harry
> wrote:
> > In article
> > <384a5a2b-a895-4643-bcde-8cb6007c7...@v29g2000hsf.googlegroups.com>,
> >
> >
> >
> > Eric wrote:
> > > My dilemma is as follows: I've created a client database which tracks
> > > when a client checks in (via "start date") and when a client checks
> > > out (via "end date"). This was working fine for me until my boss
> > > asked me to generate a report on how many clients were in the building
> > > today. You probably know where this is going, as I'm not sure how to
> > > generate a report on clients in the building today based on the
> > > "Start" and "End" dates.
> >
> > > The other problem I'm having is how to include clients that may have
> > > been in the building prior to a set date range. For example, if I
> > > need to generate a report on clients who were in the building for the
> > > month of September, it will not include clients who may have come here
> > > on August 31st and who may have left on October 1st, because I'm doing
> > > a multiple Find on "Start Date" being on or after September 1st and my
> > > second request is for a "End Date" being on or before September 30th.
> >
> > > What I need is a way to generate a client count report based on one
> > > day, one month, quarterly, and yearly.
> >
> > > Can you help me find a solution to this problem?
> >
> > > Thank you
> >
> > What you need is to use a "date range" Find in conjunction with an "OR"
> > Find. You can perform the Find manually by:
> >
> > - Enter Find mode and FileMaker will as usual display the
> > blank "record" waiting for your Find Request.
> >
> > - Click in the Start Date field and type in you Report's
> > Start Date, followed by three full stops / periods
> > (ie. "..." without quotes) and then the Report's End
> > Date.
> > eg. 9/1/2007...9/30/2007
> > The "..." is the "date range" Find symbol which tells
> > FileMaker that you want all the dates between these
> > two. The two dates can be anything - days, weeks,
> > months, years, decades apart. For a 'One Day' report
> > you only need to type the single date itself: 9/1/2007
> >
> > - Next choose "Add New Request" from the Requests menu.
> > FileMaker will add another blank "record" waiting for
> > a second Find Request. You can move up and down the
> > Find Requests using the same method as moving through
> > records when in Browse mode (eg. the "book" icon in
> > FileMaker's side Status Bar).
> >
> > - Click in the End Date field and type in the same date
> > range used above.
> > ie. 9/1/2007...9/30/2007 or 9/1/2007
> >
> > - Finally, tell FileMaker to perform the Find.
> >
> > Using the two Find Requests tells FileMaker you want any records which
> > match the first Request OR match the second request (which also
> > includes records matching both requests). The resulting Found Set of
> > records will be those clients that have their Start Date within the
> > date range OR their End Date within the date range OR both dates within
> > the date range.
> >
> > As an aside, putting the date range into both the Start Date and End
> > Date fields of the same Find request is an "AND" Find. It tells
> > FileMaker that you want to Find the records which match the data in
> > both fields at the same time, ie. clients that have both the Start Date
> > AND the End Date within the date range.
> >
> > As a second aside, there are a few special sumbols like this that can
> > be used in Find mode. You can find a list of them in the FileMaker Help
> > or by simply going into Find Mode and looking in the pop-up menu of
> > sumbols that is in the side Status Bar.
> >
> > To "count" the number of records found, you can use a Summary field:
> > eg.
> > s_NumReportClients Summary Count of ClientName
> >
> > where "ClientName" can be any field, as long as it ALWAYS has data in
> > it - you could use the Start Date field here instead.
> >
> > Put this field into a Sub-summary or Leading / Trailing Grand Summary
> > part of the Report layout.
> >
> > You can automate this Find process by using a script to perform exactly
> > what you do manually above via two Global Date fields into which the
> > user can enter the Report_StartDate and Report_EndDate, and then have a
> > button which performs a Script to Find the records and print the
> > report. The only hiccup is that FileMaker is picky about date
> > formatting so the Set Field command doesn't work and has to be a
> > replaced by the Insert Calculated Result command and changing the
> > user-entered dates into textual values. The script would be something
> > like:
> >
> > Enter Find Mode []
> > Insert Calculated Result[Select, StartDate,
> > GetAsText(Report_StartDate) & "..." &
> > GetAsText(Report_EndDate)]
> > New Record/Request
> > Insert Calculated Result[Select, EndDate,
> > GetAsText(Report_StartDate) & "..." &
> > GetAsText(Report_EndDate)]
> > Set Error Capture [On]
> > Perform Find []
> > If [Get(CurrentFoundCount) = 0]
> > Beep
> > Message ["There are no records matching those dates"]
> > Else
> > Go To Layout [Report Layout]
> > Page Setup [Restore, No dialog]
> > Print []
> > Go To Layout [Data Entry]
> > End If
> >
> > You didn't say which version of FileMaker, so some of the above Script
> > commands and functions may need changing.
> >
> > One extra bonus of doing it this way is that you can easily use the
> > user-entered Report_StartDate and Report_EndDate fields as Merge Fields
> > inside a block of Text in the Header of the Report Layout to print
> > something like:
> >
> > Clients for <> to <>
> >
> > You can change the Date format of the text to print in whatever style
> > you like.
>
> Harry,
>
> I understand your examples fully, however, I still need to find
> Clients that are in the building in the month of September who may
> have entered at the end of August and may have left at the beginning
> of October. If I use your examples, this Client will not be counted
> because his/her start and end dates were outside of September and
> therefore their record would not be found if I used Start and Ending
> dates within September. Example Find: Start Date 9/1/2007 ...
> 9/30/2007; 2nd Request Find End Date 9/1/2007 ... 9/30/2007, will not
> find the person who came 8/31/2007 and left 10/1/2007.
>
> Thanks.

Oops! You're right, it won't find such records. Sorry.

You need to add a third Find request into which you type both:

into the StartDate field: " AND into the EndDate field: ">FindEndDate", without quotes

This will find those 'naughty' Clients who started before the report
period AND finished afterwards (ie. were still there during the
reporting period).

Similarly in the scripted version you would need an extra couple of
commands:

New Record/Request
Insert Calculated Result[Select, StartDate,
"<" & GetAsText(Report_StartDate)]
Insert Calculated Result[Select, EndDate,
">" & GetAsText(Report_EndDate)]

just before the "Set Error Capture [On]" command.



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Finding Variable Date Ranges

On Jan 12, 3:44 pm, Helpful Harry
wrote:
> In article
> <5d11e2f8-2477-4945-b1df-cac3b44f5...@y5g2000hsf.googlegroups.com>,
>
>
>
> Eric wrote:
> > On Jan 9, 10:46 pm, Helpful Harry
> > wrote:
> > > In article
> > > <384a5a2b-a895-4643-bcde-8cb6007c7...@v29g2000hsf.googlegroups.com>,
>
> > > Eric wrote:
> > > > My dilemma is as follows: I've created a client database which tracks
> > > > when a client checks in (via "start date") and when a client checks
> > > > out (via "end date"). This was working fine for me until my boss
> > > > asked me to generate a report on how many clients were in the building
> > > > today. You probably know where this is going, as I'm not sure how to
> > > > generate a report on clients in the building today based on the
> > > > "Start" and "End" dates.
>
> > > > The other problem I'm having is how to include clients that may have
> > > > been in the building prior to a set date range. For example, if I
> > > > need to generate a report on clients who were in the building for the
> > > > month of September, it will not include clients who may have come here
> > > > on August 31st and who may have left on October 1st, because I'm doing
> > > > a multiple Find on "Start Date" being on or after September 1st and my
> > > > second request is for a "End Date" being on or before September 30th.
>
> > > > What I need is a way to generate a client count report based on one
> > > > day, one month, quarterly, and yearly.
>
> > > > Can you help me find a solution to this problem?
>
> > > > Thank you
>
> > > What you need is to use a "date range" Find in conjunction with an "OR"
> > > Find. You can perform the Find manually by:
>
> > > - Enter Find mode and FileMaker will as usual display the
> > > blank "record" waiting for your Find Request.
>
> > > - Click in the Start Date field and type in you Report's
> > > Start Date, followed by three full stops / periods
> > > (ie. "..." without quotes) and then the Report's End
> > > Date.
> > > eg. 9/1/2007...9/30/2007
> > > The "..." is the "date range" Find symbol which tells
> > > FileMaker that you want all the dates between these
> > > two. The two dates can be anything - days, weeks,
> > > months, years, decades apart. For a 'One Day' report
> > > you only need to type the single date itself: 9/1/2007
>
> > > - Next choose "Add New Request" from the Requests menu.
> > > FileMaker will add another blank "record" waiting for
> > > a second Find Request. You can move up and down the
> > > Find Requests using the same method as moving through
> > > records when in Browse mode (eg. the "book" icon in
> > > FileMaker's side Status Bar).
>
> > > - Click in the End Date field and type in the same date
> > > range used above.
> > > ie. 9/1/2007...9/30/2007 or 9/1/2007
>
> > > - Finally, tell FileMaker to perform the Find.
>
> > > Using the two Find Requests tells FileMaker you want any records which
> > > match the first Request OR match the second request (which also
> > > includes records matching both requests). The resulting Found Set of
> > > records will be those clients that have their Start Date within the
> > > date range OR their End Date within the date range OR both dates within
> > > the date range.
>
> > > As an aside, putting the date range into both the Start Date and End
> > > Date fields of the same Find request is an "AND" Find. It tells
> > > FileMaker that you want to Find the records which match the data in
> > > both fields at the same time, ie. clients that have both the Start Date
> > > AND the End Date within the date range.
>
> > > As a second aside, there are a few special sumbols like this that can
> > > be used in Find mode. You can find a list of them in the FileMaker Help
> > > or by simply going into Find Mode and looking in the pop-up menu of
> > > sumbols that is in the side Status Bar.
>
> > > To "count" the number of records found, you can use a Summary field:
> > > eg.
> > > s_NumReportClients Summary Count of ClientName
>
> > > where "ClientName" can be any field, as long as it ALWAYS has data in
> > > it - you could use the Start Date field here instead.
>
> > > Put this field into a Sub-summary or Leading / Trailing Grand Summary
> > > part of the Report layout.
>
> > > You can automate this Find process by using a script to perform exactly
> > > what you do manually above via two Global Date fields into which the
> > > user can enter the Report_StartDate and Report_EndDate, and then have a
> > > button which performs a Script to Find the records and print the
> > > report. The only hiccup is that FileMaker is picky about date
> > > formatting so the Set Field command doesn't work and has to be a
> > > replaced by the Insert Calculated Result command and changing the
> > > user-entered dates into textual values. The script would be something
> > > like:
>
> > > Enter Find Mode []
> > > Insert Calculated Result[Select, StartDate,
> > > GetAsText(Report_StartDate) & "..." &
> > > GetAsText(Report_EndDate)]
> > > New Record/Request
> > > Insert Calculated Result[Select, EndDate,
> > > GetAsText(Report_StartDate) & "..." &
> > > GetAsText(Report_EndDate)]
> > > Set Error Capture [On]
> > > Perform Find []
> > > If [Get(CurrentFoundCount) = 0]
> > > Beep
> > > Message ["There are no records matching those dates"]
> > > Else
> > > Go To Layout [Report Layout]
> > > Page Setup [Restore, No dialog]
> > > Print []
> > > Go To Layout [Data Entry]
> > > End If
>
> > > You didn't say which version of FileMaker, so some of the above Script
> > > commands and functions may need changing.
>
> > > One extra bonus of doing it this way is that you can easily use the
> > > user-entered Report_StartDate and Report_EndDate fields as Merge Fields
> > > inside a block of Text in the Header of the Report Layout to print
> > > something like:
>
> > > Clients for <> to <>
>
> > > You can change the Date format of the text to print in whatever style
> > > you like.
>
> > Harry,
>
> > I understand your examples fully, however, I still need to find
> > Clients that are in the building in the month of September who may
> > have entered at the end of August and may have left at the beginning
> > of October. If I use your examples, this Client will not be counted
> > because his/her start and end dates were outside of September and
> > therefore their record would not be found if I used Start and Ending
> > dates within September. Example Find: Start Date 9/1/2007 ...
> > 9/30/2007; 2nd Request Find End Date 9/1/2007 ... 9/30/2007, will not
> > find the person who came 8/31/2007 and left 10/1/2007.
>
> > Thanks.
>
> Oops! You're right, it won't find such records. Sorry.
>
> You need to add a third Find request into which you type both:
>
> into the StartDate field: " > AND into the EndDate field: ">FindEndDate", without quotes
>
> This will find those 'naughty' Clients who started before the report
> period AND finished afterwards (ie. were still there during the
> reporting period).
>
> Similarly in the scripted version you would need an extra couple of
> commands:
>
> New Record/Request
> Insert Calculated Result[Select, StartDate,
> "<" & GetAsText(Report_StartDate)]
> Insert Calculated Result[Select, EndDate,
> ">" & GetAsText(Report_EndDate)]
>
> just before the "Set Error Capture [On]" command.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Harry,

Regarding the third request option, I never thought of this. I have
put it into practical use and it works. Can you explain to us how
this works?

Re: Finding Variable Date Ranges

On Jan 12, 3:44 pm, Helpful Harry
wrote:
> In article
> <5d11e2f8-2477-4945-b1df-cac3b44f5...@y5g2000hsf.googlegroups.com>,
>
>
>
> Eric wrote:
> > On Jan 9, 10:46 pm, Helpful Harry
> > wrote:
> > > In article
> > > <384a5a2b-a895-4643-bcde-8cb6007c7...@v29g2000hsf.googlegroups.com>,
>
> > > Eric wrote:
> > > > My dilemma is as follows: I've created a client database which tracks
> > > > when a client checks in (via "start date") and when a client checks
> > > > out (via "end date"). This was working fine for me until my boss
> > > > asked me to generate a report on how many clients were in the building
> > > > today. You probably know where this is going, as I'm not sure how to
> > > > generate a report on clients in the building today based on the
> > > > "Start" and "End" dates.
>
> > > > The other problem I'm having is how to include clients that may have
> > > > been in the building prior to a set date range. For example, if I
> > > > need to generate a report on clients who were in the building for the
> > > > month of September, it will not include clients who may have come here
> > > > on August 31st and who may have left on October 1st, because I'm doing
> > > > a multiple Find on "Start Date" being on or after September 1st and my
> > > > second request is for a "End Date" being on or before September 30th.
>
> > > > What I need is a way to generate a client count report based on one
> > > > day, one month, quarterly, and yearly.
>
> > > > Can you help me find a solution to this problem?
>
> > > > Thank you
>
> > > What you need is to use a "date range" Find in conjunction with an "OR"
> > > Find. You can perform the Find manually by:
>
> > > - Enter Find mode and FileMaker will as usual display the
> > > blank "record" waiting for your Find Request.
>
> > > - Click in the Start Date field and type in you Report's
> > > Start Date, followed by three full stops / periods
> > > (ie. "..." without quotes) and then the Report's End
> > > Date.
> > > eg. 9/1/2007...9/30/2007
> > > The "..." is the "date range" Find symbol which tells
> > > FileMaker that you want all the dates between these
> > > two. The two dates can be anything - days, weeks,
> > > months, years, decades apart. For a 'One Day' report
> > > you only need to type the single date itself: 9/1/2007
>
> > > - Next choose "Add New Request" from the Requests menu.
> > > FileMaker will add another blank "record" waiting for
> > > a second Find Request. You can move up and down the
> > > Find Requests using the same method as moving through
> > > records when in Browse mode (eg. the "book" icon in
> > > FileMaker's side Status Bar).
>
> > > - Click in the End Date field and type in the same date
> > > range used above.
> > > ie. 9/1/2007...9/30/2007 or 9/1/2007
>
> > > - Finally, tell FileMaker to perform the Find.
>
> > > Using the two Find Requests tells FileMaker you want any records which
> > > match the first Request OR match the second request (which also
> > > includes records matching both requests). The resulting Found Set of
> > > records will be those clients that have their Start Date within the
> > > date range OR their End Date within the date range OR both dates within
> > > the date range.
>
> > > As an aside, putting the date range into both the Start Date and End
> > > Date fields of the same Find request is an "AND" Find. It tells
> > > FileMaker that you want to Find the records which match the data in
> > > both fields at the same time, ie. clients that have both the Start Date
> > > AND the End Date within the date range.
>
> > > As a second aside, there are a few special sumbols like this that can
> > > be used in Find mode. You can find a list of them in the FileMaker Help
> > > or by simply going into Find Mode and looking in the pop-up menu of
> > > sumbols that is in the side Status Bar.
>
> > > To "count" the number of records found, you can use a Summary field:
> > > eg.
> > > s_NumReportClients Summary Count of ClientName
>
> > > where "ClientName" can be any field, as long as it ALWAYS has data in
> > > it - you could use the Start Date field here instead.
>
> > > Put this field into a Sub-summary or Leading / Trailing Grand Summary
> > > part of the Report layout.
>
> > > You can automate this Find process by using a script to perform exactly
> > > what you do manually above via two Global Date fields into which the
> > > user can enter the Report_StartDate and Report_EndDate, and then have a
> > > button which performs a Script to Find the records and print the
> > > report. The only hiccup is that FileMaker is picky about date
> > > formatting so the Set Field command doesn't work and has to be a
> > > replaced by the Insert Calculated Result command and changing the
> > > user-entered dates into textual values. The script would be something
> > > like:
>
> > > Enter Find Mode []
> > > Insert Calculated Result[Select, StartDate,
> > > GetAsText(Report_StartDate) & "..." &
> > > GetAsText(Report_EndDate)]
> > > New Record/Request
> > > Insert Calculated Result[Select, EndDate,
> > > GetAsText(Report_StartDate) & "..." &
> > > GetAsText(Report_EndDate)]
> > > Set Error Capture [On]
> > > Perform Find []
> > > If [Get(CurrentFoundCount) = 0]
> > > Beep
> > > Message ["There are no records matching those dates"]
> > > Else
> > > Go To Layout [Report Layout]
> > > Page Setup [Restore, No dialog]
> > > Print []
> > > Go To Layout [Data Entry]
> > > End If
>
> > > You didn't say which version of FileMaker, so some of the above Script
> > > commands and functions may need changing.
>
> > > One extra bonus of doing it this way is that you can easily use the
> > > user-entered Report_StartDate and Report_EndDate fields as Merge Fields
> > > inside a block of Text in the Header of the Report Layout to print
> > > something like:
>
> > > Clients for <> to <>
>
> > > You can change the Date format of the text to print in whatever style
> > > you like.
>
> > Harry,
>
> > I understand your examples fully, however, I still need to find
> > Clients that are in the building in the month of September who may
> > have entered at the end of August and may have left at the beginning
> > of October. If I use your examples, this Client will not be counted
> > because his/her start and end dates were outside of September and
> > therefore their record would not be found if I used Start and Ending
> > dates within September. Example Find: Start Date 9/1/2007 ...
> > 9/30/2007; 2nd Request Find End Date 9/1/2007 ... 9/30/2007, will not
> > find the person who came 8/31/2007 and left 10/1/2007.
>
> > Thanks.
>
> Oops! You're right, it won't find such records. Sorry.
>
> You need to add a third Find request into which you type both:
>
> into the StartDate field: " > AND into the EndDate field: ">FindEndDate", without quotes
>
> This will find those 'naughty' Clients who started before the report
> period AND finished afterwards (ie. were still there during the
> reporting period).
>
> Similarly in the scripted version you would need an extra couple of
> commands:
>
> New Record/Request
> Insert Calculated Result[Select, StartDate,
> "<" & GetAsText(Report_StartDate)]
> Insert Calculated Result[Select, EndDate,
> ">" & GetAsText(Report_EndDate)]
>
> just before the "Set Error Capture [On]" command.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Let me ask the question again in a way others can understand . . .

What I was looking for was a way to FIND clients who may have been in
the building during the month of September, but checked in (field
StartDate) on August 31, 2007 and checked out (field EndDate) on
October 1, 2007. The problem was with my FIND, I had a dual request
FIND that asked (FIND 1: field StartDate "9/1/2007...9/30/2007") and
(FIND 2: field EndDate: "9/1/2007...9/30/2007"). Harry you suggested
I add a third FIND (FIND 3: field StartDate "<9/1/2007" AND field
StartDate ">9/30/2007").

Harry, how is the third FIND capturing clients who were in the
building prior to 9/1/2007 and after 9/30/2007?

Thanks.

Eric.

Re: Finding Variable Date Ranges

Eric wrote:
> What I was looking for was a way to FIND clients who may have been in
> the building during the month of September, but checked in (field
> StartDate) on August 31, 2007 and checked out (field EndDate) on
> October 1, 2007. The problem was with my FIND, I had a dual request
> FIND that asked (FIND 1: field StartDate "9/1/2007...9/30/2007") and
> (FIND 2: field EndDate: "9/1/2007...9/30/2007"). Harry you suggested
> I add a third FIND (FIND 3: field StartDate "<9/1/2007" AND field
> StartDate ">9/30/2007").

I don't see the need for a third request. I would do it as:

Enter Find Mode[]
Set Field[StartDate, "<=" & GetAsText(Report_EndDate)]
Set Field[EndDate, ">=" & GetAsText(Report_StartDate)]
Perform Find []

You're finding everything that started before the end of the report, and
which ended some time after the beginning of the report.

Use of the Set Field step in this case requires that you are using FM 7
or later. For earlier versions, you must use the Insert Calculated
Result step and the field must be on the current layout.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Member, FileMaker Business Alliance

Re: Finding Variable Date Ranges

In article
,
Eric wrote:
>
> Let me ask the question again in a way others can understand . . .
>
> What I was looking for was a way to FIND clients who may have been in
> the building during the month of September, but checked in (field
> StartDate) on August 31, 2007 and checked out (field EndDate) on
> October 1, 2007.
>
> The problem was with my FIND, I had a dual request
> FIND that asked
> (FIND 1: field StartDate "9/1/2007...9/30/2007")
> and (FIND 2: field EndDate: "9/1/2007...9/30/2007").
>
> Harry you suggested I add a third FIND
> (FIND 3: field StartDate "<9/1/2007" AND field StartDate ">9/30/2007").
>
> Harry, how is the third FIND capturing clients who were in the
> building prior to 9/1/2007 and after 9/30/2007?
>
> Thanks.
>
> Eric.

Okie dokie. I'll give it a try - it's more difficult to explain than do.

You need to perform an "OR" Find (with a sub-"AND" Find), which means
three separate Find requests. Each Request is created by choosing 'Add
New Request' from the Requests menu when in Find Mode - they look like
blank records.

What you are telling FileMaker to do is Find all records where:

- the client began at any date within the report's
time period (Find 1), when these people finished
is not important since they where they for at least
part of the report's time priod.

OR - the client finished at any date within the report's
time period (Find 2), when these people started is
not important since they where they for at least part
of the report's time priod.

OR - the client began at any date BEFORE the report's
time period AND finished at any date AFTER the
report's time period (Find 3), these people were
there for the entire time period of the report, but
do not actually have a date within that time period
entered anywhere in their record's data.

Some clients may of course start and finish within the report's time
period. These people will match both Find 1 and Find 2, but since there
is only one record per client they will only be included once in the
resulting Found Set.

Find 3 itself is actually an "AND" Find - you want to find clients who
match both fields, ie. started before the time period AND finished
after the time period.

You can't separate Find 3 into two Find Request because then you are
making it an "OR" Find - telling FileMaker to find clients who started
before the time period OR clients who finsihed after the time period.
This is no good because some clients would:
- start before the period, but also finish before it,
- finsh after the time period, but also start after it.
neither of which are wanted to be included since they are never there
during the time period of the report.


Let me know if that is as clear as 5ft thick solid steel. :o)



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Finding Variable Date Ranges

In article <13otprhcoj50552@corp.supernews.com>, Howard Schlossberg
wrote:

> Eric wrote:
> > What I was looking for was a way to FIND clients who may have been in
> > the building during the month of September, but checked in (field
> > StartDate) on August 31, 2007 and checked out (field EndDate) on
> > October 1, 2007. The problem was with my FIND, I had a dual request
> > FIND that asked (FIND 1: field StartDate "9/1/2007...9/30/2007") and
> > (FIND 2: field EndDate: "9/1/2007...9/30/2007"). Harry you suggested
> > I add a third FIND (FIND 3: field StartDate "<9/1/2007" AND field
> > StartDate ">9/30/2007").
>
> I don't see the need for a third request. I would do it as:
>
> Enter Find Mode[]
> Set Field[StartDate, "<=" & GetAsText(Report_EndDate)]
> Set Field[EndDate, ">=" & GetAsText(Report_StartDate)]
> Perform Find []
>
> You're finding everything that started before the end of the report, and
> which ended some time after the beginning of the report.

Yep. After logging off yesterday and continuing to think about it, I
came to this same much simpler "AND" Find solution. :o)

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Finding Variable Date Ranges

On Jan 17, 2:42 pm, Helpful Harry
wrote:
> In article <13otprhcoj50...@corp.supernews.com>, Howard Schlossberg
>
>
>
> wrote:
> > Eric wrote:
> > > What I was looking for was a way to FIND clients who may have been in
> > > the building during the month of September, but checked in (field
> > > StartDate) on August 31, 2007 and checked out (field EndDate) on
> > > October 1, 2007. The problem was with my FIND, I had a dual request
> > > FIND that asked (FIND 1: field StartDate "9/1/2007...9/30/2007") and
> > > (FIND 2: field EndDate: "9/1/2007...9/30/2007"). Harry you suggested
> > > I add a third FIND (FIND 3: field StartDate "<9/1/2007" AND field
> > > StartDate ">9/30/2007").
>
> > I don't see the need for a third request. I would do it as:
>
> > Enter Find Mode[]
> > Set Field[StartDate, "<=" & GetAsText(Report_EndDate)]
> > Set Field[EndDate, ">=" & GetAsText(Report_StartDate)]
> > Perform Find []
>
> > You're finding everything that started before the end of the report, and
> > which ended some time after the beginning of the report.
>
> Yep. After logging off yesterday and continuing to think about it, I
> came to this same much simpler "AND" Find solution. :o)
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Thank you all (especially Harry), for your responses. I used Harry's
example and it works perfectly! (and Harry, thanks for the
explanation, I hope this was useful to others.)

Take care!