Trouble with filtering records

navnoob

Hi, I have a form that have a field call Item No. Is source expression is to a variable. And I want it so when people can lookup the Item table to fill and select the correct number. So in the TableRelation, i type in Item and that worked fine, so it display a a list of Items from the Item table.

Okay, problem is here. I am also working with the Item Cross Reference table. And I want it so when i click the "thingy icon" on the Item No. Field, it'll display all the Items in the Item table that has a relatonship to the Item Cross Reference Table. That way, I do not need to see any Items that is not in Cross Reference Table.

So I did this to the table relation filter, like: Item WHERE (No.=FIELD(Item No.))

I use the wizard so assume the syntax is correct.

It compiled fine, but when i run it, i get this:

Unable to copy field. The source field is not specificed:
Table: Item <-- <Unknown>
Field: No. <-- 1

I have no idea what went wrong.

So an example table:

Item

No. Description
123 Bike
345 Car
137 Boat


Item Cross Reference

Item No.
123
137

Such that when i click on the Item No. "icon thingy" from the table relation, when a table pops up, it should be:

No. Description
123 Bike
137 Boat

I hope someone can help me out and educate me. Thank you.

kine

there are more ways... but each way have pro and cons...

1) You can add boolean flowfield to the table 27 which say:
exist some entry in cross-ref table for this item???
Than you can filter the lookup on this field to have only records with this field true... (pro: simple way, may be slow because filtering to the flowfield)

2) You can add boolean flowfield which will say that exist cross-ref record for the item. This field will be updated each time you add or delete records from the cross-ref table (pro: fast filtering because you filter normal field and you can create key for the field - do not create this key on SQL server, it has very bad selectivity.)

3) You can loop through all records in tab 27 before you show the lookup and mark only the records with cross-ref record. Than show the list with Marked only... (pro: you do not need changes i tables. Cons: if you have many items, the loop will be long and it means, that client must transfer whole item table between server and client)