cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Pall22
Level: Powered On

Searching excel table - HELP

Hi All, 

 

To explain: 

Excel table = 'Database' 

The 'Database' contains a column of peoples names. This is the result I want to show on the powerapp when the user searches. 

Also within the 'Database' are columns such as: 'BrandBuilding', 'BrandManagement' .......... cells under these columns contain 'Yes' or 'No'. 

 

PersonNameBrand BuildingBrand ManagementNPIGTM
JohnYesNoNoYes
GaryNoNoNoNo
AndrewNoNoYesNo
LukeYesYesNoNo
TomYesYesNoNo

 

I have 2 dropdowns and would like the user to first choose one of the categories (brand building, brand management etc) and the 2nd dropdown will have either 'yes' or 'no' as a choice. 

 

Is there a formula to search/filter the table to show all 'PersonName' that the search satisfies?

 

Many thanks, 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Searching excel table - HELP

Hi @Pall22 ,

Since they are all text, the formula will be easier:

If(var="a",Filter(tablename,Brand Building=Dropdown2.Selected.Value),
   var="b",Filter(tablename,Brand Management=Dropdown2.Selected.Value)
)

 The list box's setting is here:7174.PNG

Please replace the tablename with your tablename.

 

 

Best regards,

Community Support Team _ Phoebe Liu

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

View solution in original post

9 REPLIES 9
Nyk
Level: Powered On

Re: Searching excel table - HELP

I believe you are just wanting to show all of the columns and its contents, but only show the row of a specific person. 

Try this:

Filter(TableName, PersonName = Search.Text)

 
Where TableName is the name of your table, PersonName is the column header of the person's name, and Search.Text is where the user would be inputting the person's name that you want to filter the table by.

If this is not what you meant, please try to guide me in the right direction Smiley Happy 

Pall22
Level: Powered On

Re: Searching excel table - HELP

Thanks for the quick reply Smiley Happy

 

Ideally I would just like to show the PersonName in a text box. 

 

For example: 

I choose 'Brand Building' and 'Yes'

A text box will then show me the person or persons from this search. 

 

Thanks

Nyk
Level: Powered On

Re: Searching excel table - HELP

Is your data going to be static, or will you be adding onto the database every so often? 

Community Support Team
Community Support Team

Re: Searching excel table - HELP

Hi @Pall22 ,

Coule you tell me the data type of Brand Building, Brand Management and PersonName?

Waht's more, the filtered PersonName may not be one, so I suggest you display it in a list box.

For example, if you choose Brand Building and Yes, the result of PersonName will be three.

I've made a similar test for your reference:

I assume that Brand Building, Brand Management are choice column ,PersonName is text column.

1)Set drop down1's Items: ["Brand Building","Brand Management"]

Set drop down1's OnChange: 

If(Dropdown1.Selected.Value="Brand Building",Set(var,"a"),Dropdown1.Selected.Value="Brand Management",Set(var,"b"))

 Set drop down2's Items: ["Yes","No"]

2)Set the list box's Items:

If(var="a",Filter(tablename,Brand Building.Value=DropDown2.Selected.Value),
   var="b",Filter(tablename,Brand Management.Value=DropDown2.Selected.Value)
)

Set the list box's Value: PersonName

If you insist in using textbox, I suggest you insert a lable control.

Set the label's Text:

ConCat(
If(var="a",Filter(tablename,Brand Building.Value=DropDown2.Selected.Value), var="b",Filter(tablename,Brand Management.Value=DropDown2.Selected.Value) ),PersonName&",")

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Pall22
Level: Powered On

Re: Searching excel table - HELP

@Nyk Data will be static 

Pall22
Level: Powered On

Re: Searching excel table - HELP

@v-yutliu-msft Data type is value (text) for PersonName, brand building and brand management. 

 

Also: "Set the list box's Value: PersonName" - I can not see an option to change value with a list box. 

 

My apologies I am very new to powerApps, 

 

Many thanks for your help,

Andre 

 

Community Support Team
Community Support Team

Re: Searching excel table - HELP

Hi @Pall22 ,

Since they are all text, the formula will be easier:

If(var="a",Filter(tablename,Brand Building=Dropdown2.Selected.Value),
   var="b",Filter(tablename,Brand Management=Dropdown2.Selected.Value)
)

 The list box's setting is here:7174.PNG

Please replace the tablename with your tablename.

 

 

Best regards,

Community Support Team _ Phoebe Liu

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

View solution in original post

Pall22
Level: Powered On

Re: Searching excel table - HELP

@v-yutliu-msft I have managed to change the listbox items to: 

If(var="a",Filter(tablename,Brand Building=Dropdown2.Selected.Value),
   var="b",Filter(tablename,Brand Management=Dropdown2.Selected.Value)
)

However, when i update the data source to my 'tablename' and put value as PersonName it removes my formula and only shows the 'tablename' next to items tab.  Is there a way to lock the datasource for the screen?

 

Thanks,

Pall22
Level: Powered On

Re: Searching excel table - HELP

All working now Smiley Happy

 

Thank you for the help!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 346 members 5,153 guests
Recent signins:
Please welcome our newest community members: