gridField with 2 joined entities query

Lets assume you have a table with employee data (name, supervisorName, orgId) and a table with organization data (id, name) you want to use it to build a grid to display (Employee, Supervisor, and Organization Name).

You will need an expression Rule (buildEmployeeGridData) to build data for grid each row.  It will have 1 input (ri!input of "Any Type") and be defined as follows:
{
 name: ri!input.name,
 supervisor: ri!input.supervisorName,
 orgName: rule!getOrgNameById(ri!input.orgId)  <--This query returns a text string of the name of an organization based on its id field in the database

}

You will then build a datasubet and by quering the employee data table and looping thru it query the organization name for each row:

=load(
 
 local!data: rule!getAllEmployees(),
 local!gridData: todatasubset(apply(rule!buildEmployeeGridData, local!data), a!pagingInfo(1,-1)),
 
 a!dashboardLayout(

  label: "Example Join Table"
   firstColumnContents: {
    a!gridField(
     columns: {

      a!gridTextColumn(
       label: "Employee",
       field: "name",
       data: local!gridData.name),

      a!gridTextColumn(
       label: "Supervisor",

       field: "supervisor",
       data: local!gridData.supervisor),

      a!gridTextColumn(
       label: "Organization Name",
       field: "organization",
       data: local!gridData.orgName)},
     totalCount: local!gridData.totalCount,

     value: a!pagingInfo(1,-1))
   }
   
)
 )

This is not the most efficient method for large datasets but has been very effective for in situations where user requirements permit its use.

Lets assume you have a table with employee data (name, supervisorName, orgId) and a table with organization data (id, name) you want to use it to build a grid to display (Employee, Supervisor, and Organization Name).

You will need an expression Rule (buildEmployeeGridData) to build data for grid each row.  It will have 1 input (ri!input of "Any Type") and be defined as follows:
{
 name: ri!input.name,
 supervisor: ri!input.supervisorName,
 orgName: rule!getOrgNameById(ri!input.orgId)  <--This query returns a text string of the name of an organization based on its id field in the database

}

You will then build a datasubet and by quering the employee data table and looping thru it query the organization name for each row:

=load(
 
 local!data: rule!getAllEmployees(),
 local!gridData: todatasubset(apply(rule!buildEmployeeGridData, local!data), a!pagingInfo(1,-1)),
 
 a!dashboardLayout(

  label: "Example Join Table"
   firstColumnContents: {
    a!gridField(
     columns: {

      a!gridTextColumn(
       label: "Employee",
       field: "name",
       data: local!gridData.name),

      a!gridTextColumn(
       label: "Supervisor",

       field: "supervisor",
       data: local!gridData.supervisor),

      a!gridTextColumn(
       label: "Organization Name",
       field: "organization",
       data: local!gridData.orgName)},
     totalCount: local!gridData.totalCount,

     value: a!pagingInfo(1,-1))
   }
   
)
 )

This is not the most efficient method for large datasets but has been very effective for in situations where user requirements permit its use.

In the expression rule, you could create a local var to query the data from the child table...
load(
local!orgData: rule!getOrgData(ri!input.orgId),
{
name: ri!input.name,
supervisor: ri!input.supName,
organization: local!orgData.name,
location: local!orgData.location
}
)