cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lazevedo
Level: Powered On

Searching on a field belonging to a reference table

There are two tables in postgresql which are being used in powerapps through a gateway.

One is projects the other is companies .

Project table references company table.

 project tables and company table

I would like to show a vertical list with the project codes and company names and do a Search on both columns which is of the delegated type (means can handle above 500 entries with no issue).

I’m not being able to do it.

 

Search function on powerapps:

Search('[public].[projects]',TextSearchBox1.Text,"str_projectcode",<company field name here>)

 

I don't know what I should put on <company field name here> in order to search over a field from a referenced table (company).

So I don’t know how I can filter the projects based on the “name” field of the company.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Searching on a field belonging to a reference table

Hi @lazevedo,

It doesn't seem possible that the formula works as the Items property of the gallery but you can't make a collection using that same formula.  You should be able to create a Collection simply by using ClearCollect(collectionname, Formula) as the OnSelect function of a button or in the OnVisible property of a screen.  You should be able to see if the Collect() function works by looking at the View -Collections.Capture.PNG

 

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Searching on a field belonging to a reference table

Hi Lazevido, Add the company name to the Items property of the projects gallery. by using the AddColumns() function as follows. Search(AddColumns('[public].[projects]',"CompanyfName",Lookup("[Company]" ,id=company_ref, Name)), "TextSearchBox1.Text, "str_projectcode", "CompanyName") then you can search on it.
Super User
Super User

Re: Searching on a field belonging to a reference table

You could also create a collection in the OnSelect property of the screen with ClearCollect(colProjects, AddColumns('[public].[projects]',"CompanyName",Lookup("[Company]" ,id=company_ref, Name))). then you could substitute colProjects in the Items property of the gallery: Search(colProjects, "TextSearchBox1.Text, "str_projectcode", "CompanyName") Using the collection method would provide an extremely fast search for the User. You could try it both ways and see which works best for you.
Super User
Super User

Re: Searching on a field belonging to a reference table

For some reason I can't edit my posts but there is an error in my search string. There should not be a " before the TextSearchBox1.Text element in the formula.
lazevedo
Level: Powered On

Re: Searching on a field belonging to a reference table

Thanks for the quick reply.

Just tried the first approach with the AddColumns, no error but the gallery simply doesn't load, it's on an infinit "searching" status, at least the list is empty and the "loading" dots on top keep moving (image below).

I'm going to try the second approach but any ideas on why I don't get any results ?

 

loading.png

lazevedo
Level: Powered On

Re: Searching on a field belonging to a reference table

Tried the second approach of the colection by adding the following function to the BrowseScreen onStart field:

ClearCollect(projects,AddColumns('KIM Projects',"CompanyName",LookUp('KIM Companiess' ,lid=ref_15cf77be, str_firma_037e76ad)))

Means I've just created a new collection called "projects" with the new column "CompanyName".

But then I just try to show it on the gallery by putting "projects" on the function and nothing is shown:

collection1.png

collections-2-empty.PNG

 

 

 

  

Super User
Super User

Re: Searching on a field belonging to a reference table

Hi @lazevedo

If you put it in the OnStart property of the screen, you will need to close your app and reopen it before it will work.  Put it in the OnVisible property of the screen.  One reason why the first method seems to stall could be that your table is large. Plus, you could run into delegation issues if you have more than 2000 rows in the table. 

PowerApps Staff Mr-Dang-MSFT
PowerApps Staff

Re: Searching on a field belonging to a reference table

@lazevedo,

Just a hunch, but if you're not seeing any data I'm thinking you may be experiencing a bug with the 'explicit column selection' feature. I suggest turning it off in App Settings.

 

See the last note here:

https://powerapps.microsoft.com/en-us/support/

 

lazevedo
Level: Powered On

Re: Searching on a field belonging to a reference table

Thanks for the quick replies, I just tried it but doesn't seem that anything is happening, simply doesn't show anything, even when I click the "play" button to "run" the app:

screen-onvisible.png

 Only when I use the AddColumn expression directly on the data of the gallery does it show the entries, but not when I reference the created collection.

gallery-empty.png

Super User
Super User

Re: Searching on a field belonging to a reference table

Hi @lazevedo,

It doesn't seem possible that the formula works as the Items property of the gallery but you can't make a collection using that same formula.  You should be able to create a Collection simply by using ClearCollect(collectionname, Formula) as the OnSelect function of a button or in the OnVisible property of a screen.  You should be able to see if the Collect() function works by looking at the View -Collections.Capture.PNG

 

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,759)