Skip to main content

Notifications

Power Pages - Design & Build
Suggested answer

Lookup column choices filtered by an intersect table

Posted on 26 Nov 2024 15:48:27 by
I have a Power Page Basic form with two lookup columns to the tables (Provider Type and Department) which are related by a third intersect table.  It is designed this way because I need the ability to have start and end dates to the many-to-many relationship between Provider Type and Department.  When a user selects a Provider Type I want the Department lookup to only display values which are related to the Provider Type as defined in the Provider Type_Department intersect table. The steps I have seen, but I have not made them work yet are:
1. Make both Look Up fields Drop Downs - done
2. Create a Web Template which selects the records I want using FetchXML. - This is probably not written correctly, but I do not know how to test the output
3. Create a Page Template from the Web Template
4. Create a Page from the Page Template
5. Add Custom Java Script to the Form on the page to change the available values for the Department Drop Down.
Is this the best way? If so, how do I write and test the statement in step2.
Categories:
  • Suggested answer
    Fubar Profile Picture
    Fubar 7,552 on 26 Nov 2024 at 22:09:34
    Lookup column choices filtered by an intersect table
    If your fields are Lookups on the intersect table then, in the first instance, you could try using the out of the box Related Records Filtering (set this up on your dataverse form) - note: this doesn't work if you choose to render the Lookup as a dropdown rather than using the out of the box Lookup.
     
    If not wanting to use it rendered as a Lookup, in the old days calling Web Page that uses a Web Template with fetchxml would have been a way to get the values, however you can achieve the same by reading the values using the portals Web API now https://learn.microsoft.com/en-us/power-pages/configure/web-api-overview
     
    If you knew that there would only ever be a limited number of records (such as will only ever be a total of 20), you may decide to run the fetchxml in the Web Page content of your current page (and load all its records into a JavaScript array/object) and then filter based on that array/object - reason for doing this is that the query would be run before the page is rendered vs via the Web API (or old style) you run the query by making a JS Ajax call so the query gets run after the page is rendered (by the time you get the results it may be a second or two).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

November 2024 Newsletter…

November 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #7 Community Profile Tips…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,129

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,797

Leaderboard

Featured topics