Creating Dynamic Sized list, drop down, or something similar from sql data using ShowColumns and AddColumns

[Luffyloo]

Hi all, looking for some help with getting things showing properly. Currently I have a statement 

ShowColumns(
AddColumns(
'[dbo].[Entity_LanguageLink]', "Languages",
LookUp('[dbo].[Languages]', ClientList.Selected.ParentID = '[dbo].[Entity_LanguageLink]'[@ID] && LanguageID = '[dbo].[Entity_LanguageLink]'[@LanguageID], LanguageName)), "Languages")

Essentially, this involves 2 sql tables.

The Entity_LanguageLink table and the Languages table. What I am doing is looking in the link table to see which language ids are associated with which parent id. This works but whenever I try to attach it to a list or dropdown there seems to be blank values. I'm not sure if this is because it is storing values from other parents and just not showing them because of my conditions or if I am doing something wrong. I'll post a screenshot of a drop down and list for clarity. Also, currently there are only 3 total languages, English, Spanish, and Creole.

 So this drop down the parent only speaks these 2, so I am confused why there is essentially a blank 3rd row in the dropdown. Thanks! 

[v-siky-msft · Accepted solution]

Hi @Luffyloo ,

Please share more details about Entity_LanguageLink and Languages tables.

Are there three records in Entity_LanguageLink, right?

The code you used is to add a new column to store the language name based on some conditions and extract that column as the Items of dropdown.

So if the Entity_LanguageLink has three records, two of which satisfy the condition to get the Language Name, then the table should be as follows:

Column1	Column2	Languages
Test	Test	English
Test	Test	Spanish
Test	Test

Then you use ShowColumns function to extract the Languages Columns, then it shows as:

Languages

English

Spanish

As an alternative workaroound, add a Filter to filter out the blank record.

Filter(ShowColumns(
AddColumns(
'[dbo].[Entity_LanguageLink]', "Languages",
LookUp('[dbo].[Languages]', ClientList.Selected.ParentID = '[dbo].[Entity_LanguageLink]'[@ID] && LanguageID = '[dbo].[Entity_LanguageLink]'[@LanguageID], LanguageName)), "Languages"), !IsBlank(Languages))

Hope this helps.

Sik