You’re offline. This is a read only version of the page.
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
Sik,
Thanks so much for your help and patience.
It works now.
Kind Regards
Andy😁
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,
What I will I need to change In Items code to enable this ?
If(IsBlank(Var1), [@'[dbo].[tblVanJourneyLog]'], Filter([@'[dbo].[tblVanJourneyLog]'], EmployeeID=Var1))
Andy
Sik,
Ok can I sort by employeeID, This would be ok.
Thanks in advance.
Andy
No, All sort function can only sort the original columns of Data source.
tblVanJourneyLog only contains Employee ID and Van ID, there is no way to be sorted by Employee Name.
Sik
Sik,
That worked Thanks.
Just one last thing the sort button is not working obviously since I removed the SortByColumns command from the Items code.
Is it possible to still have sort routine within this code or the sort button
If(IsBlank(Var1), [@'[dbo].[tblVanJourneyLog]'], Filter([@'[dbo].[tblVanJourneyLog]'], EmployeeID=Var1))
UpdateContext({SortDescending1: !SortDescending1})
Andy
Ok @AndyRossco
Set the Items property of gallery as below:
Share
Report
All responses (
Answers (If(IsBlank(Var1), [@'[dbo].[tblVanJourneyLog]'],
Filter([@'[dbo].[tblVanJourneyLog]'], EmployeeID=Var1))And append the following code to OnSelect property of Refresh icon:
Set(Var1, Blank())Sik
Hi Sik,
That worked to filter records by name, I now need to show all records on form load initially, then search when required. So when form loads it shows all records and when refresh will show all records also.
See attached last code I entered as you suggested.
Kind Regards
Andy
Employee_fname doesn't belong to 'tblVanJourneyLog' table, so the filter and SortByColumns cannot work well.
Please try this:
Filter([@'[dbo].[tblVanJourneyLog]'], EmployeeID=Var1)
Sik
#1
WarrenBelz
637
Most Valuable Professional
#2
stampcoin
570
Super User 2025 Season 2
#3
Power Apps 1919
473
