Gallery Filter by SP List Content

(0) Share

Report

Posted on by Phineas

5,331 Moderator

I have a 'DeptNumber' SP List (below).

I have a Power App with Gallery1.

When a 'Principal' views Gallery1 I'd like the Gallery to be filtered by whether the DeptNo. appears in the 'Title' column of
the 'DeptNumber' SP List.

I am setting a variable at OnStart -
Set(varDepartmentNumber, LookUp(DepartmentCodesCollection, DeptDescription = UserDepartmentFld.Text,Title));

Goal: If the viewer is a 'Principal' filter Gallery1 to show only items where the Department Number is in the Title column of the SP List.
If(IsBlank(CategorySearchComboBox.Selected.Value) && UserJobTitleLbl.Text = "Principal" &&
varDepartmentNumber,
SortByColumns(Filter(LessonCollection,
Status.Value = "Pending Teacher Approval" ||
Status.Value = "Request for More Information" ||
Status.Value = "Lesson Under Teacher Review"),"MeetingDate",SortOrder.Descending))

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Phineas ,

You have quite a few collections/variables within the two formulas mentioned above. Without knowing what they are, we'll not be able to understand what you are doing with the two formulas. Also, what are UserDepartmentFld and UserJobTitleLbl Labels, CategorySearchComboBox?

It seems Gallery1 is for viewing LessonCollection where includes all lessons, then are Lessions coming from another SP list? Is there a User column like a person column in this list?

Best regards,

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

There are two lists/collections.

DepartmentCollection is from the List shown in the initial comment, with two columns - DeptNo ('Title'), and DeptDescription.

LessonCollection (Lesson_List) has all of the Teachers and their information.

The varDepartmentNumber was my attempt at bringing in the 'Title' (DeptNumber) column from the DeptCollection so I can compare it to the LessonCollection 'Departments' column.

Where the viewer is a Principal, any items in the LessonCollection having content in the 'Departments' column that is also in the 'Title' column of the DeptCollection, display ONLY those items (from LessonCollection) in Gallery1.

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Phineas ,

I assume there is no LookUp/Person columns in the Lesson list, or let's say JobTitle and Departments are Text columns in the Lesson list, then try below formula in Items of Gallery1:

If(IsBlank(CategorySearchComboBox.Selected.Value), SortByColumns(Filter(LessonCollection, (Status.Value = "Pending Teacher Approval" || Status.Value = "Request for More Information" || Status.Value = "Lesson Under Teacher Review") && JobTitle = "Principal" && Departments = varDepartmentNumber), "MeetingDate",SortOrder.Descending ), SortByColumns(Filter(LessonCollection, (Status.Value = "Pending Teacher Approval" || Status.Value = "Request for More Information" || Status.Value = "Lesson Under Teacher Review") && JobTitle = "Principal" && Departments = varDepartmentNumber && Category.Value = SearchComboBox.Selected.Value), "MeetingDate",SortOrder.Descending ), )

Best regards,

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

There is no 'JobTitle' in the list that can be used for this purpose.

The job title is coming from the active directory and displayed in the UserJobTitleLbl.Text.

Also, the SP List doesn't have a 'DepartmentNumber' column, only a 'Departments' column, in which is a department description that also contains the department number.

For this reason I am using the 'in' to match the 'DeptCollection - Title' (which is just the Dept. No.) contents to 'Department' in the 'LessonCollection'.

My current formula is as follows. I believe this will get me the result I desire.

Can you confirm the structure of the formula?

/*If Combo Box IsBlank Filter for Principal*/
If(IsBlank(CategorySearchComboBox.Selected.Value) && UserJobTitleLbl.Text = "Principal" && Dept_Number_List_Fld.Selected.DeptNumber in varDepartmentNumber,
SortByColumns(Filter(LessonCollection,
Status.Value = "Pending Teacher Approval" ||
Status.Value = "Request for More Information" ||
Status.Value = "Lesson Under Teacher Review"),"MeetingDate",SortOrder.Descending))

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

Variable varDepartmentNumber is of type Text according to the Set function. So, use = instead of in is fine. Your formula for now will only get records those with the 3 kinds of Status from the Lesson list when the conditions meet. It will not show any records if any of the three conditions mismatch.

Best regards,

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

I've tried the equal previously. I get an 'incompatible' types error, stating one is a Table and the other text - not unlike the error you identified.

It came to me that I have formulas controlling data that is entered into the Power App and uploaded to the SP List.

Consequently, I can filter based on the Status column alone and achieve the same result.

That said, I am still interested in knowing how to write a formula that allows me to filter a Gallery based on a number (Text) in the Title column of one List if that number (Text) appears in the Departments column of of another list - where the number is part of a text steing in that column.

Not an exact match between columns; Rather the number in the Title column of List1 is in the content of the Departments column in List2, that has a complete description, including the number from List1.

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Phineas ,

It doesn't make sense. The variable varDepartmentNumber created by below formula should be of type text because of the LookUp function.

Set(varDepartmentNumber, LookUp(DepartmentCodesCollection, DeptDescription = UserDepartmentFld.Text,Title));

Dept_Number_List_Fld seems like a Combo box where Items property is the DeptNumber list, and Dept_Number_List_Fld.Selected.DeptNumber will only have one DeptNumber which is selected in this Combo box. I cannot find any Table here at all.

It seems the Departments column in List2 is a LookUp column. Please describe all types of columns mentioned in both lists, especially the Lesson list. You can go to the list settings and provide screenshots to explain more clearly:

Only to achieve the requirement you said:

how to write a formula that allows me to filter a Gallery (connects to Lesson list) based on a number (Text) in the Title column of DeptNumber list if that number (Text) appears in the Departments column of Lesson list - where the number is part of a text string in that column.

the formula could be very simple:

Filter(Lesson, StartsWith(Departments, varDepartmentNumber))

Best regards,

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Your formula presumes the 'ListCollection - Departments' column and the 'DeptCollection - Title' column have the same content that can be matched.

This is not the case. The 'DeptCollection - Title' column only has the department number.

The 'ListCollection - Departments' column contains the full department name, including the department number.

All columns are 'single line of text' columns.

This is why I was using the 'in' and couldn't use the 'equal', as none will equal, but so.e will have 'in'.

Does that make sense?

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

I created 2 lists:

In my App, I have also added a Label to "save" the department number, no matter where it came from. In a Gallery which connects to Lesson as data source, I can filter all lessons based on the department number:

Best regards,

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

In my 'LessonCollection'my 'DepartmentDescription' starts with four letters then a hyphen then the 'DeptNumber'.

This is the issue. Can you mak3 a StartsWith that ignores the first five characters then see the next four?

Or, some kind of 'len' or 'LEFT' formula that achieves the goal?

Was this reply helpful? Yes No