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 

Have you ever tried the workaround I posted in this thread?

https://powerusers.microsoft.com/t5/Building-Power-Apps/Powerapps-Gallery-Search-using-multible-tabl... 

Sik

[AndyRossco]

Hi Sik, Thanks for reply.
Yes I tried your workaround, I have attached screenshots of error messages.
This has really confused me now, not clear how this all works with Var1 etc.

Please advise what next to resolve if possible.

Also can you let me know if you are getting screenshots ok or do you want me to insert into here as pictures.

Kind Regards
Andy

[v-siky-msft]

@AndyRossco 

The error shows the variable has been defined as other types in some places.

You can try to change another variable name, and try again.

Sik

[AndyRossco]

Hi Sik,

          Tried setting variable to Var2,Var20 and also VarE still no luck.

How do I check where variables are assigned in App.

Also how does this code work ?

Thanks in Advance

Andy

[v-siky-msft]

@AndyRossco 

Go to File - Variables -Definitions, you can find all definitions of variable, then check them.

BTW, Have you mixed the Set and UpdateContext functions? You can't use these two functions together to define a variable.

Sik

[AndyRossco]

Sik,
I have attached screenshots of variable errors etc.

[v-siky-msft]

@AndyRossco 

That's wired.

Could you try to use this codes to check if there is any error?

Set(Var1,LookUp(Employee,  NameSearchBox.Text in employee_fname & employee_sname ).employee_id)

If it still doesn't work, I suggest you create a new app and try my formula again to check if the error is fixed.

Sik

[AndyRossco]

Hi Sik,

           I have edited code and removed second Gallery1.

This has removed some errors as attached screen shots.

However it is not showing all records in application only the first record. I have 3 Records in table.

When I try and search it only returns first record if I leave search box empty. No other records shown ?

Code for SORT is using formulae below which has Notes Field, not sure this is correct ?

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

Also when I look at Var1 in Variables it is showing number 50 which is EmployeeID for Mike Morgan which is correct.

But this is not changing when I search. It reverts to no value unless I search by empty string.

Thanks for your help in advance

Andy

[AndyRossco]

Dear Sik,
I have noticed Var1 is retrieving the correct ID when you type in first name.
So this code is good
Set(Var1,LookUp('[dbo].[Employee]', NameSearchBox.Text in employee_fname).employee_id)

The problem is the BrowseGallery1 Items code, this is looking at Notes Column
I have tried changing this with no luck, expects Text value to match NameSearchBox

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