Alpha Video Training
Results 1 to 8 of 8

Thread: help on a complicated filter

  1. #1
    Member
    Real Name
    Daniel Weiss
    Join Date
    May 2000
    Location
    Monsey, NY
    Posts
    506

    Default help on a complicated filter

    Would it be possible to use a filter based on multi fields but it should only include one record (the last one) based on one of the fields?
    example

    The inv_item table has the following fields
    Item_Code
    Ord_no
    Category
    Cust_id

    “Show_only” is a variable that holds a value for the Category field
    “C_custid” is a variable that holds a value for the Cust_id field

    I am using the “table.external_record_content_get” (to populate a list in a x_dialog)

    table.external_record_content_get("inv_item","Item_Code+Ord_No+ Category+recno()",
    "Item_Code”,"Category = "+quote(show_only)+".and. cust_id = "+quote(c_custid)+"")

    The filter needs to include all records that matches a given “Category” for a given “Cust_id” but only to include a unique record (last record – last Order_no or last recno()) for each “Item Code”

    A solution would be greatly appreciated
    Thank you,
    Daniel Weiss

  2. #2
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default RE: help on a complicated filter

    Are you saying you want a unique record for each "Item_code" or a unique record for each "Item_code+Category+Cust_id"?

    If you add .and. unique_key_value() to your filter expression, it will select unique records based on the order expression. I believe the genie will do this for you.

    So, if you want to select a unique "Item_code+Category+Cust_id", you will need to modify your sort order as appropriate. (I say 'as appropriate' because I don't know if all fields involved are character fields - but, if they aren't, they should be.)

    One last issue. I believe the unique_key_value() function will select the first rather than the last record so you would have to play around with the Descending option if you really need the last record. (I'm not sure how you would do that but it should be in the Help files somewhere).

  3. #3
    Member
    Real Name
    Daniel Weiss
    Join Date
    May 2000
    Location
    Monsey, NY
    Posts
    506

    Default RE: help on a complicated filter

    Thanks Cal
    Yes I want a unique record for each "Item_code" which is why the “unique_key_value()” won't work
    To understand what I am looking for I’ll explain
    I want the user to be able to lookup the last paid price for an item based on a category for a given customer and on the same time which items the customer purchases from that category type

    Daniel Weiss

  4. #4
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default RE: help on a complicated filter

    result_string=table.external_record_content_get("inv_item","Item_Code+Ord_No+ Category+recno()",
    "invert(ordno)”,"Category = "+quote(show_only)+".and. cust_id = "+quote(c_custid)+"")

    one_record=word(result_string,1,crlf())


    this puts the records into a crlf()-delimited list in the descending order of ordno, and then takes the first record in the list.

  5. #5
    Member
    Real Name
    Daniel Weiss
    Join Date
    May 2000
    Location
    Monsey, NY
    Posts
    506

    Default RE: help on a complicated filter

    Thanks Peter
    But if I understand you correctly this only returns the vary last record of the result, I need the last record for each item_code that matches the given category and cust_id
    I hope I am understood
    Thanks
    Daniel Weiss

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

    Default RE: help on a complicated filter

    Daniel,

    I suggest you step through the table using Dr. Wayne's filter to find each desired record, and copy it to another (empty) table.

    -- tom

  7. #7
    "Certified" Alphaholic
    Real Name
    Peter Wayne
    Join Date
    Apr 2000
    Posts
    1,728

    Default RE: help on a complicated filter

    i don't see how you can do it in one step.
    what you can do is to read the filtered table contents into an array (using array.initialize_from_table()). use the same syntax for order and filter you would use on external_record_content_get(). then you can sort the array in descending order. finally, you can cycle through the array and compare the values in each array element, and store only the first value of each identical item_number and cust_id.

  8. #8
    David Mason
    Guest

    Default RE: help on a complicated filter

    Correct Sir.

    I believe your method to be faster, but it may be simpler to the less learned to make a "temp" table and append only what is needed to that table in however many steps necessary. That temp table would have the appropriate report already made and would be emptied after the reports was printed. May be slower, but a lot more easily done for those unaware of arrays. Oh well, 2 more cents worth.

    Good Luck
    Dave

    PS - I even read you book

Similar Threads

  1. Complicated Lookup Problem
    By Charles Hoens in forum Alpha Five Version 6
    Replies: 2
    Last Post: 08-07-2005, 05:36 PM
  2. Complicated function code needed
    By Kevin G. Timberlake in forum Alpha Five Version 6
    Replies: 7
    Last Post: 07-25-2005, 02:07 PM
  3. Complicated Operation Help Needed
    By seidel1 in forum Alpha Five Version 6
    Replies: 3
    Last Post: 11-03-2004, 08:35 PM
  4. lost child records-complicated problem?
    By Andy Neufell in forum Alpha Five Version 1
    Replies: 2
    Last Post: 09-11-2002, 10:40 AM
  5. Complicated Sort Order
    By forskare in forum Alpha Five Version 5
    Replies: 4
    Last Post: 07-23-2002, 06:19 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
  •