Filtering Gallery Based on Calculated Lookup

[CouchTurner]

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?

[RandyHayes]

@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.

[CouchTurner]

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]

@CouchTurner 

What is your formula?

[CouchTurner]

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]

@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!

[CouchTurner]

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]

@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.

[CouchTurner]

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!