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