Populate dropdown items with filter and lookup

[Paul_GP]

Hi All,

Ive come across something that i cant seem to do,

I have two Azure SQL tables similar to below with dummy data:

I need to populate a dropdown or combobox with from the content within Table1 but filtered to only show the items that are enabled. This i have been able to acheive pretty easily by filtering for only items that are 'true' and showing BVR_ID as the dropdown items. However obviously this only shows the ID number of BVR.

Is there a way that i can then perform a lookup on the filtered results against table 2 so i can return the BVR_Name as opposed to its ID number?

Thanks!

[timl]

Hi @Paul_GP

Since you're using SQL Azure, I would recommend that you create a SQL View and populate the contents of your dropdown with the view. This method would work more efficiently than doing the lookup in PowerApps.

If you were to do this lookup in PowerApps, the formula for the items property of your dropdown would look something like this:

AddColumns(Filter(Table1, BVR_Enabled = true),
           "BVR_Name",
           LookUp(Table2, 
                  Table2[@ID] = BVR_ID
           )
)

[Paul_GP]

Hi @timl

Thanks for the reponse,

Unfortunatley i dont think SQL views will be an option as there is more filtering that occurs prior to this point in the app based on selections the end user will make.

Ive tried the forumla you provided but that just returns the values of the ID column of table1.

Ive tried playing with the formula but im not haivng much luck, any further ideas greatly appreciated! 

[timl]

Hi @Paul_GP

Just to confirm, when you view the 'value' property of your dropdown, do you see the option to change it to "BVR_Name"?

To further diagnose this, if you add a data table control and set the items property to the formula, do you see the "BVR_Name" field?

[Paul_GP]

Hi @timl,

So to be more specific if im using a combobox, it displays the ID of table1,

If i am using a dropdown it actually defaults to another column of Table1 called description (which is didnt show on my example screenshot) but Value is greyed out and therefore i can change it.

If i create a data table it shows the column of BVR_Name and allows me to select it, the actual data within it however shows [object Object] for each row.

Thanks for your help thus far!

[timl · Accepted solution]

Hi @Paul_GP

I think I see what's wrong here. Could you try this formula instead?

AddColumns(Filter(Table1, BVR_Enabled = true),
           "BVR_Name",
           LookUp(Table2, 
                  Table2[@ID] = BVR_ID
           ).BVR_Name
)

[Paul_GP]

Hi @timl

Were making progress! Well you are!

That fixes the data table control which now correctly displays BVR_Name with lookups being completed correctly.

However the dropdown still seems determined to lock itself onto this description column of table1 with no ability to change it.

Im wondering wether this formula could be manipulated to achieve the same thing but as a collection at which point i could point the dropdown to the BVR_Name column of the collection?

Thanks

[Paul_GP]

@timlScrap the above without changing anything it does seem to be working correctly on dropdown controls.

It still doesnt work on combo box it just keeps resetting the displayfield but dropdown should work perfectly for what i need it for.

The formula has a delegation warning but as i briefly mentioned before there is other filtering that will happen prior to this so the number of records your formula will be working with should only ever be around 40 or so so shouldnt be an issue.

If you have the time could you explain a little bit more about what the formula is doing? Thank you for your help, my app hit a complete brick wall until you fixed this.

Thanks again!

[timl]

Hi @Paul_GP

Just to explain this in a bit more detail, the formula calls the AddColumns function to add the BVR_Name column to Table1. AddColumns accepts 3 arguments - the data source, the name of the column to add, and the data source of the column to add.

In terms of the data source of the BVR_Name column that we want to add, the formula calls the Lookup function to lookup the ID in Table2 that matches the BVR_ID field in Table1. Since both Table1 and Table2 contain columns that are named ID, we use the 'disambiguation syntax' (ie Table2[@ID]) to avoid any ambiguity in Lookup function. 

Hope that clarifies things a bit more for you.