function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
SKTSKT 

How to get Parent Ids of User

I have a requirement to get all the Project Ids of Resource under User Detail page.

Below is my Object Schema:
Parent: Project (Field: ProjectId)
Child: Timecard (Field: Project__c, Resource_Name__c)


Now, if the Resource Name under timecard matches with User Name, the Project Ids from that timecard should be stored on User detail page separated by a semicolon.
Below is my logic that I have achieved so far:
 
list<user> user = [SELECT  name from User where isactive = true]; 
list<string> str = new list<string>();
for(user u1 : user){
    str.add(u1.name);
}

List <Timecard__c> timecard = [
  SELECT ID,Project__c,resource_name__c  
  FROM Timecard__c 
  Where Resource_name__c = :str  and Project__c != null and Resource_Name__c != null];
            
    set<string> allProsset = new set<string>();
    for (Timecard__c timecards : timecard) {
      allProsset.add(timecards.Project__c);
    }
    list<string>allProsList = new list<string>(allProsset);
    list<User> userupdates = new list<user>();
    
    for(User us : user){
     
        us.Project_Ids__c = String.join(allProsList,';');
        userupdates.add(us);
        }
        
    update userupdates;

Now when i execute the above logic, Project Ids under User record is getting updated with some random Project IDs but not from the timecard where the User name is matching. Instead of pulling all the project Ids that the resource is working under, I am seeing some random project ids


Is there anyway that I can update user records with the exact project ids that matches resource name with user name in timecard under a project?


 
Best Answer chosen by SKT
Maharajan CMaharajan C
Then you have to use a one time batch:

Batch Class:
 
global class batchUserProjectUpdate implements Database.Batchable<sObject> {

    global Database.QueryLocator start(Database.BatchableContext BC) {

        String query = 'SELECT name from User where isactive = true';

        return Database.getQueryLocator(query);

    }


    global void execute(Database.BatchableContext BC, List<user> scope) {

        Set<string> str = new Set<string>();
		for(user u1 : scope){
			str.add(u1.name);
		}

		List <Timecard__c> timecard = [ SELECT ID,Project__c,resource_name__c  FROM Timecard__c 
									  Where Resource_name__c IN: str and Project__c != null and Resource_Name__c != null];
									  
									  
		Map<String, set<String>> usertcMap = new Map<String, set<String>> ();
		for (Timecard__c timecards : timecard) {
			if(!usertcMap.containsKey(timecards.Resource_name__c))
				usertcMap.put(timecards.Resource_name__c, new set<String>{timecards.Project__c});
			else
				usertcMap.get(timecards.Resource_name__c).add(timecards.Project__c);
		}

		list<User> userupdates = new list<user>();
		for(User us : user){ 
			if(usertcMap.containsKey(u1.name))
			{
				set<string> allProsSet = usertcMap.get(u1.name);
				list<string> allProsList = new list<string>(allProsset);
				us.Project_Ids__c = String.join(allProsList,';');
				userupdates.add(us);
			}
		}

		if(!userupdates.IsEmpty())
			update userupdates;

    }   


    global void finish(Database.BatchableContext BC) {

    }

}


To Execute the above batch :

We run the batch class based on the user count... So below the batch will pick 10 users per execution.

Run the below code in Dev Console or in Workbench.
 
Id batchJobId = Database.executeBatch(new batchUserProjectUpdate(), 10);


Thanks,
Maharajan.C

All Answers

mukesh guptamukesh gupta
Hi,

Please use below code:-
 
list<user> user = [SELECT  name from User where isactive = true]; 
list<string> str = new list<string>();
for(user u1 : user){
    str.add(u1.name);
}

List <Timecard__c> timecard = [
  SELECT ID,Project__c,resource_name__c  
  FROM Timecard__c 
  Where Resource_name__c IN: str  and Project__c != null and Resource_Name__c != null];
            
    set<string> allProsset = new set<string>();
    for (Timecard__c timecards : timecard) {
      allProsset.add(timecards.Project__c);
    }
    list<string>allProsList = new list<string>(allProsset);
    list<User> userupdates = new list<user>();
    
    for(User us : user){
     
        us.Project_Ids__c = String.join(allProsList,';');
        userupdates.add(us);
        }
        
    update userupdates;

