Searching excel table - HELP

[Anonymous]

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'. 

PersonName	Brand Building	Brand Management	NPI	GTM
John	Yes	No	No	Yes
Gary	No	No	No	No
Andrew	No	No	Yes	No
Luke	Yes	Yes	No	No
Tom	Yes	Yes	No	No

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, 

[Nyk]

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 🙂 

[Anonymous]

Thanks for the quick reply :), 

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]

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

[v-yutliu-msft]

Hi @Anonymous ,

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

[Anonymous]

@Nyk Data will be static 

[Anonymous]

@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 

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

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:

Please replace the tablename with your tablename.

Best regards,

Community Support Team _ Phoebe Liu

[Anonymous]

@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,

[Anonymous]

All working now 🙂

Thank you for the help!