gridField with 2 joined entities query

Hi All,

I have to entities and I would like to create a gridField with their data

What is the best way to create a query with this join and then show the grid field  ?

 

Thanks

Elia  

  Discussion posts and replies are publicly visible

Parents
  • The view is a great way to handle this...Alternatively, we have also created expressions that return a dictionary and perform query of the "child" table data in the expression. Then create a datasubet using the combined data to build the grid. This has worked well for us in situations where the requirements are changing very rapidly without having to update the database view and its CDT.
  • Bryant I don't know if I well understood the solution
    Could you post an example , please ?
  • 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.

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

Children