I have data from different dates that are listed by Week, Month, Quarter.
Exactly what do you mean by that? Is there a single date field in your portal's table and you want the user to see all related records where the date falls in a user selected week, month or quarter?
How do you plan to set up the layout for them to specify this? (There are several different ways and I can adapt the filter expression to work from the methods you choose to set up.)
Thanks for the reply.
Basically, team members log multiple activities in the database on a daily basis. For example:
Staff - Date - Task - Hours
Rick - 1 Jan 2012 - Laundry - 2 hours
Rick - 12 Feb 2012 - Lunch - 1 hour
Jo - 2 Jan 2012 - Laundry - 3 hours
Jo - 27 Feb 2012 - Lunch - 1 hour
The portion where they "log" the activies doesn't have a portion for week, month or quarter. The plan is to do that in the background probably by playing around with Date functions available in FileMaker like WeekOfYear, etc.
Now, there's a different layout where the staff can then select fields that have their name as the first filter and one of the ff as a second filter (each have their own separate field, the user just picks one and leaves the other 2 blank):
A. Week number, B. Month, C. Quarter
The plan is for the portal to show records that fall on the filters selected and show:
Date - Task - Hours
I hope this clarifies things a bit. :D Thanks in advance for any help that you could offer.
I've included a screen shot of a set of fields I use in some of my databases for pulling up reports of dated records just to show what might be used for this. While my scripts use the data in these global fields to create search criteria for performing a find, a portal filter could also use the same interface design.
Working just from what you describe, I don't see a field for specifying the year. I would guess that either your current concept assumes that these filters will work off of the current year or that you left out a field for specifying the year.
I'm going to assume that you also have a "year" field, but you can replace each reference to that field with Year ( get ( CurrentDate ) ) if you want the filter to always filter for this year's data.
For week number, I am also assuming you want to match to the value returned by WeekOfYear ( date ).
This portal filter should do the trick, but to avoid filtered portal refresh issues note my comments at the end of this post that discuss a method for a relationship that will smoothly update each time the user modifies one of these "filter" fields.
Let ( [ Y = Year ( PortalTO::Date ) ;
M = Month ( PortalTO::Date ) ] ;
Case ( Not IsEmpty ( YourTable::WeekNumber ) ; WeekOfYear ( PortalTO::Date ) = YourTable::WeekNumber and YourTable::year = Y ;
Not IsEmpty ( YourTable::Month ) ; YourTable::Month = M and YourTable::year = Y ;
YourTable::Quarter = Ceiling ( M / 3 ) and YourTable::Year = Y
) // case
) // Let
This filter refers to four fields that can be edited by the user: WeekNumber, Year, Month and Quarter. Unless you include them in the portal's relationship, you'll need to use a script (can be performed by script triggers on these four fields) with this script step: Refresh Window [Flush cached join results]. This works, but can result in very long delays while the screen refreshes in a number of circumstances so it is better to avoid using this script step when possible. You can avoid needing that script if you set up your relationship for the portal like this:
YourTable::WeekNumber x PortalTO::anyField AND
YourTable::Month x PortalTO::anyField AND
YourTable::Year x PortalTO::anyField AND
YourTable::Quarter x PortalTO::anyField AND
add the current fields already used in your relationship here
You can double click the relationship line to open a dialog box where you can add the additional fields and use the cartesian join operator (x) instead of =. It will not matter what field in the Portal's table occurrence that you specify for these added pairs of match fields.
Thanks for the help! This should help a lot :)