Search and then filter results from Excel data source

[Petecmsmith]

Hi all,

New caller long time listener.

A real newbie with PowerApps trying to create a really useful app for my team during this current climate and downturn in actual work.

The idea
Using a spreadsheet held in OneDrive I have over 2000 rows of questions and answers (plus fields for various other bits of info) that I want to create my app to interrogate. I want to allow my users to search for keywords throughout the data and once provided with the results to be able to refine using drop-down filters based on the other info held in the table.

So far

I have the data source set up and connected and an app that does search and provide results which we can expand on and edit and update. All good. However, I have noticed that I'm possibly not getting 100% accurate results as there are some keywords that I know exist that I cannot bring up in results. Is there a limit to the amount of data in Excel?

Also I am struggling to work out how to then apply filters to the results, is there an example somewhere you can point me to?

Thank you and stay safe

[v-xida-msft]

Hi @Petecmsmith ,

If all of these Product Tables have same data structure, please consider set the DataSource property of the Detail Form/Edit form to following:

If(
   ProductDropdown.Selected.Value = "ProductA",
   ProductA_Table,
   ProductDropdown.Selected.Value = "ProductB",
   ProductB_Table,
   ProductDropdown.Selected.Value = "ProductC",
   ProductC_Table,
)

Set the Item property of the Display form or Edit form to following:

If(
   ProductDropdown.Selected.Value = "ProductA",
   LookUp(ProductA_Table, PrimaryColumn = Gallery1.Selected.PrimaryColumn),
   ProductDropdown.Selected.Value = "ProductB",
   LookUp(ProductB_Table, PrimaryColumn = Gallery1.Selected.PrimaryColumn),
   ProductDropdown.Selected.Value = "ProductC",
   LookUp(ProductC_Table, PrimaryColumn = Gallery1.Selected.PrimaryColumn),
)

If these Product Tables do not have same data structure, I think the Display form/Edit form control could not achieve your needs. You need to add individual Label/TextInput boxes within your app for displaying data from your Product Table/collecting data back to your Products Table.

After that, you could use Patch function to add/edit records in your Products Table. 

Please take a try with above solution, check if the issue is solved.

Best regards,

[Petecmsmith]

Thank you.

Just working through this now, in the item property of the edit form where you state PrimaryColumn = Gallery1.Selected.PrimaryColumn - should I replace Primary Column with the name of the first column of the data source (the product tables are all the same structure)?

And Gallery1.Selected.PrimaryColumn what do I need to consider here?

When simply replacing the PrimaryColumn with the name of my first column in my table (ID) I get an error Invalid use of '.'

[v-xida-msft]

Hi @Petecmsmith ,

The PrimaryColumn represents the column in your Excel table, which could identify one record uniquely. Please need to replace it with actual field name from your Excel Table.

Please make sure the ID column you want to reference is existed in your Excel table. You could type the following formula:

Gallery1.Selected.

then all available columns would be displayed after the . (property selector) due to the Formula Intelligence in PowerApps Studio. You could find your proper field name from the available column list under the  . (property selector).

Best regards,

[Petecmsmith]

Great thanks, nearly there although getting an error in my usage of logical statements. Getting the error Unexpected characters and needing an else value. Here is my formula completely with table names, column names included:

If(

ProductDropdown.Selected.Value = "AccessLMS", LookUp(AccessLMS, Question = BrowseGallery1.Selected.Question), ProductDropdown.Selected.Value = "GameBrain", LookUp(GameBrain, Question = BrowseGallery1.Selected.Question), ProductDropdown.Selected.Value = "EarlyPay", LookUp(EarlyPay, Question = BrowseGallery1.Selected.Question), ProductDropdown.Selected.Value = "CoreHR", LookUp(CoreHR, Question = BrowseGallery1.Selected.Question), ProductDropdown.Selected.Value = "PeopleHR", LookUp(PeopleHR, Question = BrowseGallery1.Selected.Question), ProductDropdown.Selected.Value = "SelectHR", LookUp(SelectHR, Question = BrowseGallery1.Selected.Question),

)

[timl · Accepted solution]

Hi @Petecmsmith 

You might want to consider using the Switch function which would result in a cleaner looking formula.

There are more details about the function here.

https://docs.microsoft.com/en-gb/powerapps/maker/canvas-apps/functions/function-if 

Switch(
      ProductDropdown.Selected.Value,
      "AccessLMS", 
      LookUp(AccessLMS, Question = BrowseGallery1.Selected.Question), 
      "GameBrain", 
      LookUp(GameBrain, Question = BrowseGallery1.Selected.Question), 
      "EarlyPay", 
      LookUp(EarlyPay, Question = BrowseGallery1.Selected.Question), 
      "CoreHR", 
      LookUp(CoreHR, Question = BrowseGallery1.Selected.Question),   
      "PeopleHR", 
      LookUp(PeopleHR, Question = BrowseGallery1.Selected.Question),   
      "SelectHR", 
      LookUp(SelectHR, Question = BrowseGallery1.Selected.Question)
)

[Petecmsmith]

Thanks, timl - worked a treat.

Thanks to all other contributors to this thread. Much obliged to you all.

Stay safe