cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Meneghino
Level 10

Re: linking two tables

Please try just Main_Query, nothing else.

 

PS No filtering or sorting or anything.  This is because I think the problem is with the name of your data source.

sajarac
Level 8

Re: linking two tables

Hi, well here is what happened:

 

I was basically changing fields names in my database to avoid any kind of weird characteres. I have deleted my subsite create a new new one uploaded my two new tables, then in PA I have created a new connection changed all of the field names now I don't have _x0020, etc.

 

So I check again the sort condition for my gallery and this is what I get:

 

SortByColumns(If(drpfy.Selected.Value="All FY",Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name","AssessorName"), Filter( Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name"), drpfy.Selected.Value=FiscalYear))),"Assessment_Name",If(SortDescending1,Ascending,Ascending))

 

Untitled.png

sajarac
Level 8

Re: linking two tables

Good News, finally it works!!!!

 

using this formula:

 

SortByColumns((If(drpfy.Selected.Value="All FY",Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name","SRName","AssessorName"), Filter( Search( MainQuery, Proper(TextSearchBox1.Text), "Assessment_Name"), drpfy.Selected.Value=FiscalYear))),"Assessment_Name",If(SortDescending1,Ascending,Ascending))

 

I still receiving a warning but is working

 

Untitled.png

 

Thank you very much for your kind cooperation.

 

Regards

 

 

 

View solution in original post

Meneghino
Level 10

Re: linking two tables

hi @sajarac

glad to see that it is finally resolved.

I think the reason you get the warning is that the Search function is not delegated in SharePoint, so that it will work slowly and only return data from the first 500 records.

However, there is a very valid alternative, the StartsWith function, which is delegated in SharePoint.  So please try something like this:

SortByColumns(
	Filter(MainQuery,	
		StartsWith(Assessment_Name, Proper(TextSearchBox1.Text)) ||	
		StartsWith(SRName, Proper(TextSearchBox1.Text)) ||	
		StartsWith(AssessorName, Proper(TextSearchBox1.Text)),	
		If(drpfy.Selected.Value="All FY", true, drpfy.Selected.Value=FiscalYear),
	"Assessment_Name",If(SortDescending1,Ascending,Descending)
	)

Also, I am somewhat surprised that your code works, since there are a couple of possible errors:

1) The names of columns are in double quotes for the Search function, which means they are taken as text and not the column values.  Please note that some functions take the columns names with quotes (i.e. SortByColumns) and some without (i.e. Sort)

2) You have Ascending twice at the end

sajarac
Level 8

Re: linking two tables

@Meneghino, good morning and thank you very much for your reply. I've tried your formula but doesn't work, see screenshot, and you are right is taking more time to load data.

 

Untitled.png

Meneghino
Level 10

Re: linking two tables

Hi, I am surprised it is not working at all. There has been an issue in case the search box is empty.  What happens if you type something in there?

In any case, if you have a version that works for you, then let it be.

Helpful resources

Announcements
thirdimage

Power Apps 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

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (4,768)