Engineered Code is proud to announce the availability of ecLearn - the Learning Management System built on top of Microsoft Dataverse

ENGINEERED CODE BLOG

Power Apps Portals: Related Records Filtering on Lookups When Creating Records via Subgrid

The power of Entity Forms in Power Apps Portals is that they mirror much of the functionality available in model-driven apps (and Dynamics 365). While the parity isn’t 100%, one advanced feature that Entity Forms does support is Related Records Filtering on lookups, where the available options in the lookup are filtered by some other data that has already been selected. However, in certain cases, this functionality doesn’t offer exact parity out-of-the-box – one of those cases is when you are creating a new record via a subgrid, and you’re expecting that the parent record is used in the filtering of the lookup.

Related Records Filtering

The Related Records Filtering functionality on lookups allows you to limit the available records that can be selected for a lookup, based on another value on the form.

This technique is used quite a bit in the Portal Administration model-driven app, to limit the records that are available to only those associated to the current website. So, for example, when creating a Web Page, once you select the Website record, the options for lookups like Parent Page and Page Template will only display records that are also associated to that Website (on top of the filtering of the view defined as part of the lookup). This prevents you from mixing records between websites, which would leave your portal in an invalid state.

As I mentioned above, Power Apps Portals supports this functionality when you expose lookups via Entity Forms – in addition to filtering provided by both Entity Permissions and any filtering included as part of the view used to show lookup options. However, because of a difference in how fields are set when creating related record from a subgrid, it doesn’t always work the same was as in a model-driven app.

Creating Records from a Subgrid

When you create a record from a subgrid in a model-driven app, the lookup for that particular relationship is automatically populated with the record that the subgrid was on. In Power Apps Portals, it doesn’t quite work the same way. While if you create the record from a subgrid, the record will be associated with the parent (I’ll use the term “parent” for the record that appears in the lookup, although it doesn’t necessarily need to be a parental-type relationship, just 1:N), this only occurs as part of the save – if you include the lookup on the form it will be blank. Because this value is blank, the value of the parent record is not taken into consideration when it comes to related record filtering. Only after the record is saved is the relationship populated.

In order to get the related records filtering to work as expected on create, we need to get that field populated on the form.

Getting the Value of the Parent Record

If the value of the parent record is set until the record is saved, how does the Portal code know what to set it to? The answer is that the ID of the parent record is passed in the query string. This is obvious when you choose the Web Page Target Type for the subgrid Create action, as you can see the query string parameter in the URL. What some people don’t realize is that when you choose the Entity Form Target Type, the form is rendered as an IFrame, and the same query string parameters are passed. The ID of the parent record is passed via the refid query string parameter.

Since we have the ID of the parent, we can use that to prepopulate the lookup, which then will apply the filtering to other lookups.

As an example, let’s say you have a custom entity called Projects, which has a lookup to both Account and Contact. Users are able to create new Project records from a subgrid on the Account record. When selecting a Contact record on a new Project created from an Account, we only want to see Contacts related to the Account.

This can be achieved by:

  • Adding the Contact lookup to the form and enabling Related Records Filtering to only show Contacts related to the selected Account.
  • Adding the Account lookup to the form used on the create of the new Project – it can be marked as read-only.
  • Add the following code to the Custom JavaScript section of the Entity Form used on the create of the Project:

    $(document).ready(function() {
        $('#new_lookupattribute').val("{{request.params['refid']}}").closest('.lookup').hide();
    });
    

    Replace new_lookupattribute with the name of the lookup to the parent record.

    This code uses the refid query string parameter to set the value of the read-only lookup. Once set, it will be factored into the related record filtering on the Contact lookup.

    The code also hides the read-only lookup, as the user doesn’t need to see the field.

Taking It One Step Further

This technique can also be used to filter based on fields on the parent record, and not just the parent record itself. For example, let’s say there was a lookup to language on the Account record, and you only wanted to show Contacts whose primary language matched that lookup. Using ID of the record (from the query string) and Liquid, you can get that value from the parent record, set it in a lookup, similar to above. As an example:

