The filter expression that you specify would only allow records with one specific date to show, (A date 7 days earlier than today) not a range of records all within a specific date range such as "1 Weeks worth of records starting on Wednesday".
I suggest adding a calculation field, cWeekStartingOn, with a date result type and this expression:
DateOfEntry - DayOfWeek ( DateOfEntry ) + 4
All dates for a given week, from Wednesday to the following Tuesday will return the same date, the Wednesday date that marks the start of that week.
You can then set up a match field or a portal filter that specifies a particular Wednesday Date and the portal will then show only records for a given week.
Let ( D = Get ( CurrentDate ) ; D - DayOfWeek ( D ) + 4 ) = PortalTable::cWeekStaringOn
is a possible portal filter you could use if you want to show only the current week's records in the portal.
I tried your example and it only shows 5 days of entries rather than 7 days. Also in your example the dates its showing are still the previous Tuesday date as well. Is there a way to just show entries entered in from Wednesday(Date +6 days days)??? Now Im totally confused.
You are right, (Though you really do get 7 days, just not the correct 7 days with a common date). I was adapting Let ( D = Get ( CurrentDate ) ; D - DayOfWeek ( D ) + 1 )
which works when you want Sunday as the first day of the week. What I should have used was:
Let ( D = Date + 4 ; D - DayOfWeek ( D ) + 1 )
To get Wednesday as the first day of the week.
I've changed the portal filter to:
Let ( D =Get ( CurrentDate ) + 4 ; D - DayOfWeek ( D ) + 1 )
but it shows all records. I have no clue.
And it would as that evaluates as a non zero value--True--for all records.
I described two parts to this.
Part 1: define a calculation field named cWeekStartingOn in the portal's table:
DateOfEntry + 4 - DayOfWeek ( DateOfEntry + 4 ) + 1
And Then Part 2, a portal filter:
Let ( D =Get ( CurrentDate ) + 4 ; D ) = cWeekStartingOn
Apologies for not spelling out all the details here.
Thanks for your patients. now I get the formula, sorry I was getting confused with what goes where, now looking at it I can see how the calculations work. This now works as expected thank you :) I have so much to learn ;)
OK I have another question relating to this solution, the code entered to filter the portal, can this be used somewhere to filter a grid based layout rather than a portal? I tried pasting it into a quick find script but it doesn't seem to work?
I wouldn't use quick find for this. You should be able to use a script to perform a find where you use such an expression in a set field step to specify the find criteria.
For examples of scripted finds, see: Scripted Find Examples
Can anyone test this script for me on FilerMaker Pro 13v5?????
We just updated to the latest version of Filemaker v5 and now this script doesn't show any records in the portal, if I have try the same file on a previous version the portal works fine. It seems the update has made some changes somewhere. Any ideas?
The update shouldn't make a difference here. And just to be a bit picky, this is not a script--something that you create in Manage Scripts, these are calculations.