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

  • I don't know if this a good way because also in your solution you have to call rule!getOrgNameById(ri!input.orgId) as many times as you have data rows ... More we need to create a particular rule for each field, i.e. you have getOrgNameById , if we needed another field we should create getOtherFieldById
  • 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
    }
    )

  • Yes you would have to call the query once for each row, that is why we only use it in particular situations...not all situations can use this approach. We find it particularly useful with small data sets, unclear and changing requirements, and during prototyping. Also, use of expression rules instead of query rules will help improve performance.
  • yes, I understand
    So the best way is the view
    Thanks for you help
    Elia