Filter table Lookup - field rules

[fddizon]

hello,

how to filter a lookup table values based on two or more fields?

supposed i have this fields:...

ProdCode:---1001-------------1002----------------1003
Category:---Hard Disk--------Hard Disk-----------Hard Disk
Brand:------Seagate----------Western Digital-----Maxtor
Model:------st3160zzz--------
Description:160GB SATA-------160GB SATA----------320GB SATA
FullDesc: (concatenated value of the 4 fields category, brand, model and description)

... and i wanted to search from another field of the table FullDesc containing the concatenated values of category, brand, model and description.

suppose i want to search hard disk 160GB...

filtered value should give prod code 1001 and 1002.

regards

[Mike Wilson]

Re: Filter table Lookup - field rules

Francis,
I don't think you need the fulldesc field. You can build your filter dynamically and filter the values you would like in the 4 fields in combination. Here is an example.

[fddizon]

Re: Filter table Lookup - field rules

Mike,

thanks for the response. your routine is very useful for a flat table. i forgot to mention that i was using the lookup for an invoicing system. searching for a product on a child table (invoice details).

as i see it, filter under table lookup will only filter the records based on the filter criteria, but not filter records as you type? you cannot search in between the fulldescription string.

for example:
Seagate 320gb hard disk st3320zz

suppose i want to type 320gb

it will return 0 records on the list.
Seagate should be typed first then the 320gb to show the product you want.

is it possible to use a customized search form for the lookup instead of the built-in field rule lookup table?

open a search form, search for the required product, fetch the product code and set it to the embedded browse (invoice details)..

see attached search form that i use to filter records.
this search form is only capable of searching in between but not on two criteria. haven't figure out yet how to do it.

Francis,
I don't think you need the fulldesc field. You can build your filter dynamically and filter the values you would like in the 4 fields in combination. Here is an example.

[Mike Wilson]

Re: Filter table Lookup - field rules

OK,
I seemed to be not clear or complete on you selection path as you originally described it. Sorry.

[fddizon]

Re: Filter table Lookup - field rules

Mike,

My apology for not making it clear.

i was offered to convert an inventory system created in clipper for a construction hardware store. he said he cannot use the invoicing system because he struggles to search items because the items has same properties but slight different on the other. if he search for instance an angular bar, made, (not familiar with the exact properties) thickness, size etc... he should know the code for him to be able to add invoice item. what he wants is that he can search/type on the lookup like angular bar, thickness, size or angular bar, size.

its the same problem i encounter with alpha's lookup table. no ability to search in between the strings or multiple parts of the string. have you created a search using another form for an invoice detail entries in replacement with the combo lookup table field rule?

thanks
and regards

[Mike Wilson]

Re: Filter table Lookup - field rules

Francis,
I am not sure why you can't just use the query genie? That allows multiword searches choosing whichever field you wish to search on. Other than that, look at this new example with the "search with words" button.

[JackForman]

Re: Filter table Lookup - field rules

I am wondering if Francis is not trying to do it from a dropdown field.
I am attaching the last sample from MIKE WILSON, but I did create a table with a lookup and a form InvoicelineFOrm

Under normal circumstances the more you type in a dropdown the narrow the results are, but you have to start matching from the first character.
Is there a wild card to force the field to search in the dropdown only the word entered before drop execution.
EX: In the product table there are 2 entries with the the word "digital" or one with "2800".
In other words I type 2800 in the dropdown field fulldescription, I should only see the records in the full description to match the entry 2800.
How to display those selected records from the dropdown?
Is it possible?

[fddizon]

Re: Filter table Lookup - field rules

Jack,

we have the same problem. last night, i created a form to enter invoice items. i have a button to open the form and on that form is a button to open another form with embedded browse with 3 input box below the browse to enter search parameters and a button to apply the filter. the 3 input boxes are session variables needed for the search. the search works fine but after fetching and closing the search form, the invoice item form does not refresh the products description and unit price of the product.

will post my sample later for everyone interested to help.

thanks

I am wondering if Francis is not trying to do it from a dropdown field.
I am attaching the last sample from MIKE WILSON, but I did create a table with a lookup and a form InvoicelineFOrm

Under normal circumstances the more you type in a dropdown the narrow the results are, but you have to start matching from the first character.
Is there a wild card to force the field to search in the dropdown only the word entered before drop execution.
EX: In the product table there are 2 entries with the the word "digital" or one with "2800".
In other words I type 2800 in the dropdown field fulldescription, I should only see the records in the full description to match the entry 2800.
How to display those selected records from the dropdown?
Is it possible?

[robert]

Re: Filter table Lookup - field rules

Without analyzing the problem in detail, I wonder if it wouldn't be beneficial to break the "full description" field into separate fields using an Update operation? This would make it easier for the lookups.

since the only separator is a space, names like western digital would have to be replaced by westerndigital using a search and replace operation so that you could separate the names from the size, etc.

[MikeC]

Re: Filter table Lookup - field rules

But robert, the first post states

FullDesc: (concatenated value of the 4 fields category, brand, model and description)

Francis,
Your latest sample will most likely be of use in finding a solution....but be very specific in what you want...and what you are currently getting as results.

[fddizon]

Re: Filter table Lookup - field rules

Jack,

you are right. im trying to search from a dropdown lookup field.

to all, i tried a workaround and it works sometime, but not most of the time.
im at home right now (having 53kbps wifi connection that is very slow, :( ) hopefully i can post my sample that needs refinement this coming monday or tuesday in our office.

regards and thanks

[Tom Cone Jr]

Re: Filter table Lookup - field rules

Francis, you should experiment with using the KEYWORDSI() function in a new indextag specification. If you define the index as

KEYWORDSI(full_desc)

The index will contain a separate entry for each "word" in the full_desc field, and these entries will be pre-sorted ascending (or descending).

So, if your calc field expression inserted a blank space between the concatenated field values, this kind of index would permit you to do a fetch_find based on any "word" in the full_desc field.

-- tom

[JackForman]

Re: Filter table Lookup - field rules

I looked at the definition of keywordsi() and keywords()
They both seem to do the same, but Tom can you give an example how to use it in the last sample DB posted.
Do you create a separate query?
Or can you add it to the dropdown field looking at the lookup table?

[Tom Cone Jr]

Re: Filter table Lookup - field rules

Jack, I'm thinking of a progressive lookup that uses a keywordsi() index. Wouldn't that do the trick?

[G Gabriel]

Re: Filter table Lookup - field rules

Seems unduly & unnecessarily complicating a simple matter.
Progressive lookup, that has been discussed and beaten to the ground in many other threads, is all you need.