$(document).ready(function() {
    $('#new_lookupattribute').val("{{entities.account[request.params['refid']].new_language.Id}}").closest('.lookup').hide();
});

14 responses to “Power Apps Portals: Related Records Filtering on Lookups When Creating Records via Subgrid”

  1. […] post Power Apps Portals: Related Records Filtering on Lookups When Creating Records via Subgrid appeared first on Engineered […]

  2. Krishnan says:

    Hi Nicholas,

    Thanks for your post. I have a list on the Portal home page, when the user edits a record from the list, a form that contains a sub grid is shown. If they create a new record using the sub grid, the context with the previous form is lost. However, this works when they edit a record from the sub grid. I have tried the solution that you have recommended. It still does not work. I would greatly appreciate any further input or ideas from you to solve this. Thanks.

    • Nicholas Hayduk says:

      Can you confirm what sort of context you are expecting to see on the create form? Is it filtering of related records?

  3. Krishnan says:

    Thanks a lot for your response. Sorry for the lengthy reply. Basically the scenario is as follows
    STEP1 – In the Portal home page, the user navigates to the Entity List containing a list of records and clicks on the ‘Edit’ option of a specific record.
    RESULT: Entity Form1 belonging to Entity 1 is displayed with the correct data about the specific record. Entity Form 1 also includes a sub grid that contains records from a child entity (Entity 2).
    STEP2- User clicks on the ‘Create’ button in the sub grid to add a new child record.
    RESULT: Entity Form2 belonging to Entity 2 is shown. However the key look up fields in Entity Form 2 are not pre-filled with the data that already exists in Entity Form1.

    Based on the solution that you had provided, I added the relevant code to the Custom JavaScript section of the Entity Form 2. Look up field 1 in Entity Form 2 seems to get pre-filled with blank data, instead of the primary name from Entity Form 1. I was wondering whether I should add some sort of delay to the loading of Entity Form 2 in order to properly capture the refid parameter?

    Kindly let me know. Thanks.

  4. Lipsita says:

    I want to filter the entity form subgrid based on the lookup value, is this possible. If so can you please help me out .

    • Nicholas Hayduk says:

      Unfortunately I’m not aware of a configuration-based approach to do that. The best I can think of is to build out your own subgrid using JavaScript, FetchXML and Liquid, where you could apply whatever filtering you want.

      Nick

  5. Glenn says:

    Hi Nicholas,

    Thanks for your post.

    Is there a security risk here in that someone could breakpoint put a breakpoint in the $(document).ready(function and stop the filtering occurring? Thereby gaining access to the ensure list.

    • Nicholas Hayduk says:

      You’re right, as a JavaScript solution someone could totally bypass this.

      However, this use case isn’t really about hiding records because of security, but instead the user experience of not having to go through irrelevant rows. Table/entity permissions should still be configured on the related entity to ensure that users don’t have access to data they shouldn’t be able to see.

      There is nothing in the platform that enforces the related record filtering at a data level, so it shouldn’t be used as a security mechanism.

      Nick

  6. Linn Zaw Win says:

    Thanks a lot for this article.Filtering based on fields on the parent record is what I’m looking for. I wish relationship column mappings are also working on the portal as well.

  7. Nadie says:

    is there a way to create a related record with target type= page ?
    seems that the option i am getting for create related record is only basic form.

    TIA

  8. Ana says:

    Is it possible to filter:
    I have an A list and a B list on one power pages. I select record A in list B only records related by one-to-many relationship are shown to me. one A, many B.

    • Nicholas Hayduk says:

      Nothing out of the box to do this that I know of. You’d need to implement something like this with JavaScript and Liquid or WebAPI.

      Nick

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact

Engineered Code is a web application development firm and Microsoft Partner specializing in web portals backed by Dynamics 365 & Power Platform. Led by a professional engineer, our team of technology experts are based in Regina, Saskatchewan, Canada.