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

Powerapps Gallery Search with Muliple SQL Linked Tables

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

 

21 REPLIES 21

@AndyRossco 

Please try this: 

If(IsBlank(Var1),

SortByColumns([@'[dbo].[tblVanJourneyLog]'],"EmployeeID",If(SortDescending1, Descending, Ascending)),

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

Reference: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-sort 

Sik

Sik,

      Thanks so much for your help and patience.

It works now.

 

Kind Regards

Andy😁

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

Top Solution Authors
Top Kudoed Authors
Users online (18,726)