Alpha Video Training
Results 1 to 17 of 17

Thread: Query filter help needed

  1. #1
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default Query filter help needed

    My table has username and datepaid fields. There are multiple usernames, ie, same are repeated possibly several times for each datepaid. What I need to do is extract the last date paid for each username in the table. I would like to do this via either an index or a query, preferrably, then display the records in a browse. It seems quite simple, but it eludes me. Any help would be appreciated.

    Tom Lyon

  2. #2
    "Certified" Alphaholic
    Real Name
    William Hanigsberg
    Join Date
    Apr 2000
    Location
    Toronto, ON
    Posts
    4,018

    Default RE: Query filter help needed

    Tom,

    If username is truly unique and datepaid is a date field you could query on username+cdate(datepaid) and fetch the last record.

    If there are a lot of records you could build an index so the query optimizes.

    Bill

  3. #3
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    "...and fetch the last record..."

    I must be dense. I didn't see how you got there. Could you explain? I'm looking for the last datepaid for each unique username in the table.

    Tom Lyon

  4. #4
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default RE: Query filter help needed

    I think you could make an index on username+invert(cdate(datepaid)) with a filter Dbmax or maximum or tablemax. I haven't read up on these or tried it but it would seem that you could use one of them to only include the greatest date paid for each username.

    I was thinking another way might be to do a query with the above order and then run a query on the query results to filter out lesser dates.



    Russ

  5. #5
    Steve Dentler
    Guest

    Default RE: Query filter help needed

    No promises here, but try this.

    Link the table to itself - one to one (last match)
    parent linking field - name
    child linking field - cdate(datepaid)

    Create a browse on the set.

    Run a query on the top level table, order my name, use the unique option (or use a unique index on username).

    query:
    f = browse.load("mybrowse")
    t.f:tables:usernames.this
    query.options = "u"
    query.order = "usernames"
    query.filter = ""
    t.query_create()
    f.resynch()
    f.show()
    f.activate()

    If you create a index, just display the browse. I'm not a 100% on this, but it seems like it should work.

  6. #6
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default RE: Query filter help needed

    Here's another option with minimal xbasic.
    create a dummy table with one field username
    append all unique usernames from your table
    link dummy to your table one to one, first matching.
    sort your table on username+invert(cdate(datepaid))
    It should then only display the newest date record for each user.
    The only thing is you will have to put an append unique to dummy operation on the save of the form used to enter username to keep the dummy up to date.

    Now on a form for the set display an embedded browse, it should only have the records you want.

    Russ

  7. #7
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    Hmm.

    I'm not following your logic at all, and as such, am unable to implement what you suggest. I can be rather dense at times and I bet this is one of those times :) I did try to follow your ideas, but get similar results to what I have been seeing. I know there is a way to do this...

    Tom Lyon

  8. #8
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    Clarification: previous message referenced Steve's post, not Russ'. I've yet to try Russ' suggestions.

    Tom Lyon

  9. #9
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default RE: Query filter help needed


    Tom,

    Here's one solution, using a script to mark the last payment records, and then running a query to display only marked records.

    -- tom

  10. #10
    Steve Dentler
    Guest

    Default RE: Query filter help needed

    See zip

  11. #11
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    I still don't follow, Steve.

    I am looking for, based on your sample data, 7 records out of the 20 records in payments to be displayed in the form named lastpaid. I see 20 records there as well. Was this your intention?

    If this method can work, it will be slick.

    Tom Lyon

  12. #12
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    That was my next step, too. You've worked it out nicely, and it works well. I was really hoping for a simpler solution, but we'll work with what we have to and get the job done.

    Really appreciate your hard work in this and all the issues to which you contribute.

    Tom Lyon

  13. #13
    Steve Dentler
    Guest

    Default RE: Query filter help needed

    My form only displays the seven records from the DB I created. I just downloaded my own zip and it, too, only displays seven records. Perhaps someone else might try downloading the zip and see what they get. Can't explain your results.

  14. #14
    Steve Dentler
    Guest

    Default RE: Query filter help needed

    My form only displays the seven records from the DB I created. I just downloaded my own zip and it, too, only displays seven records. Perhaps someone else might try downloading the zip and see what they get. Can't explain your results.
    ----------------------------------------
    Yes, I can. You're using the wrong index when displaying the form "Lastpaid." Select the index, "Userpaid" by hitting control-I and choosing from the dropdown.

  15. #15
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    You are correct! Very cool!

    Nice work, Steve. Very good insight.

    Tom Lyon

  16. #16
    "Certified" Alphaholic forskare's Avatar
    Real Name
    Ken Nordin
    Join Date
    Apr 2000
    Location
    Walker, MN
    Posts
    7,545

    Default RE: Query filter help needed

    Hi Tom,

    I'm doing something similar which might work for you. Perhaps you're already using something like this.

    I run a report to see a list of my active cases and the last activity for each case and the date the activity took place. I built it on an inverted set with the CASE_NUMBR field on the parent and the CASE_NUMBR+INVERT(CDATE(DATEFIELD)) as the links on a one to one first match.

    You're using a date field so just need the common field to add to it. This could be put on a button along with the query example that Tom Cone suggested only you would not need to mark records.

    I haven't tried it but it should work.

    kenn

  17. #17
    Member
    Real Name
    Tom Lyon
    Join Date
    Apr 2000
    Posts
    610

    Default RE: Query filter help needed

    Yah, I was going to try that, but Steve Dentler's idea worked perfectly. I would never have thought of that on my own. You know how much I dislike sets, but this is one case where sets is good :)

    Tom Lyon

Similar Threads

  1. Query Filter
    By Louis Nickerson in forum Web Application Server v6
    Replies: 4
    Last Post: 03-08-2005, 07:07 AM
  2. Tablesum help needed - filter problems??
    By Jim Faliveno in forum Upgrading to Alpha Five from Alpha Four
    Replies: 5
    Last Post: 01-07-2004, 08:13 AM
  3. Show All Filter Help Needed
    By Alan Lucas in forum Alpha Five Version 5
    Replies: 6
    Last Post: 06-05-2003, 08:08 AM
  4. Query Help Needed
    By dtheo in forum Alpha Five Version 5
    Replies: 9
    Last Post: 06-02-2003, 05:02 AM
  5. Quick filter needed with API call
    By Jim Stanley in forum Alpha Five Version 4
    Replies: 0
    Last Post: 05-24-2000, 01:28 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
  •