in earlier blog post “caml query: lookup field by id not by value” i have mentioned how you can query lists for lookup field based on lookup field id.
SharePoint by default uses query by value for lookups , not by ID.
– Even in All views the queries are by values not by ID. This can be taken as BUG. As you can see in following example
Lets have one list “List1” having only single field “Title”
There are 4 items with first 3 one having same value, 3rd one is different by capital case
For List2 RefItem is Lookup of Title from List1
For test1, test2 , test3 is referring to DIFFERENT titles selected in lookup during data entry.
You can identify items by their case.
Now here is the buggy thing….
There is just modification in view is given where “group by” is selected with RefItem, and as you can see though test1, test2, test3 are having different values for RefItem, all are under one group. (Even see the ITEM1 which is in capital)
Logically they should be in different group because they are referring to different items actually.
One way to come out is having custom event handler which stops user adding duplicate items. In some cases you may need them. so Point is when CAML supports query by ID, while grouping items in grid SharePoint should do it using ID instead of value. and I believe its not hard to do it. MS can optimize the SPListView code behind to have this.