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 

 

What does the search use for?

The NameSearchBox is used to search employee name, not to search Notes column. If you want to search Note, you need to add another textinput box named 'NotesSearchBox'.

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

 

If you change formula to filter by Employee table, this formula would face delegation error. This is why I suggest using Gallery1.AllItems to avoid delegation error, but it 's wired, it doesn't work for you. I believe there is something wrong with your codes. 

As an alternative workaround, the delegation error is because 'in' operator is only supported for ("string value" in <column>), not for ("string value" in <column>&<column>), so could you try to create a new 'fullname' column in Employee table to combine fname and sname?

then modify the formula as below, it will have no delegation error.

Set(Var1,LookUp('[dbo].[Employee]',  NameSearchBox.Text in fullname ).employee_id)

 

Sik

Sik, I am trying to search by First Name only at the moment, but get error if I change as below

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

@AndyRossco 

 

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

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

Ok @AndyRossco 

 

Set the Items property of gallery as below:

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

 

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

@AndyRossco 

 

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,
Ok can I sort by employeeID, This would be ok.

Thanks in advance.

Andy

@AndyRossco 

Yes, that's right.

 

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

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

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

Top Kudoed Authors
Users online (20,654)