Powerapps Gallry Search using multible tables

(0) Share

Report

Posted on by 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.

Thanks in advance for any help on this.

Andy

Categories:

General Questions

Annotation 2020-0...

All responses (4)

Answers (0)

AndyRossco 64 on at

Like (0)

Report

Re: Powerapps Gallry Search using multible tables

Here is view of SQL links

Annotation 2020-0...
AndyRossco 64 on at

Like (0)

Report

Re: Powerapps Gallry Search using multible tables

Here is view of app.

POWEAPPSVIEW.png
AndyRossco 64 on at

Like (0)

Report

Re: Powerapps Gallry Search using multible tables

Hello v-yutliu-msft, Thank you so much for your reply.

See below 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.
v-yutliu-msft on at

Like (0)

Report
Re: Powerapps Gallry Search using multible tables

Hi @AndyRossco ,

Do you want to filter tblVanJourneyLog based on either employee name or vanreg?

Does tblEmployee have fields:employee_fname,employee_sname,employee_id

tblVandetails have fields:vanreg,employee_id

tblVanJourneyLog have field:EmployeeID?

If so, try this formula:

Filter(@'[dbo].[tblVanJourneyLog]', EmployeeID in Search( AddColumns( '[dbo].[tblEmployee]',"Name",Concatenate( employee_fname," ",employee_sname ) ), TextSearchBox1.Text,"Name" ).employee_id || EmployeeID in Search( '[dbo].[tblVandetails],TextSearchBox1.Text,"vanreg" ).employee_id )

Best regards,