Sometimes, while writing JavaScript or liquid code, you need to retrieve the information from a related table. For example, retrieve the account details of the logged-in contact and retrieve the primary contact details of the account associated with the logged-in contact. Let’s discuss three different business scenarios, where we will retrieve data from related tables.
Pre-requisites
It’s important to have appropriate table permissions assigned to the respective web roles that are assigned to logged-in portal users. For example, if you want to retrieve the account details of a logged-in portal contact, then you must have appropriate table permissions assigned to the Account and Contact tables.
Scenario 1: Retrieve data from the related table associated with Contact table.
If any table has a N:1 relation with Contact table or has lookup on Contact table, then you can directly use user object to retrieve the related table primary id and primary name. For example, Account table has N:1 relation with Contact table. That means, you can directly access account id (primary id column) and account name (primary column name) using user object as shown in code below.
In the following code, i have written following code on Profile webpage to display account name on hover of logged-in user’s name.
var accountName = '{{user.parentcustomerid.name}}';
<script>
$(document).ready(function(){
alert(accountName);
$("#firstname").attr('title', accountName}}');
});
</script>
Scenario 2: Retrieve data from the table using Guid
If you know the Guid of the related table record, then use entities object to retrieve the information. For example, In the following code, I am retrieving the associated account’s website of logged-in portal user.
{% assign account = entities['account'][user.parentcustomerid.id] %}
<script>
$(document).ready(function(){
alert('{{ account.websiteurl}}');
$("#parentcustomerid_name").attr('title', '{{ account.websiteurl}}');
});
</script>
In the following code, I am retrieving the primary contact name of the associated account of the logged-in portal user, which is a two level down nesting of the related table.
{% assign account = entities['account'][user.parentcustomerid.id] %}
<script>
$(document).ready(function(){
alert('{{ account.websiteurl}}');
$("#parentcustomerid_name").attr('title', '{{ account.primarycontactid.name }}');
});
</script>
Scenario 3: Retrieve data from the table using Guid available in query string URL
Sometimes you can also get the record ID from the URL or query string of the portal webpage. In that case, you can use the following code:.
{% assign account = entities['account'][request.params.id] %}
<script>
$(document).ready(function(){
alert('{{ account.websiteurl}}');
$("#parentcustomerid_name").attr('title', '{{ account.websiteurl }}');
});
</script>
Summary
By understanding entity relationships and using liquid code effectively, you can retrieve data from related tables and display it on Power Pages to provide a rich and interactive user experience.