Populating a Dropdown Box with one of 2 Collections based on the value in another Dropdown box

[Greg27]

I have a Dropdown that is populated from a SharePoint List. I have 2 other Dropdowns that are populated with Collections based on which value is selected from the first Dropdown. My problem seems to be with loading the collection sorted and displaying Lastname, Firstname. My first dropdown is working fine and displays 2 values. My 2nd Dropdown seems to be struggling with the 2 field names in my collections. Here is the code:

Switch(dd_DataCardValue4_1.Selected.School,

    "Trad undergrad", Sort(AddColumns(ColAzureAdvisorsTug, "FullName", surname & ", " & givenName), surname),

    "GRTS (SEM)", Sort(AddColumns(ColAzureAdvisorsGRTS, "FullName", surname & ", " & givenName), surname)

)

[RandyHayes · Accepted solution]

@Greg27 

In general, using multiple datasources for a control in PowerApps is not a good design idea as this can lead to a host of issues at play time and in many cases is not allowed to even be entered.

The only time that it is somewhat feasible is if the two tables are identical.

So if your ColAzureAdvisorsTug and ColAzureAdvisorsGRTS are not identical, this will lead to issues.

I would suggest dispensing with the use of the collection to start with, but otherwise making sure they have the same schema.

Then I would look at duplication of formula.  You are performing the same type of operation twice.  My suggestion would be to simplify to the following:

Sort(
    AddColumns(
        Switch(dd_DataCardValue4_1.Selected.School,
            "Trad undergrad", ColAzureAdvisorsTug, 
            "GRTS (SEM)", ColAzureAdvisorsGRTS
        ),
        "FullName", surname & ", " & givenName
    ), 
    surname
)

I hope this is helpful for you.

[Greg27]

@RandyHayes 

I tried the statement you included and I still get the same errors. The 2 collections are the same, but I would rather do this the right way as opposed to making the wrong way work. If I drop the Collections, What is the best way to accomplish this?

[RandyHayes]

@Greg27 

Well let's see what is going on to begin with before dealing with the other aspect.  Although, if you state that the collections are identical, then there is not much concern over the formula.

So what is it that you are seeing/not seeing??

[Greg27]

@RandyHayes 

Here is a screenshot of the statement you gave me:

If I hover over surname or givenName, I see the following:

Without showing any data, Here are the column headers. This one is for ColAzureAdvisorsTug:

Here is the one for ColAzureAdvisorsGRTS:

Is there anything else that would help to see?

[RandyHayes]

@Greg27 

Formula and collection looks good.  So let's see what PowerApps is seeing...

Place your cursor at the end of the name of your collection in the formula and type a "dot"/period and see what the column name suggestions are that comes up.

Also, what is your Items property on the dd_DataCardValue4_1 control?

[RandyHayes]

@Greg27 

Oh...and I should add....

Once you have typed the period after the collection name to see what it has, do the same thing at the end of the Switch function closing paren.  It should show the same column suggestions there as well.

[Greg27]

@RandyHayes 

When I put a period after my first collection, it gave me the suggestion of 'value'. Here is the screenshot:

Here is the screenshot of putting a period after the ')' which ends the Switch command:

I hope I did this correctly. Let me know if you need any more information.

[RandyHayes]

@Greg27 

Not seeing the suggestions in the screenshot, but that's okay...you mention that all you see is Value, so there is something wrong.

How are you populating that collection??  In other words, what is your Collect/ClearCollect statement?

[Greg27]

@RandyHayes 

On the OnStart Property of the App, here is my code which loads the Collections:

ClearCollect(ColAzureStaffulty, Office365Groups.ListGroupMembers("Azure Object ID")); ClearCollect(ColAzureAdvisorsTug, Office365Groups.ListGroupMembers("Azure Object ID")); ClearCollect(ColAzureAdvisorsGRTS, Office365Groups.ListGroupMembers("Azure Object ID")); Set(varStudentName,User().FullName);

I replaced the actual Azure Object ID with the name "Azure Object ID".