In my current project, it was very surprising to find that some of the records had vanished from the foundset after sorting and listing them. I was shocked, what could I have done that causes loss of data. Then I tried to find the issue.
Issue:
If you are showing a list of records of a table in table view or list view, and you are listing some fields that are actually from another table and you are showing the field values by using a relation. And in this situation, if you are going to sort records basing upon the related field, then it may lead to data losses, especially, if some of the records don’t have any value for that related fields.
Here is an example to help understand this issue :
Let’s say you are showing a list of products, with fields such as, ProductID, Product Name, Category Name, Units In Stock, Price. And your product table has only the ‘Category_id’ field and you are populating the Category Name through a relation, ‘product_to_catagory’. And if you sort basing upon, ‘product_to_catagory.catagory_name’, then you will loose all the records from the product table that don’t have any category associated with them. This will happen even if you click the headers in the Table view.
Solution:
There are two solutions for this issue.
Method -1:
If you are sorting on a related value, MAKE SURE these fields always have some VALUE on them.
Method – 2:
Change the ‘Join Type’ of the relation to ‘left outer join’. That’s in our case, change the ‘Join Type’ of the relation ‘product_to_catagory’ to ‘left outer join’.