cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndyRossco
Helper II
Helper II

Powerapps Gallery Search using multible tables or criteria

Hi,
I am having a problem with the search on gallery, I have 3 tables and I need to search by text values not ID's.
The gallery code is as follows. This is looking at the Notes field, I need to search by either employee name or vanreg.
These values are in 2 other tables, tblEmployee and tblVandetails. These tables hold the unique ID which is stored in the main table tblVanjourneyLog.
SortByColumns(Search([@'[dbo].[tblVanJourneyLog]'], TextSearchBox1.Text, "Notes"), "Notes", If(SortDescending1, Descending, Ascending))
Each field on form has a lookup field as below.
LookUp('[dbo].[Employee]',employee_id = ThisItem.EmployeeID,Concatenate(employee_fname," ",employee_sname))
I need search routine to search by either employee name or vanreg as text not ID's.

 

See attached table relationships, As you can see the tblVanjourneyLog is main table pulling employee details and van details from related tables.

I need to search or filter gallery by either employee ",Concatenate( employee_fname," ",employee_sname ) or Reg.

Basically when user types in text into search text box it looks for similar text in these fields. So user can type Mike and it finds Mike Morgen etc, Or

He types WA18 and finds anything associated with this reg number.

Alternatively if this is too much , need search box to search by either employee or van reg. using separate search boxes.


Thanks in advance for any help on this.
Andy

3 REPLIES 3
v-siky-msft
Community Support
Community Support

@AndyRossco 

tblVanjourneyLog only has the GUID columns, so just need to convert the reg or name to their GUIDs by LookUp function and use their GUIDs to filter the gallery. Please check the following steps, take filtering name as an example:

1. Set NameSearchbox OnChange property:  

 

Set(Var1,LookUp(Gallery1.AllItems,  NameSearchBox.Text in fname&sname ).employee_id)

 

The Gallery1 is from Employee table, Why we use Gallery1.AllItems instead of Employee table is to avoid delegation error, in operation is not delegable in SQL. For more information, please check the following threads: 

function countif Create a report from powerapps with two sharepointlist sources  

2. Set Items property of gallery as below:

 

SortByColumns(Search(Filter([@'[dbo].[tblVanJourneyLog]'], EmployeeID=Var1), TextSearchBox1.Text, "Notes"), "Notes", If(SortDescending1, Descending, Ascending))

 

3. The same thing with Reg, add a RegSearchbox and follow the steps above.

Note: Gallery1 for Employee table and Gallery2 for Van_Details table(not mentioned above) are necessary for avoiding delegation error, you can set them invisible if they are no need to be shown.

Snipaste_2020-01-15_17-30-59.png

Sik

Hi, I tried as you proposed, but I am still having problems

 

See attached screenshots of app, I entered code as you suggested.

SortByColumns(Search(Filter([@'[dbo].[tblVanJourneyLog]'], EmployeeID=Var1), NameSearchBox.Text, "Notes"), "Notes", If(SortDescending1, Descending, Ascending))

 

Set(Var1,LookUp(BrowseGallery1.AllItems, NameSearchBox.Text in employee_fname & employee_sname ).EmployeeID)

 

But seems like I am still getting problems with the NameSearchBox code

 

Kind Regards

 

Andy

@AndyRossco 

Could you please share the error message?

I think the error is from here: 

Set(Var1,LookUp(BrowseGallery1.AllItems, NameSearchBox.Text in employee_fname & employee_sname ).EmployeeID)

 

The Gallery for avoiding delegation error doesn't mean the BrowseGallery1, you need to create a new Gallery, e.g. Gallery1 and set Items property to Employee table. You can set the Visible property of the gallery to false.

Then modify the formula: 

 

Set(Var1,LookUp(Gallery1.AllItems, NameSearchBox.Text in employee_fname & employee_sname ).EmployeeID)

 

Sik

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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