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 Formstack Documents 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 Formstack Documents) 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}

Was this article helpful?
1 out of 3 found this helpful

Comments

42 comments
  • 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.

    0
  • 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!

    0
  • 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

    0
  • 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'

    0
  • 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?

    0
  • 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!

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

    0
  • 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}

    0
  • 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?

    0
  • 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 :)

    0
  • 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

    0
  • 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!

    0
  • 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!

    0
  • 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!

    0
  • Hi,

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

    Your response will be greatly appreciated. Thank you.

    0
  • Hi George,

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

    Thanks!

    0
  • 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.

    0
  • 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}

    0
  • 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.

    0
  • 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,

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

    Thanks, Jerome.

    0
  • 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!

    0
  • Hi Jeremy,

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

    Best regards.

    0
  • 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.

    0
  • 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.

    0
  • 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?

    0
  • 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.

    0
  • Hi Jeremy,
    I want to access all the contact which have same account Id of my current AccountId of Contact record. So for this i wrote an a Following SOQL
    Select id,Name,Phone,Salary__c,AccountId from Contact where AccountId =:AccountIdOnContact__c
    Where AccountIdOnContact__c is formulla field which store this AccountId.
    But it not works.

    0
  • Hi Aksahy,

    Can you try this query instead (I added quotes around the field in the query):

    Select id,Name,Phone,Salary__c,AccountId from Contact where AccountId = ':AccountIdOnContact__c'

    0
  • Thank You it works now

    0

Please sign in to leave a comment.