function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nitish PisalNitish Pisal 

I am trying to get list of unique values of a field (Store__r.region__c) on custom object which has lookup on lead object

I want to get all unique values of Store__c, so that I can use it to querry my store object later (I can't use group by here). My query looks like  
List<lead> listLead = [ SELECT  Store__c FROM Lead WHERE status = 'Eligible'];
Set<lead> random2 = new Set<lead>();
random2.addAll(listLead);
System.debug(random2);
The problem here is I am getting result for random2 as follows. I only want a set which has Store__c and not Id and RecordTypeId for lead 
Lead: {Store__c=a0O1F000000R39cUAC, Id=00Q1F000002I6FFUA0, RecordTypeId=0121F000000B25UQAS}, 
Lead:{Store__c=a0O1F000000R4xqUAC, Id=00Q1F000002IBziUAG, RecordTypeId=0121F000000B25UQAS}}
Also, my next question is : how do I use this Set to query the Store__c object to get all unique Regions__c. (How do I iterate the list of stores in soql query) e.g select region__c from Store__c where id=  set of retrieved Store ids in previous query . Also, how efficient would it be?

Thanks
Best Answer chosen by Nitish Pisal
mritzimritzi
If you have to use Region__c to create a picklist on the Lightning component, do the following:
  
List<Store__c> listStore = [ SELECT  Id, Name, Region__c From Store__c Where Id IN: setStoreId LIMIT 50000];
//now you don't need map, collect all values of Region__c in a set of String and send it to the calling method in Lightning front-end controller.
Set<String> setRegion = new Set<String>();
//return NULL if no data has been retreived from the object
if(listStore == NULL || listStore.size() == 0)
    return NULL;
//otherwise create set and return it
for(Store__c store:listStore){
    setStore.add(store.Region__c);
}
return setStore;

If this solves your problem, please mark this as BEST ANSWER
 

All Answers

Narender Singh(Nads)Narender Singh(Nads)
Hi Nitish,
If you just want to store the IDs of related Store__c records then you can do something like this:
  
Set<id> random2 = new Set<id>();
List<lead> listLead = [ SELECT  Store__c FROM Lead WHERE status = 'Eligible'];
for(lead l : listlead){

     random2.add(l.Store__c);
}

System.debug(random2);

And for the second part, once you get the list of IDs, you can write a qeury like this: 
Store__c[] StoreList=[select region__c from store__c where Id in :random2];

 Regards,
Narender
Nitish PisalNitish Pisal
Thanks Narender, but for second part I want SET of regions that means i want unique list of regions, how do I do that
Narender Singh(Nads)Narender Singh(Nads)
Ohh, For that you can code like this: 
set<string> ListOfRegions=new set<string>();
for(store__c s: [select region__c from store__c where Id in :random2]){

     ListOfRegions.add(s.region__c);
}

Please mark a best answer if my answer helped you, so that others with similar problem can benefit from this post.

Thanks
mritzimritzi
Following code may help you.
Change filed/object Api names if required. Correct syntax error, if any 
List<lead> listLead = [ SELECT  Store__c FROM Lead WHERE status = 'Eligible' LIMIT 50000];
Set<Id> setStoreId = new Set<Id>();
for(Lead lead:listLead){
	if(lead.Store__c != NULL)
		setStoreId.add(lead.Store__c);
}
List<Store__c> listStore = [ SELECT  Id, Name, Region__c From Store__c Where Id IN: setStoreId LIMIT 50000];
//Id in map will store store Id, you change it to suit your needs (Set<String> -> Unique Regions) 
Map<Id, Set<String>> uniqueRegionMap = new Map<Id, Set<String>>();

for(Store__c store:listStore){
	if(!uniqueRegionMap.containsKey(store.Id))
		uniqueRegionMap.put(store.Id, new Set<String>());
	uniqueRegionMap.get(store.Id).add(store.Region__c);
}
// if you want to see the stored data
System.debug('stores with set of Unique Regions:' + JSON.serializepretty(uniqueRegionMap));

If this solves your problem, please mark this as BEST ANSWER

 
Nitish PisalNitish Pisal

Hi Mritzi,

can I return the map to lightning component which has picklist to display the region names we fetched ? If not then I will have to return list of regions :(
mritzimritzi
If you have to use Region__c to create a picklist on the Lightning component, do the following:
  
List<Store__c> listStore = [ SELECT  Id, Name, Region__c From Store__c Where Id IN: setStoreId LIMIT 50000];
//now you don't need map, collect all values of Region__c in a set of String and send it to the calling method in Lightning front-end controller.
Set<String> setRegion = new Set<String>();
//return NULL if no data has been retreived from the object
if(listStore == NULL || listStore.size() == 0)
    return NULL;
//otherwise create set and return it
for(Store__c store:listStore){
    setStore.add(store.Region__c);
}
return setStore;

If this solves your problem, please mark this as BEST ANSWER
 
This was selected as the best answer