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
Mahmoud Coudsi 1Mahmoud Coudsi 1 

Compile Error: Invalid foreign key relationship: Account.Labs__r at line 35 column 47

Why am I getting this error?

Problem:

I'm trying to get the first occurrences value for each of the labs records on Salesforce from the Labs object and return it on the labs values custom fields (inital a1c value, inital LDL value..etc.) on the Accounts records level.

P.S: 

Accounts and Labs have a parent-child relationship with accounts being the parents records and labs being the child records. 

Code:
 
/* Get the first occurrence value for each of the labs from the Labs object and return it on the labs value fields on Accounts record leve. */
Trigger GetInitialLabsValue on labs__c(after insert, after update) {
    Set<Id> accountIds = new Set<Id>();

for (labs__c lb : trigger.new) {
        accountIds.add(lb.Patient__c);
    }

//Elimnate the the accounts that don't have IDs for
    accountIds.remove(null);
    
//SOQL query that returns that initial weight value 
    if (!accountIds.isEmpty()) {
        List<Account> accountsToUpdate = new List<Account>(); 
        for (Account account : [
            SELECT Id,
                (
                    SELECT Specimen_Received__c, Value__c
                    From labs__r
                    WHERE Value__c != NULL
                    Order by Specimen_Received__c ASC
                    Limit 1
                )
            From Account
            Where Id in :accountIds
        ]) {
           
            //Declare a decimal variable to store initial labs value for each of the labs 
            Decimal IA1C = NULL;
            Decimal ILDL = NULL;
            Decimal IHDL = Null;
            Decimal ITrigl = NULL;
            
            // Get(0) to return the first element in the list value for each of the labs
            if (!account.Labs__r.isEmpty() && account.Labs__r.Observation_Description__c.contains('A1C') ) {
                IA1C = account.labs__r.get(0).Value__c;
            } Else if (account.Labs__r.isEmpty() && account.Labs__r.Observation_Description__c.contains('LDL')){
                 ILDL = account.labs__r.get(0).Value__c;
            } Else if (account.Labs__r.isEmpty() && account.Labs__r.Observation_Description__c.contains('HDL')){
                 IHDL = account.labs__r.get(0).Value__c;
            } Else if (account.Labs__r.isEmpty() && account.Labs__r.Observation_Description__c.contains('Trigl')){
                 ITrigl = account.labs__r.get(0).Value__c;}

accountsToUpdate.add(new Account(
                Id = account.Id,
                initial_A1c_labs_value__c = IA1C,
                initial_LDL_labs_value__c = ILDL,
                initial_HDL_labs_value__c = IHDL,
                initial_Trigl_labs_value__c = ITrigl
            ));
            
        }   
        
        Update accountsToUpdate;
        
        }
      }

 
Zuinglio Lopes Ribeiro JúniorZuinglio Lopes Ribeiro Júnior
Hello Mahmoud,

Be sure "Labs" is a valid Child Relationship Name. If you access the details of your Account lookup field in the object Labs__c, you can see the Child Relationship Name as shown below:

User-added image

If the name displayed is not Labs, you have to change FROM labs__r to FROM [CorrectName]__r and in any other reference to it in the code.

Furthermore, check the profile permission for both objects, you must have at least Read permission in the Labs object and its fields and Edit permission in the Account object and its fields.

I also noticed that you didn't queried the field Observation_Description__c so I added it to the query and changed a few lines:
 
/* Get the first occurrence value for each of the labs from the Labs object and return it on the labs value fields on Accounts record leve. */
Trigger GetInitialLabsValue on labs__c(after insert, after update) {
    
	Set<Id> accountIds = new Set<Id>();

	for (Labs__c lb : Trigger.new) {
		if (lb.Patient__c != null)
			accountIds.add(lb.Patient__c);
	}

	List<Account> accountsToUpdate = new List<Account>();
	
	for (Account account : [
							SELECT Id,
									(
										SELECT 		Observation_Description__c,
													Value__c
										FROM 		labs__r
										WHERE 		Value__c != NULL
										ORDER BY 	Specimen_Received__c ASC
										LIMIT 1
									)
							FROM 	Account
							WHERE 	Id IN :accountIds
	]) {
	   
		//Declare a decimal variable to store initial labs value for each of the labs 
		Decimal IA1C 	= null;
		Decimal ILDL	= null;
		Decimal IHDL    = null;
		Decimal ITrigl 	= null;
		
		if (!account.Labs__r.isEmpty()) {
			
			Labs__c lab = account.labs__r.get(0);
			
			// Get(0) to return the first element in the list value for each of the labs
			if (lab.Observation_Description__c.contains('A1C') ) {
				IA1C = lab.Value__c;
			} else if (lab.Observation_Description__c.contains('LDL')) {
				ILDL = lab.Value__c;
			} else if (lab.Observation_Description__c.contains('HDL')) {
				IHDL = lab.Value__c;
			} else if (lab.Observation_Description__c.contains('Trigl')) {
				ITrigl = lab.Value__c;
			}

			accountsToUpdate.add(new Account(
				Id 							= account.Id,
				initial_A1c_labs_value__c 	= IA1C,
				initial_LDL_labs_value__c 	= ILDL,
				initial_HDL_labs_value__c 	= IHDL,
				initial_Trigl_labs_value__c = ITrigl
			));
		}
	}   
	
	if (!accountsToUpdate.isEmpty())
		update accountsToUpdate;
			
}



Hope to have helped!

Regards.

Don't forget to mark your thread as 'SOLVED' with the answer that best helps you.