Nadya,

I would work with a cursor, built from the data your receive

As to the Amenities field, I would split it in 10 fields/cols

eg c1, c2, c3, ....

for each code, add an array to the form and fill that array with the possible values
eg thisform.c1[1], thisform.c2[1], ...

If you want a visual effect, each column can have a dynamicBackColor
eg 'iif(!empty(ascan(thisform.c1, table.c1)), rgb(192,192,192), rgb(255,255,128)'

next, the filter. Index seek would do if there are not that may records
If you want it fast, then there are two possibilities
a) make the array public
set filter to empty(ascan(Array_c1, c1)) or empty(ascan(Array_c2, c2))

b) make a filter statement (max 24 parameters for inlist())
local part1
part1 = '!Inlist(c1'

select table_of_c1
scan all
part1 = part1 + ',"' + rtrim(c1_code) + '"'
endscan
part1 = part1 + ')'

same for parts 2-10

set filter to &part1 or &part2 or &part4 ..... or &part10

to further speed it up you can add an index
index on c1 tag c1
....
index on c2 tag c2

my favourite is the inlist() which I use a lot for dynamically built filters

in addition, you can use the inlist() for the dynamicBackColor as opposed to the ascan

Success and a happy 2002





>Hi everybody,
>
>Let me give you some info about the problem first: Each year we receive town's data from Assessor's office for each town in MA and bunch of towns in CT. These files may be in different formats and contain data in different coding systems. We have two people, called "Converters", whos job is to convert these files into our database format.
>
>During this conversion they may incorrectly convert some code values into our codes. For instance, the table has BuiltStyle, RoofMat, RoofType, ParkType codes, which descriptions could be found in corresponding Lookups tables.
>
>We have an application, called BldMstr Editor. This application has a form with three pages. First page has a grid with several fields from the table (only essential fields, so no codes) and two other pages show record detail info. The last step of the job is a report, which will show, how many coded fields we have in a file, and if there is incorrect code value, it shows ******** in description. So, converter will see, that he (she) has 70 records with incorrect value in, say, amenities, and now he(she) wants to look up those records.
>
>So, here is the question: how can we filter incorrect codes? Usually these lookups tables contain only few records (less than 15), so we may create arrays for each of them.
>
>So, set filter to not indexseek(BuildStyle,.f.,'BuildStyle','Code) or what would be a good filter expression?
>
>One more complication for Amenities field - this field is 10 char long and it's a concatenation of codes, e.g. ABCUD, where each letter is a code. How would we filter those contain incorrect codes?
>
>Thanks a lot in advance.