Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Trouble with filtering records
navnoob
Member Posts: 26
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.
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.
0
Comments
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)
MVP - Dynamics NAV
My BLOG
NAVERTICA a.s.