Friday 3 August 2018

FirstOnly in datasource and its alternative (using ROW_NUMBER())

When there’s a requirement to retrieve only the first record from datasource, we might think the “FirstOnly” property under the datasource table will comes in handy, but unfortunately it doesn’t.

I’ve created a View and set the datasource’s FirstOnly property to Yes, then created a class to select from this View, it still returns all the records.
Not sure how this property works, but that’s not the focus of this article.

Screenshot showing the “FirstOnly” property



As an alternative, we can create an additional field (RowNumber) and filter it using RowNumber = 1.

Eg. Given the sample tables below, the requirement is to shows only one contact number for the customer.

Customer
Contact
AccountNum
AccountNum
Locator
ABC
ABC
12345
DEF
ABC
34567
DEF
56789
DEF
78901
DEF
90123

We’ll start with creating the View method under the View.

public class QTQ_CustNonPrimaryPhoneView extends common
{
    public static server str rowNumByAccountNum()
    {
        return 'ROW_NUMBER() OVER (PARTITION BY T1.Partition, T1.AccountNum ORDER BY AccountNum)';
    }
}

Then create a String computed column, set the View Method with this method name.



Select from this View will product result as shown below.

AccountNum
Locator
RowNumByAccountNum
ABC
12345
1
ABC
34567
2
DEF
56789
1
DEF
78901
2
DEF
90123
3

You can easily filter it with “RowNumByAccountNum = 1” to retrieve only the first contact.

Eg.
When using it in code
select from QTQ_CustNonPrimaryPhoneView where QTQ_CustNonPrimaryPhoneView.RowNumByAccountNum = 1.

When using it in Query/View, set the Range by putting in the field ‘RowNumByAccountNum’ and set the value to 1