Power Platform Community Forum Thread Details

I am building a canvas app to track projects using Dataverse for the back end. I have two tables named Site (think of this as a physical location) and Phase. Phase has a lookup column to Site as one site can have multiple phases, but a phase can only belong to one site. On the Power App, there are two text input fields and two galleries on a single screen. In the first version of the app, the two text input fields were for Site Number (a text column on the Site table) and Customer Number (also a text column on that table). The first gallery was for sites and the second was for phases. The text in the two text input fields was used to filter sites, and then the selected site from the 1st gallery would filter the phases gallery. The code for the sites gallery looked like this:

Sort(
    Filter(
        Sites,
        And(
            Or(
                IsBlank(txt_SiteNum.Value),
                StartsWith(
                    SiteNum,
                    txt_SiteNum.Value
                )
            ),
            Or(
                IsBlank(txt_CustNum.Value),
                StartsWith(
                    CustNum,
                    txt_CustNum.Value
                )
            )
        )
    ),
    'Created On',
    SortOrder.Descending
)

However, I've recently learned that the customer number is actually a property of the phase and not the site. I've added that column to my Dataverse table for phases, but now I'm having a hard time using the customer number to filter the sites gallery. I basically need to filter the Sites table for any records which have phases where the customer number is equal to (or StartsWith() if possible) the number entered in txt_CustNum. This is the closest I've been able to get, but I don't know if there is a better way I should be doing this. The code below doesn't work:

With(
    {
        scpPhasesForCustomer: Filter(
            Phases,
            Or(
                IsBlank(txt_CustNum.Value),
                StartsWith(
                    CustNum,
                    txt_CustNum.Value
                )
            )
        ).SiteID
    },
    Sort(
        Filter(
            Sites,
            And(
                Or(
                    IsBlank(txt_SiteNum.Value),
                    StartsWith(
                        Number,
                        txt_SiteNum.Value
                    )
                ),
                ThisRecord in scpPhasesForCustomer
            )
        ),
        'Created On',
        SortOrder.Descending
    )
)

Categories:

General Questions