Question about the "Range" operator and a sub-summary report

[Hoib]

Using FM 13.1 Pro Advanced on Windows 7 Pro.  Fairly novice user.  Using no scripting at this point as I'm just trying to use FM native UI to see more clearly what I'm doing.  Yes, this screams for a script, however...

 

I have a TO containing 'individuals_TIMESHEETS'.  This TO has two fields called zs_HoursWorked (a Summary field) and date_Timesheet (a Date Field).  I'm using a sub-summary section to tally up total hours worked for each person.  On my layout, list view mode, up in the heading I'm in Find Mode, looking at 'date_Timesheet' attempting to enter a range that will give me timesheets between two dates.  In Find Mode I enter "6/2013...6/2014".  FM inserts automatically the string "6/*/2013...6/*/2014" into the field in find mode.  Go to Browse.  I have to then sort in the sub-summary based on the FullName_Reversed found in the INDIVIDUALS TO.

 

First thing I notice is that the Find criteria entered switches in Browse to a specific date usually the date found in the first INDIVIDUAL's record.  I thought it would preserve the Find request in Browse but I'm wrong obviously.  The second and more important thing is this:

 

First Find Request - in date_Timesheet - Find 6/2013...6/2014 - shows zs_HoursWorked down in sub-summary for John as 85.4 hours.  Clear it all out with show all records.

 

Second Find Request - in date_Timesheet - Find 1/2014...6/2014 - shows zs_HoursWorked down in sub-summary for John as 85.4 hours.  I know because I manually looked, that the hours should be less.

 

So from what I see my range spec of "..." isn't working and I don't know what FM wants to see there in order to filter down results from what appears to be the grand total of all hours worked.

 

What do I do to Find a date range and have the date range restrict the sub-summary to only what's between the two dates?

[comment]

First thing I notice is that the Find criteria entered switches in Browse to a specific date usually the date found in the first INDIVIDUAL's record.  I thought it would preserve the Find request in Browse but I'm wrong obviously.

 

There are no Find Requests in Browse mode. There are are only found records.  if you searched for all records in the year 2014, and the first record in 2014 has the date of February 15, then that is the earliest date you will see in Browse mode.

 

Perhaps you should enter your search criteria into two global date fields (gRangeStart and gRangeEnd), then have a script(!) create the find request out of these; that way you can display the range in Browse mode, too.

 

 

It's difficult to answer your second issue, because it's not clear what you expect. Obviously, not what you get. Perhaps you are not aware that when you enter "6/*/2013...6/*/2014" as the search criteria (in a file using the M/D/Y date format), Filemaker will find all records between 6/1/2013 and 6/30/2014, inclusive.

[Hoib]

OK, Let me see if I can explain the second part better.  FM is not finding a restricted found set between 1/1/2014 and 6/30/2014.  It is finding ALL records from 2003 through the present which is not what I want.  I only want records between the two dates.  If I leave the date blank in Find Mode, I get the same total hours (no restriction) that I get when filling in a date range.  That's what I don't understand:  I am asking it for a limited range, it's giving me the entire range with no restriction.  That's why I'm' suspecting that I'm setting up the range correctly in the Find.  I've read and re-read the help file numerous times...

 

Should date_TIMESHEET field be placed in another section, like the body section and somehow hide it when printing?  That seems very unlikely.  If I put it in the body then I get that field printed in the body.  I can't put it in the sub-summary as I mentioned because that doesn't go there.  I placed it in the header - any problem with that placement?

 

And, on the first part, I'll try to do this without a script and just create the two fields, put them on the layout and see what I get using another operator on each ie. greater than gRangeStart and less than gRangeEnd.  That should work.  I'll try it and see.

[comment]

FM is not finding a restricted found set between 1/1/2014 and 6/30/2014.  It is finding ALL records from 2003 through the present which is not what I want.

 

Could you be more clear here? Describe exactly what you entered as the search criteria, and which date was found that should not have been.

 

 

on the first part, I'll try to do this without a script and just create the two fields, put them on the layout and see what I get using another operator on each ie. greater than gRangeStart and less than gRangeEnd.  That should work.

 

I am afraid not. That won't work at all. You cannot enter the name of a field as the search criteria and expect Filemaker to understand that you mean it as a field reference; Filemaker will search for the literal text.

 

 

Should date_TIMESHEET field be placed in another section, like the body section and somehow hide it when printing?

 

This has nothing to do with finding the correct set of records.