Using SOQL to replicate tables and rows

  • 29 March 2018
  • 6 replies
  • 44 views
T

I've used SOQL to successfully build a relationship between my main object and child objects through a junction object. Each of those children has children, so I want to use the SOQL relationship to replicate a table (which works) and a second SOQL relationship to replicate the grandchildren as rows.  However, only the same grandchild record is replicated in the tables. 

 

So it should be Table A has row a and Table B has row b, but what I have is Table A row a and Table B row a.

 

Does that make sense? Any guidance is appreciated!

5 Attachments

6 replies

R
Userlevel 4
Badge +8

Hey Tim McIntire‌,

You're on the right track but the way table and row replication work in SOQL Relationships is a little different than our basic Relationships.

You need to nest the grand child query in your main query. 

For example: 

SELECT Id, Name, (SELECT Id, Phone FROM Contacts) FROM Account

The above query would replicate Tables for Accounts and Rows for Contacts. If there are multiple grand children you can continue to nest queries within the query. The field tagger will automatically recognize this and add both objects into the tagger for you.

Example of multiple grand child objects:

SELECT Id, Name, (SELECT Id, Phone FROM Contacts), (SELECT Id, Name FROM Opportunities) FROM Account

I hope that helps.

Cheers,

Ryan

UPDATED: Please keep in mind you need to use the child object relationship name on inner queries. So for example to find the Child Object Relationship name you can navigate to the Opportunity object in Setup find the field that looks up to the Account and grab the Child Object Relationship name from there.

(Click on the image to enlarge)

T

That really helps!  Do I need to choose TABLE as the Copy Type in the Relationship Editor at all, or does the tagger take care of it?

R
Userlevel 4
Badge +8

I believe the relationship will force you to choose Table and the tagger will handle the correct tags. I added some additional info in the above post as well.

T

I'm so close I can taste it!  I'm struggling with the syntax for using the child relationship name in the inner query.  I feel like I need a "__r" somewhere...?

(SELECT AcctSeed__Amount__c FROM Project_Account_Payable_Lines) 

R
Userlevel 4
Badge +8

Ahh, yes. Since it is a custom object you need __r after the name like:

(SELECT AcctSeed__Amount__c FROM Project_Account_Payable_Lines__r) 

D
Badge

Can some one help me here to generate report for grandchild. ex.

Here I want to generate a Summary report for Accounts

Account » Related Opp Records » Related Line Items

Account 1 (account 1 and related records)

  opp1 

     line tem 1

     line tem 2

  opp2 

     line tem 1

     line tem 2

Account 2 (account 2 and related records)

 

I tried below soql but inner query does not support here:
select id,Name,(Select Id,Name,(select Id from opportunityLineItems) from Opportunities)  from account 

 

I can achieve this using w/o soql but it works only single (current working account record and I want to generate summary report for multiple account records)

Account

Opprortunities(Relationship)

OpprortunitLineItmes(Relationship)

 

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings