Answered

Javascript for filtering a subgrid based on the value from a lookup field from another table

(0) Share

Report

Posted on by CU31011808-1

I am trying to create a filtered subgrid that is located on a form from a table called "Reporting". "Reporting" has a field called ppt_sitename that is a lookup to Account Name from the Accounts table. The subgrid is based on a table called "Pick Up Data" that also has a field called ppt_sitename that is a lookup to Account Name from the Accounts table. I want to filter the subgrid based on the value in the ppt_sitename field from TableB.

Here is the code I am using which is finding the correct subgrid, but giving a filtering an error on the relationship criteria. I feel like the code cannot find the lookupId because it doesn't know the root table of ppt_site name (which is the Account table. The code executes on load of the form from "Reporting".

Any help would be appreciated!

function filterPickupSubgrid(executionContext) {
    var formContext = executionContext.getFormContext();
    var lookupField = formContext.getAttribute("ppt_sitename"); // Replace with your lookup field name
    var subgridName = "subgrid_pickupdata"; // Replace with your subgrid name
    var lookupValue = lookupField.getValue();
    if (lookupValue != null && lookupValue.length > 0) {
        var lookupId = lookupValue[0].id;
        var entityName = lookupValue[0].entityType;
        var fetchXml = `<fetch version='1.0' mapping='logical'>
                          <entity name='ppt_pickupdata'> 
                            <attribute name='ppt_pickupdataid' />
                            <filter type='and'>
                              <condition attribute='ppt_pickupdata_SiteName_account' operator='eq' uitype='${entityName}' value='${lookupId}' />
                            </filter>
                          </entity>
                        </fetch>`;
        formContext.getControl(subgridName).setFilterXml(fetchXml);
        formContext.getControl(subgridName).refresh();
    } else {
      // If the lookup field is empty, show all records in the subgrid
        var fetchXmlAll = `<fetch version='1.0' mapping='logical'><entity name='ppt_pickupdata'><attribute name='ppt_pickupdataid' /></entity></fetch>`;
        formContext.getControl(subgridName).setFilterXml(fetchXmlAll);
        formContext.getControl(subgridName).refresh();
    }
}

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

SaiRT14 1,990 Super User 2025 Season 2 on at

Like (0)

Report

Hi CU31011808-1

ppt_pickupdata_SiteName_account, but ensure that this matches the actual relationship name between Pick Up Data and Account. The relationship name should be the logical name of the lookup field in the Pick Up Data table that points to the Account entity. This is usually in the format accountid or a custom logical name, such as ppt_sitename.

The uitype should be set as account for the Account entity.

function filterPickupSubgrid(executionContext) {
var formContext = executionContext.getFormContext();
var lookupField = formContext.getAttribute("ppt_sitename"); // Replace with your lookup field name from Reporting table
var subgridName = "subgrid_pickupdata"; // Replace with your subgrid name
var lookupValue = lookupField.getValue();

if (lookupValue != null && lookupValue.length > 0) {
var lookupId = lookupValue[0].id; // Get the lookup ID
var entityName = lookupValue[0].entityType; // Get the entity type (should be 'account')

// Correct relationship name (assumes ppt_sitename is the field in Pick Up Data table that links to Account)
var fetchXml = `<fetch version='1.0' mapping='logical'>
<entity name='ppt_pickupdata'>
<attribute name='ppt_pickupdataid' />
<filter type='and'>
<condition attribute='ppt_sitename' operator='eq' uitype='account' value='${lookupId}' />
</filter>
</entity>
</fetch>`;
formContext.getControl(subgridName).setFilterXml(fetchXml);
formContext.getControl(subgridName).refresh();
} else {
// If the lookup field is empty, show all records in the subgrid
var fetchXmlAll = `<fetch version='1.0' mapping='logical'>
<entity name='ppt_pickupdata'>
<attribute name='ppt_pickupdataid' />
</entity>
</fetch>`;
formContext.getControl(subgridName).setFilterXml(fetchXmlAll);
formContext.getControl(subgridName).refresh();
}
}

pls try and let me know if you need more details.

thakns

Was this reply helpful? Yes No
CU31011808-1 69 on at

Like (0)

Report

SaiRT14,

It worked!!! Thank you so much! Made my day.

Was this reply helpful? Yes No