Powerapps Gallery Search with Muliple SQL Linked Tables

[AndyRossco]

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

[v-siky-msft]

@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

[AndyRossco]

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))

[v-siky-msft]

@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

[AndyRossco]

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

[v-siky-msft]

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

[AndyRossco]

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

[v-siky-msft]

@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

[AndyRossco]

Sik,
Ok can I sort by employeeID, This would be ok.

Thanks in advance.

Andy

[v-siky-msft]

@AndyRossco 

Yes, that's right.

[AndyRossco]

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