help on a complicated filter

[Daniel Weiss]

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

[CALocklin]

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).

[Daniel Weiss]

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

[Peter.Wayne]

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.

[Daniel Weiss]

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

[Tom Cone Jr]

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

[Peter.Wayne]

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.

[David Mason]

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