if you need any assistanse, Please let me know!!

Kindly mark my solution as the best answer if it helps you.

Thanks
Mukesh 
SKTSKT
Could you please let me know the difference between IN: str and = :str. 
Maharajan CMaharajan C
Hi SKT,

Try the below code:
 
list<user> user = [SELECT name from User where isactive = true]; 

Set<string> str = new Set<string>();
for(user u1 : user){
    str.add(u1.name);
}

List <Timecard__c> timecard = [ SELECT ID,Project__c,resource_name__c  FROM Timecard__c 
							  Where Resource_name__c IN: str and Project__c != null and Resource_Name__c != null];
							  
							  
Map<String, List<String>> usertcMap = new Map<String, List<String>> ();
for (Timecard__c timecards : timecard) {
	if(!usertcMap.containsKey(timecards.Resource_name__c))
		usertcMap.put(timecards.Resource_name__c, new List<String>{timecards.Project__c});
	else
		usertcMap.get(timecards.Resource_name__c).add(timecards.Project__c);
}

list<User> userupdates = new list<user>();
for(User us : user){ 
	if(usertcMap.containsKey(u1.name))
	{
		list<string> allProsList = usertcMap.get(u1.name);
		us.Project_Ids__c = String.join(allProsList,';');
		userupdates.add(us);
	}
}
	
update userupdates;

Thanks,
Maharajan.C
​​​​​​​
SKTSKT

@Maharajan,

The issue here is that, under a project, there will be multiple timecard records filled by a resource. So, is there anyway we can remove duplicate project Ids from the Project Ids that we are passing to User object? 

Please suggest

Maharajan CMaharajan C
Try the below one:
 
list<user> user = [SELECT name from User where isactive = true]; 

Set<string> str = new Set<string>();
for(user u1 : user){
    str.add(u1.name);
}

List <Timecard__c> timecard = [ SELECT ID,Project__c,resource_name__c  FROM Timecard__c 
							  Where Resource_name__c IN: str and Project__c != null and Resource_Name__c != null];
							  
							  
Map<String, set<String>> usertcMap = new Map<String, set<String>> ();
for (Timecard__c timecards : timecard) {
	if(!usertcMap.containsKey(timecards.Resource_name__c))
		usertcMap.put(timecards.Resource_name__c, new set<String>{timecards.Project__c});
	else
		usertcMap.get(timecards.Resource_name__c).add(timecards.Project__c);
}

list<User> userupdates = new list<user>();
for(User us : user){ 
	if(usertcMap.containsKey(u1.name))
	{
		set<string> allProsSet = usertcMap.get(u1.name);
		list<string> allProsList = new list<string>(allProsset);
		us.Project_Ids__c = String.join(allProsList,';');
		userupdates.add(us);
	}
}

Thanks,
Maharajan.C
Maharajan CMaharajan C
Sorry Missed the Update Statement.
 
list<user> user = [SELECT name from User where isactive = true]; 

Set<string> str = new Set<string>();
for(user u1 : user){
    str.add(u1.name);
}

List <Timecard__c> timecard = [ SELECT ID,Project__c,resource_name__c  FROM Timecard__c 
							  Where Resource_name__c IN: str and Project__c != null and Resource_Name__c != null];
							  
							  
Map<String, set<String>> usertcMap = new Map<String, set<String>> ();
for (Timecard__c timecards : timecard) {
	if(!usertcMap.containsKey(timecards.Resource_name__c))
		usertcMap.put(timecards.Resource_name__c, new set<String>{timecards.Project__c});
	else
		usertcMap.get(timecards.Resource_name__c).add(timecards.Project__c);
}

list<User> userupdates = new list<user>();
for(User us : user){ 
	if(usertcMap.containsKey(u1.name))
	{
		set<string> allProsSet = usertcMap.get(u1.name);
		list<string> allProsList = new list<string>(allProsset);
		us.Project_Ids__c = String.join(allProsList,';');
		userupdates.add(us);
	}
}

