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
