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
Gabe MejiaGabe Mejia 

Return Text from a SOQL Query

I am trying to update the following 3 custom fields under my Member_Vendor_del__c custom object:

Vendor_ID__c
Member_Account_ID__c
Member_State__c

I have created the code below to update the Vendor_ID__c and Member_Account_ID__c

trigger VendorRepUpdateTrigger on Member_Vendor_del__c (before update) {
// Update the Vendor's ID, Member's ID, and Member's Physical State into the Member Vendor Record.    
    for (Member_Vendor_del__c u : trigger.new){
          if (u.Vendor_ID__c == null)
              u.Vendor_ID__c                 = u.Vendor__c;
              u.Member_Account_ID__c = u.Member__c;
  }
}

My issue is with getting Member_State__c to populate with the Physical State(API Field Name: BillingState) from the related member account. I have createdthe SOQL query below to return the string text value on the account but I keep getting the error "Illegal assignment from List to String"

// Update the Member's Physical State onto the Member Vendor Record.                       
     for (Member_Vendor_del__c u : trigger.new)
        if (u.Member_State__c == null)
                  u.Member_State__c = [Select BillingState
                                     From Account
                                     Where Account.Name = :u.Member__c];}

I can add .Id to the query and the error goes away but then the trigger wants to return a Salesforce ID rather than the 2 letter abbreviation for the State.

Question #1: What can I do to my SOQL query to return the text value rather than an ID?

Question #2: Can I add the SOQL query to the first part of the trigger? Something like:

trigger VendorRepUpdateTrigger on Member_Vendor_del__c (before update) {
// Update the Vendor's ID, Member's ID, and Member's Physical State into the Member Vendor Record.   
    for (Member_Vendor_del__c u : trigger.new){
          if (u.Vendor_ID__c == null)
              u.Vendor_ID__c         = u.Vendor__c;
              u.Member_Account_ID__c = u.Member__c;
              u.Member_State__c      = [Select Id, Account.BillingState
                                        From Account
                                           where Account_ID__c = :u.Member__c].Id;
 }
}
Best Answer chosen by Gabe Mejia
Joe BrodarJoe Brodar
Hey Gabe!

The first error, "Illegal assignment from List to String," i sbeing thrown because a SOQL query returns a list, and you are attempting to assign the returned List of Accounts into a String variable.

When you are expecting only 1 row to be returned, you can add the "LIMIT 1" modifier to the end of your query, which will then return a single Account record instead of a List: 
[SELECT BillingState FROM Account WHERE Name = :u.Member__c LIMIT 1]
With that modification, you can then call out the BillingState field, just as you did with the Id field: 
// if u.Member__c is the Name of the Account:
u.Member_State__c = [SELECT BillingState FROM Account WHERE Name = :u.Member__c LIMIT 1].BillingState;

// if u.Member__c is the Id of the Account:
u.Member_State__c = [SELECT BillingState FROM Account WHERE Id = :u.Member__c LIMIT 1].BillingState;
This will assign just the the String field "BillingState" into your u.Member_State__c field.

So the final trigger would be: 
trigger VendorRepUpdateTrigger on Member_Vendor_del__c (before update) 
{
// Update the Vendor's ID, Member's ID, and Member's Physical State into the Member Vendor Record.   
    for (Member_Vendor_del__c u : Trigger.new)
    {
        if (u.Vendor_ID__c == null)
        {
            u.Vendor_ID__c = u.Vendor__c;
            u.Member_Account_ID__c = u.Member__c;
            u.Member_State__c = [SELECT BillingState 
                FROM Account 
                WHERE Name = :u.Member__c 
                LIMIT 1].BillingState;
        }
    }
}
Let me know if this helps!

- Joe

All Answers

Joe BrodarJoe Brodar
Hey Gabe!

The first error, "Illegal assignment from List to String," i sbeing thrown because a SOQL query returns a list, and you are attempting to assign the returned List of Accounts into a String variable.

When you are expecting only 1 row to be returned, you can add the "LIMIT 1" modifier to the end of your query, which will then return a single Account record instead of a List: 
[SELECT BillingState FROM Account WHERE Name = :u.Member__c LIMIT 1]
With that modification, you can then call out the BillingState field, just as you did with the Id field: 
// if u.Member__c is the Name of the Account:
u.Member_State__c = [SELECT BillingState FROM Account WHERE Name = :u.Member__c LIMIT 1].BillingState;

// if u.Member__c is the Id of the Account:
u.Member_State__c = [SELECT BillingState FROM Account WHERE Id = :u.Member__c LIMIT 1].BillingState;
This will assign just the the String field "BillingState" into your u.Member_State__c field.

So the final trigger would be: 
trigger VendorRepUpdateTrigger on Member_Vendor_del__c (before update) 
{
// Update the Vendor's ID, Member's ID, and Member's Physical State into the Member Vendor Record.   
    for (Member_Vendor_del__c u : Trigger.new)
    {
        if (u.Vendor_ID__c == null)
        {
            u.Vendor_ID__c = u.Vendor__c;
            u.Member_Account_ID__c = u.Member__c;
            u.Member_State__c = [SELECT BillingState 
                FROM Account 
                WHERE Name = :u.Member__c 
                LIMIT 1].BillingState;
        }
    }
}
Let me know if this helps!

- Joe
This was selected as the best answer
Gabe MejiaGabe Mejia
That worked perfectly, thank you!!

I had previously been playing with the code and forgot to add the Limit 1 back. It would not have helped me as I did not know to use ".BillingState"

I do have one more questoin if you may be able to assist.

Below is my entire trigger. Can you recommend what i can do add in order to allow the Member Vendor record to save even if there are no matches? Say there is no Vendor Rep that meets the criteria outlined.

Also, the Counties_Serviced__c  and States__c under the Contact object are multi picklists. Can I have tell the SOQL query returning the Vendor Rep to see if the multi picklists contain said State and/or County in the Member Vendor record?

trigger VendorRepUpdateTrigger on Member_Vendor_del__c (before update) {
// Update the Vendor's ID, Member's ID, Member's Physical State, and County/Parish into the Member Vendor Record.    
    for (Member_Vendor_del__c u : trigger.new){
          if (u.Vendor_ID__c == null)
              u.Vendor_ID__c                 = u.Vendor__c;
              u.Member_Account_ID__c = u.Member__c;
              u.Member_State__c          = [Select BillingState
                                                           From Account
                                                            where Id = :u.Member__c Limit 1].BillingState;
              u.Member_County__c        = [Select County_Parish__c
                                                            From Account
                                                            where Id = :u.Member__c Limit 1].County_Parish__c;}

 // Update Vendor Rep Name to Member Vendor Record based on criteria.    
    for (Member_Vendor_del__c u : trigger.new){
         if (u.Vendor_Rep__c == null){
             u.Vendor_Rep__c = [Select Id,Name
                                              From Contact
                                              Where Contact_Type__c        = 'Vendor Rep'
                                              AND States__c                       = :u.Member_State__c
                                              AND Counties_Serviced__c   = :u.Member_County__c
                                              AND Contact_Vendor_ID__c   = :u.Vendor_ID__c limit 1].Id;
     }
  }
}