cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luffyloo
Regular Visitor

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

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.dropdown.PNG

 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! 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

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:

Column1Column2Languages
TestTestEnglish
TestTestSpanish
TestTest 

 

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

View solution in original post

1 REPLY 1
v-siky-msft
Community Support
Community Support

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:

Column1Column2Languages
TestTestEnglish
TestTestSpanish
TestTest 

 

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

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,563)