Reference Qualifier: How to get unique records in a reference field for a table which has duplicate values

[ashwanikumar]

Hi All,

i am trying to get unique records from a table which has duplicate records. I am able to achieve it by my script but it is taking a lot of time to execute. Following is my exact requirement:

1. Custom table has duplicate records for the users with the user ID.

2. I need to return recently created unique record for each user.

e.g. following table should return Record #2 for User A, #4 for user B and #5 for user C.

#	User Name	user id	Created (Date)
1	User A	00001	27/02/2017
2	User A	00001	27/03/2017
3	User A	00001	27/01/2017
4	User B	00002	27/03/2017
5	User C	00003	27/02/2017
6	User C	00003	27/01/2017

Following is my script:

//Glide the table , order by the user id and run query.
   var resource = new GlideRecord('table_name');
   resource.orderBy('user_id');
   resource.query();
   while (resource.next()){
//Glide again and add one more condition to filter out only those records which matches with resource user id. descending order by created and pust the recently created record
   var resource2 = new GlideRecord('table_name');
   resource2.addQuery('user_id', resource.user_id);
   resource2.orderByDesc('sys_created_on');
   resource2.query();
   if(resource2.next()){
   sysid += (',' + resource2.sys_id);
   }
   }

As I am gliding 2 times to get   the unique records and also gliding for all the duplicate records, script is taking a lot of time.

Please let me know what is the best approach to write reference qualifier for this case?

Thanks,

Kumar

[Jaspal Singh]

Hi Ashwani,

Did you try looking How to set the default search column on a list view   link.

[venkatiyer1]

Hi Ashwani,

Use GlideAggregate instead.