if(!userupdates.IsEmpty())
    update userupdates;

 
SKTSKT

Yeah i got this update resolved..but when i run it for users for this year, I am able to update the users but when I tried to do it for users created in last year and so on, i am seeing the error as "System.LimitException: Too many query rows: 50001". 

But I have hardly 200 users but the issue seems to be from timecard query. Is there anyway that I can overcome this error? If i limit the timecard soql, i might miss few records for users.

Please suggest

Maharajan CMaharajan C
Then you have to use a one time batch:

Batch Class:
 
global class batchUserProjectUpdate implements Database.Batchable<sObject> {

    global Database.QueryLocator start(Database.BatchableContext BC) {

        String query = 'SELECT name from User where isactive = true';

        return Database.getQueryLocator(query);

    }


    global void execute(Database.BatchableContext BC, List<user> scope) {

        Set<string> str = new Set<string>();
		for(user u1 : scope){
			str.add(u1.name);
		}

		List <Timecard__c> timecard = [ SELECT ID,Project__c,resource_name__c  FROM Timecard__c 
									  Where Resource_name__c IN: str and Project__c != null and Resource_Name__c != null];
									  
									  
		Map<String, set<String>> usertcMap = new Map<String, set<String>> ();
		for (Timecard__c timecards : timecard) {
			if(!usertcMap.containsKey(timecards.Resource_name__c))
				usertcMap.put(timecards.Resource_name__c, new set<String>{timecards.Project__c});
			else
				usertcMap.get(timecards.Resource_name__c).add(timecards.Project__c);
		}

		list<User> userupdates = new list<user>();
		for(User us : user){ 
			if(usertcMap.containsKey(u1.name))
			{
				set<string> allProsSet = usertcMap.get(u1.name);
				list<string> allProsList = new list<string>(allProsset);
				us.Project_Ids__c = String.join(allProsList,';');
				userupdates.add(us);
			}
		}

		if(!userupdates.IsEmpty())
			update userupdates;

    }   


    global void finish(Database.BatchableContext BC) {

    }

}


To Execute the above batch :

We run the batch class based on the user count... So below the batch will pick 10 users per execution.

Run the below code in Dev Console or in Workbench.
 
Id batchJobId = Database.executeBatch(new batchUserProjectUpdate(), 10);


Thanks,
Maharajan.C
This was selected as the best answer
SKTSKT
Many Thanks Maharajan. I could able to update the records but I am seeing this error during the job run
caused by: System.DmlException: Update failed. First exception on row 0 with id <id>; first error: PICKLIST_INACTIVE_VALUES_EXCEEDED, picklist inactive limit exceeded: []

Could you please let me know what could be the reason behind this and could you suggest how to overcome this error?
Thanks!
 
Maharajan CMaharajan C
Hi SKT,

I think it's seems your org has more inactive picklist values. it's crossing the Salesforce Limit i think.

So your team have to perform the clean up activities on inactive picklist values across the objects.

Otherwise you can incresase the limit but it's recommended by Salesforce. Read the below article fully then take the decision.
https://help.salesforce.com/articleView?id=release-notes.rn_forcecom_fields_inactive_picklists.htm&release=230&type=5 (https://help.salesforce.com/articleView?id=release-notes.rn_forcecom_fields_inactive_picklists.htm&release=230&type=5)
https://help.salesforce.com/articleView?id=release-notes.rn_forcecom_fields_inactive_picklist.htm&type=5&release=232 (https://help.salesforce.com/articleView?id=release-notes.rn_forcecom_fields_inactive_picklist.htm&type=5&release=232)

I hopy my answers helped you... Can you please mark the best answer to close this thread...

Thanks,
Maharajan.C
Baya AdamBaya Adam
Information Transformation Service provides  web scraping Services that provides high-quality structured data to improve business outcomes and enable intelligent decision making,their Web scraping service allows you to scrape data from any websites and transfer web pages into an easy-to-use format such as Excel, CSV, JSON and many others.