Alpha Video Training
Results 1 to 3 of 3

Thread: "Simple" filter problem using week() Function to filter grid

  1. #1
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default "Simple" filter problem using week() Function to filter grid

    I have spent hours messing around with many possible ways to do this - I get so close and I know it is just a bracket or date conversion away from getting it.

    I am using a dialog on a page with a grid to filter the grid by the chosen dates which falls within a calendar week. The dialog contains a Date Calendar control. In the AfterValidate event, I have the following code (as an example):
    HTML Code:
    currentform.redirecttarget = "scheduler.a5w?grd_Scheduler_filter=Client_id=session.__protected__clientid.AND.Region_id=session.__protected__regionid.and.week(Date_of_service)=week(WEEKDATE)&grd_Scheduler_order=Date_of_service"
    If I substituted the WEEKDATE with an actual date and hit Submit on the dialog, it works just fine and filters the grid for those dates occuring withing that week. But when I try to use the calendar itself, it gives me an invalid filter error.

    So my question is:
    1) How do I express a date value from the dialog to be used with week() function in the filter?
    2) How do I deal with making sure it pulls from the correct year as well?

    I would appreciate any help or a better suggestion to filter the grid by selected weeks.

    Thanks, Mike Scholin

  2. #2
    VAR Pat Bremkamp's Avatar
    Real Name
    Pat Bremkamp
    Join Date
    Apr 2000
    Location
    Oregon, USA
    Posts
    2,625

    Default Re: "Simple" filter problem using week() Function to filter grid

    Grids take care of a lot of the conversions for you, but dialogs leave that up to you.

    values in the controls in a dialog are all character, so try
    week(ctod(WEEKDATE))

    Pat

  3. #3
    Member
    Real Name
    Michael Scholin
    Join Date
    May 2007
    Location
    Madison, WI
    Posts
    321

    Default Re: "Simple" filter problem using week() Function to filter grid

    Pat - thanks. You were correct about the ctod function, that got me closer. I really hate filters - there must be a cleaner way to write these things.

    Here is the solution that I finally stumbled onto that solves both the dialog calendar issue and the year issue:

    HTML Code:
    Filter = "week(Date_of_service) = week(ctod(\""+Weekdate+"\")) .and. year(date_of_service)=year(ctod(\""+Weekdate+"\")).AND.Client_id=session.__protected__clientid.AND.Region_id=session.__protected__regionid"
    currentform.redirecttarget = "scheduler.a5w?grd_Scheduler_filter="+Filter
    The reason I was having so much trouble was getting the aftervalidate to recognize the date field from the calendar on the dialog. It took all those screwy filter combination of (\""+ Field . . .etc) that I really have no idea as to what or why that works. But in the end and more than 8 hours later - I got them all in the right place.

    I hope this will help out someone else who may be searching for the answer to a similar problem.

Similar Threads

  1. Replies: 0
    Last Post: 09-16-2007, 11:07 AM
  2. "Filter Grid That is using more than one table"
    By radhika in forum Application Server Version 8
    Replies: 0
    Last Post: 05-25-2007, 06:40 AM
  3. "Invalid Report Filter Expression"
    By lemow in forum Alpha Five Version 8
    Replies: 22
    Last Post: 04-17-2007, 06:32 PM
  4. "Filter expression not logical" on Table lookup.
    By Graham Wickens in forum Alpha Five Version 6
    Replies: 2
    Last Post: 12-24-2005, 03:29 PM
  5. "Invalid report filter express"
    By Bill Warner in forum Alpha Five Version 6
    Replies: 9
    Last Post: 02-09-2005, 03:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •