cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering SQL table for use in a dropdown

I'm have an app with dropdowns populated by many SQL tables

 

Ownership,

Source,

Resolution

 

are some examples of the tables.  Rather than have multiple SQL tables i was hoping to have a single 'List of Values' table with a type field that i could filter on per dropdown.

 

In my Ownership dropdown I used this code to only return the value B@H (which worked):

 

Filter('[dbo].[B@H_DRAINAGE_OWNERSHIP]',(NAME = "B@H"))

 

so i tried to use this code to return all the Name values with the Type of "BB":

 

Filter('[dbo].[B@H_DRAINAGE_OWNERSHIP]',(TYPE = "BB"))

 

I had hoped this would only let me see the values that had a type of BB, but it didn't work, error message showed "Invalid Name". i want to have one table for my list of values and use my existing SQL tables as Types within the List of Values table.

1 ACCEPTED SOLUTION

Accepted Solutions

Hello

 

In your initial post you write Type = "BB", but in the hypothetical table in your latest respone you don't have a type with the text BB. In which table is this data stored, in B@H_DRAINAGE_OWNERSHIP?

 

If you want to display the Owner-items, you would set the Items-property of the dropdown to:

Filter(Correct DB-table; TYPE = "Owner")

 

and as @v-micsh-msft writes, then set the Value-property of the dropdown to Name. Same will apply for the Result-dropdown, but instead of "Owner" you filter on the string "Result".

View solution in original post

6 REPLIES 6
v-micsh-msft
Community Support
Community Support

Would you please share a reference of your table columns?

The filter function should be written as below, if you would like the Dropdown to only show TYPE field.

Filter('[dbo].[B@H_DRAINAGE_OWNERSHIP]',TYPE = "BB").TYPE

You may also configure the Value for this dropdown to TYPE.

14.PNG

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BitLord69
Power Participant
Power Participant

Hi,

 

To me it looks like you're entering the wrong column name. Are you sure the column you want to filter on is called TYPE? What's your DB/table definition? 

Anonymous
Not applicable

It (hypothetically) would look something like this

 

ID          type                   Name       

10           Owner              Private

20           Owner              Social

30           Result              More investigation needed

40           Result              Resolved

 

The Ownership dropdown i ony want to be able to see names relating to TYPE 'OWNER' and the Result Dropdown the values in TYPE 'RESULT'

 

i attach an image of current database strucure and an image of my app, i wanted to get away from multiple tables in my DB for List of Values/Dropdowns

Hello

 

In your initial post you write Type = "BB", but in the hypothetical table in your latest respone you don't have a type with the text BB. In which table is this data stored, in B@H_DRAINAGE_OWNERSHIP?

 

If you want to display the Owner-items, you would set the Items-property of the dropdown to:

Filter(Correct DB-table; TYPE = "Owner")

 

and as @v-micsh-msft writes, then set the Value-property of the dropdown to Name. Same will apply for the Result-dropdown, but instead of "Owner" you filter on the string "Result".

View solution in original post

Anonymous
Not applicable

Sorry...inconsistent examples but the same idea!  My Type field would have a value and your description makes sense.

 

looks like i've just muddled where the brackets go

 

I'll give it a whirl, thank you.

Hi @Anonymous,

 

Do you need any further help on this?

 

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (36,751)