cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CouchTurner
Frequent Visitor

Filtering Gallery Based on Calculated Lookup

I have a gallery, and one of the values on the row is created by using a lookup formula (NOT A SP LOOKUP COLUMN) from two SharePoint list. I also want it to filter based on a combo box. How do I do this?

8 REPLIES 8
RandyHayes
Super User
Super User

@CouchTurner 

You need to move the Lookup function out of your gallery row and into the items property of the gallery.

 

Example, if you are doing a lookup IN a gallery label of, let's say a Company - LookUp(companyList, CompanyID=ThisItem.CompID, Title)

 

If you want to sort and filter on the Company Name ("title"), then you need to move this into your Items property.

Sort(
    Filter(
        AddColumns(yourListOrOtherFilter,
            "_companyName", LookUp(companyList, CompanyID=CompID, Title)
        ),
        StartsWith(_companyName, "A")
    ),
    _companyName
)

Note, the StartsWith and Sorting are all just examples...not required, just to demonstrate the use of moving the lookup to the Items property.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
CouchTurner
Frequent Visitor

So I know that I want to filter based on a value solved from a lookup. But I have a delegation issue. Is that normal? 

RandyHayes
Super User
Super User

@CouchTurner 

What is your formula?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
CouchTurner
Frequent Visitor

SortByColumns(
Search(
If(
And(
IsBlank(Combobox1.Selected.Result),
IsBlank(Combobox2.Selected.Result)
),
Filter(
'Sharepoint List',
Company = LookUp(companyList, CompanyID=CompID, Title),
'Starting Date' >= From_2.SelectedDate,
'End Date' <= To_1.SelectedDate
)
)
)
)

I kept the company example from your previous response.

 

Thanks!

RandyHayes
Super User
Super User

@CouchTurner 

That can't be your complete formula!  It is missing parameters for the Search and the SortByColumns, and you have not "else" condition for your If!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
CouchTurner
Frequent Visitor

Ah right sorry. I thought I copied the whole formula. And just to make sure, I want to see if I can filter the data based on a lookup. My primary problem is that when I change a value, I not only want to see the value changed on the row, but also I want to see that value's company change as well.  And for the row to disappear from the gallery if it does not fit with the current filter on the gallery 

For ex.) If I change the row's compid, I want to see that row's company, let's say Walmart, to automatically change to another company, let's say Microsoft. And if the current gallery is filtered based on Walmart, I do not want to see the row I just changed on the gallery because I just edited it. 

 

SortByColumns(
Search(
If(
And(
IsBlank(Combobox1.Selected.Result),
IsBlank(Combobox2.Selected.Result)
),
Filter(
'Sharepoint List',
Company = LookUp(companyList, CompanyID=CompID, Title),
'Starting Date' >= From_2.SelectedDate,
'End Date' <= To_1.SelectedDate
),
If(
And(
IsBlank(Combobox1.Selected.Result),
!IsBlank(Combobox2.Selected.Result)
),
Filter(
'Sharepoint List',
Company = LookUp(companyList, CompanyID=CompID, Title),
'Starting Date' >= From_2.SelectedDate,
'End Date' <= To_1.SelectedDate
'Column_2' = Combobox2.Selected.Result),
If(
And(
!IsBlank(Combobox1.Selected.Result),
IsBlank(Combobox2.Selected.Result)
),
Filter(
'Sharepoint List',
Company = LookUp(companyList, CompanyID=CompID, Title),
),
'Starting Date' >= From_2.SelectedDate,
'End Date' <= To_1.SelectedDate
'Column_1' = Combobox1.Selected.Result
),
If(
And(
!IsBlank(Combobox1.Selected.Result),
!IsBlank(Combobox2.Selected.Result)
),
Filter(
'Sharepoint List',
Company = LookUp(companyList, CompanyID=CompID, Title),
'Starting Date' >= From.SelectedDate,
'End Date' <= To.SelectedDate,
'Column_1' = Combobox1.Selected.Result,
'Column_2' = Combobox2.Selected.Result
)
)
)
)
),
'TextInput1'.Text,
"field_14"
),
varSortPriority,
If(
SortDescending,
Ascending,
Descending
)
)

 

RandyHayes
Super User
Super User

@CouchTurner 

Yes, that formula is more concise...however, it does not seem to be a direct copy/paste as there are several errors in it (syntax).

 

But it doesn't make a difference as the formula is incorrect in general.  You do NOT want to do a lookup as part of a Filter.  It will always be non-delegable.

 

Your formula should be the following:

With({_items: Filter('Sharepoint List', 'Starting Date' >= From_2.SelectedDate, 'End Date' <= To_1.SelectedDate)},

    SortByColumns(
        Search(
            AddColumns(
                Filter(_items,
                    (IsBlank(Combobox1.Selected.Result) || 'Column_1' = Combobox1.Selected.Result) &&
                    (IsBlank(Combobox2.Selected.Result) || 'Column_2' = Combobox2.Selected.Result)
                ),
                "_companyName", LookUp(companyList, CompanyID=CompID, Title)
            ),
            TextInput1.Text, "field_14"
	),
	varSortPriority,
        If(SortDescending, Ascending, Descending)
    )
)

 

Also, try to avoid using If statements in a filter/items formula. This only leads to duplication of work - as in the case of your repeated filter functions.

 

With the above formula, you have the same functionality as your posted one except it will be delegable and you will then have a _companyName column to use for further sorting and filtering as needed.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
CouchTurner
Frequent Visitor

Ah thanks for the simplified code; it's much easier to read now. However, the lookup part is still not working properly. 

For the sake of simplification:

I have two sharepoint list. 

SP1. The main sharepoint list that has multiples of distinct items listed from the second sharepoint list.

SP2. One is categorical in a sense that there are  only distinct items on one column and the other column shows what category there in.

 

"companyName", Lookup(SP2, SP2_column 1= SP1_column6, SP2_column2).

Would that be the right format? Also should I probably add a dropdown list to filter the gallery based on the category?

 

Thanks!  

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,720)