Use SOQL Queries

If you're comfortable with SOQL queries and you want to pull in a custom list of object/records from Salesforce, we have a way for you to write your own queries.

The result of the queries will be a list of records, so you need to setup a loop in your document.  Then in your WebMerge Mapping, you'll find that loop merge field, and you'll select << SOQL Query >> from the dropdown.

Then you'll see a text box where you can enter your query:

query.png

 

Use field values in query

This query can include fields from the main object you're pulling data from in the Mapping (ie the Account record).  Our system will search for this format in the query:

:FieldName

So for example, if you wanted to include the Account's Name in the query you would use:

:Name

 

Default System Fields

:UserInfo.Id

:UserInfo.Name

:UserInfo.Email

:UserInfo.FirstName

:UserInfo.LastName

:UserInfo.ProfileId

:UserInfo.RoleId

:UserInfo.Type

 

Using the query result

The result of this query (ie the data that's sent over to WebMerge) will be in a list/array that you can loop through in your document.  Inside the loop, you'll use the subfields that you queried to print the values.

Here's an example:

{foreach from=$opps item=_row}
    {$_row.Name}
{/foreach}

Have more questions? Submit a request

27 Comments

  • Avatar
    Priya Y

    Hi,
    For pulling data through SOQL queries, it has been told that we need to set up a loop in the document and then map with <> in Salesforce field mapping.
    Could you provide with the syntax?
    Ex: I have used the loop below in my document
    {foreach from=$contacts item=_con
    {$_con.Name}
    {/foreach}

    But while doing mapping for contacts I am not able to see > in Salesforce fields.

  • Avatar
    Jeremy Clarke

    Hi Supriya,

    In your WebMerge Mapping, next to your "contacts" field, type in "<".

    You may have to upgrade to the latest version of our managed package. This was released last week.

    Thanks!

  • Avatar
    Adarsh Singh

    Hi Jeremy,
    I am using SOQL for field mapping, this below SOQL am used to get records.

    SELECT Id, Name, Price_Book__c, Product__c, Price_Book__r.Id, Price_Book__r.Name, Product__r.Id, Product__r.Name, Product__r.Product_Name__c FROM Price_Book_Entry__c where Price_Book__r.Name = :LoopName

    In above image, LoopName is a merge field, mapped with 'Price Book Name'.

    but am not able to get any records due to incorrect filter parameter. can you please let me know the correct way to pass a dynamic value in where clause in SOQL.

    thank you

    Edited by Adarsh Singh
  • Avatar
    Jeremy Clarke

    Hi Adarsh,

    Instead of :LoopName, you need to use the API Field Name for the field that's on the primary object of your mapping (if the field isn't on your primary object, then you'll need to create a new custom field and use a formula to set the value). It should look more something like this:

    SELECT Id, Name, Price_Book__c, Product__c, Price_Book__r.Id, Price_Book__r.Name, Product__r.Id, Product__r.Name, Product__r.Product_Name__c FROM Price_Book_Entry__c where Price_Book__r.Name = ':Loop_Name__c'

  • Avatar
    Gurpreet Kaur

    Hi Jeremy

    I am fetching full name using SOQL query
    Select name from Contact where Id=':Contact__c'

    This is added in Relationship (Custom object) which has master detail relationship with Contact. I am getting data in Merge data while debugging however my output template shows array instead of full name.

    How to fix this?

  • Avatar
    Jeremy Clarke

    Hi Gurpreet,

    When the data is sent over to WebMerge from an SOQL query, it will always send over an array (even if you just select 1 field), so I suggest doing this in your template:

    {$Contact.Name}

    Then in your WebMerge Mapping you'll put your SOQL next to the "Contact" merge field.

    Thanks!

  • Avatar
    Shafqat Ali

    Can the loop or SOQL be done in a PDF document?

  • Avatar
    Jeremy Clarke

    Hi Shafqat,

    You can't use a loop inside a fillable PDF, but you can use static fields using the Field Map (https://support.webmerge.me/hc/en-us/articles/206526086-Field-Map) like this:

    {$products.0.name}
    {$products.0.price}
    {$products.1.name}
    {$products.1.price}

  • Avatar
    Carles Massot Cardona

    Hi,

    We have a requirement for which we need the ID of the logged in user. We plan to use in the SOQL query. How can we get this?

  • Avatar
    Jeremy Clarke

    Hi Carles,

    We just released a new version of our app (version 1.240) that supports UserInfo fields like this in your SOQL:

    UserInfo.Id
    UserInfo.Name
    UserInfo.Email
    UserInfo.FirstName
    UserInfo.LastName
    UserInfo.ProfileId
    UserInfo.RoleId
    UserInfo.Type

    Please install the latest version of our app from the AppExchange :)

  • Avatar
    Saraswathi D

    Hi ,
    I need to pass object id in the SOQL
    eg : I have a full object TEAM so how to use TEAM.ID(any other field using dot operator) in the SOQL WHERE condition

  • Avatar
    Jeremy Clarke

    Hi Saraswathi,

    Unfortunately, you can only use fields that are on the primary object of your mapping, but you could setup a custom field on that object that references your Team ID field and then use that custom field in your SOQL query.

    Thanks!

  • Avatar
    JP Sulit

    Hi,

    I'm trying to do this SOQL query on the WebMerge mapping but it didn't work.

    Query: select id, name from user where id = ':UserInfo.Id'

    Error: SOQL Error: invalid ID field: :UserInfo.Id

    Your help will be greatly appreciated. Thanks!

  • Avatar
    Jeremy Clarke

    Hi JP,

    Actually, you don't need to use a SOQL query to grab the user information. We have a field available called "Current User: (Full Object)" in the field dropdown. You can use it like this: https://support.webmerge.me/hc/en-us/articles/115004710026-Pull-Data-from-Parent-Related-Records

    Thanks!

  • Avatar
    George Hedley

    Hi,

    Is it possible to do a COUNT in a SOQL query in WebMerge?

    Your response will be greatly appreciated. Thank you.

  • Avatar
    Jeremy Clarke

    Hi George,

    Yes, you should be able to use COUNT in your SOQL queries.

    Thanks!

  • Avatar
    George Hedley

    Thank you for the response. I managed to do it but I cannot get the expected output. I want to return an integer but I'm seeing this value 'Array' instead.

    This is my SOQL query.

    SELECT COUNT(Opportunity.SyncedQuoteId) FROM OpportunityContactRole WHERE Opportunity.SyncedQuoteId = ':Id'

    Your ideas will be much appreciated. Thank you.

  • Avatar
    Jeremy Clarke

    Hi George,

    With our SOQL queries, we always return a list of rows, so that's why you're seeing Array. If you turn on Debug Mode (https://support.webmerge.me/hc/en-us/articles/206526126-Debug-Mode) you can view the exact merge field to use. It will be something like {$FieldName.0.Count}

  • Avatar
    Shafqat Ali

    I enter the soql in Product field and ask for name, price, qty
    In my field map I have the field map like this in each respective field.
    {$products.0.name}
    {$products.0.price}
    {$products.0.qty}
    {$products.0.desc}

    since the soql is in the field Name and the return data is "Apple"
    on all the other fields I get just the letter A. Except qty is fine. thats a number

    Help please.

  • Avatar
    Jeremy Clarke

    Hi Shafqat,

    Please turn on Debug Mode (https://support.webmerge.me/hc/en-us/articles/206526126-Debug-Mode) to make sure we're receiving an array of data from your SOQL query and that you're using the correct sub-fields in your template.

    Thanks,

  • Avatar
    Jérôme AUDINEAU

    Hello,
    I do not understand where to integrate the query soql, can you help me?

    Thanks, Jerome.

    Edited by Jérôme AUDINEAU
  • Avatar
    Jeremy Clarke

    Hi Jerome,

    Inside your WebMerge Mapping, under the Field Mapping section, you'll choose << SOQL Query >> from the dropdown, then you can enter the query in the box that shows up below it.

    Thanks!

  • Avatar
    Jérôme AUDINEAU

    Hi Jeremy,

    thanks a lot for your rapid answer, i located the field !

    Best regards.

  • Avatar
    Rohit Verma

    Hello Jeremy.
    I am trying nested Query and unable to get the child records in the loop. Below is my logic.
    {foreach from=$InnovationAgenda item=_agenda}
    {$_agenda.Name}
    {foreach from=_agenda.Agenda_Participants__r item=_part}
    {$_part.Name}
    {/foreach}
    {/foreach}

    I am running my report on event object which is a parent of Agenda object. I want to report Event and its related Agenda's and each Agenda attendees.
    Can you please help me.

  • Avatar
    Jeremy Clarke

    Hi Rohit,

    It looks like you're missing the "$" in your 2nd foreach loop in front of _agenda. It should be this:

    {foreach from=$_agenda.Agenda_Participants__r item=_part}

    If that still doesn't work, please turn on Debug Mode in WebMerge and verify that "Agenda_Participants__r" is the correct sub-field in that array.

  • Avatar
    Alex Waddell

    Jeremy,

    I am trying to use a SOQL query to return a SUM inside my webmerge document

    I am using the following query which is a valid query in SOQL;

    SELECT SUM(Invoice_Amount__c) FROM Invoice__c Where VendorName__c = ':Account__c' AND Pay_Period__c = ':Pay_Period__c' And Year__c = ':Year__c'

    If i just try to call on the merge field called {$Total} i am returned a value of "Array"

    But if I try and place this within a loop;
    {foreach from=$Total item=_row}
    {$_row.sum(Invoice_Amount__c)}
    {/foreach}

    I get the following error: There is an error in your document: Syntax error in template "string:" - Unexpected "{Invoice_Amount__c}", expected one of: "}"

    Do you know of a way to return a Sum in webmerge?

  • Avatar
    Jeremy Clarke

    Hi Alex,

    If you turn on Debug Mode (https://support.webmerge.me/hc/en-us/articles/206526126-Debug-Mode) and run another merge then you'll be able to click on the field in your merge data to see the exact merge field to use, but it should be something like this:

    {$Total.0.Invoice_Amount__c}

    The SOQL queries return an array (list) of rows, so we have to reference the first item in that array using the ".0" then the field name after that.

Please sign in to leave a comment.
Powered by Zendesk