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

Display certain items in one list by using an item in a second list

I have two lists. One has the item details and the other is a list of the that item multiple times. The first list is a list of fire extinguishers throughout the site. The second list has the monthly inspections on each item in the first.

 

I want to find the items in the first list that are not in the second list for a certain month and year. Basically I want to find items that were not inspected in a certain month.

 

List1 (one)

DepartmentCode

Location

Type

Size

 

List2 (many)

DepartmentCode

InspectionDate

Inspector

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-siky-msft
Community Support
Community Support

Hi @dmac73 ,

 

What is the Data type of DepartmentCode column in list2? Text or LookUp type?

 

For example, this month, 8/2020.

If the DepartmentCode column is Text type:

LookUp(List1, DepartmentCode in Distinct(Filter(list2, Month(InspectionDate)=8, Year(InspectionDate)=2020), DepartmentCode).Result)

 If the DepartmentCode column is  LookUp type:

 

LookUp(List1, DepartmentCode in Distinct(Filter(list2, Month(InspectionDate)=8, Year(InspectionDate)=2020), DepartmentCode.Value).Result)

Hope this helps.

Sik

 

View solution in original post

7 REPLIES 7
ErinCannon
Resolver I
Resolver I

I would load the second list into a collection and then add columns for the month (and year if needed) of the inspection date. So something like:

ClearCollect(List2Collection, List2); 
AddColumns(List2Collection, "MonthNum", Value(Text(DateValue(InspectionDate), "mm")));


Then filter your collection by the month you've selected and then collect your first list where the ID is not in the list 2 collection for that month.


ClearCollect(List2CollectionforMonth, Filter(List2Collection, MonthNum = Selected Month);
ClearCollect(List1Collection, Not(ID in List2CollectionforMonth.List1ID))

Thanks for the code, but I am new to this. 

 

How would I use a dropdown with the months and a label with a list of fire extinguishers not inspected? 

 

Thanks.

One way to do it would be to connect to an excel table of dates that can load data into your app.  I've attached an example of what I'm talking about.

 

I would use a combo box instead of a straight dropdown because that lets you use different columns of the record for your reference instead of the ones you are using for the filtering.

 

In the OnVisible property of the sheet I would collect my data like I suggested earlier. In this example I didn't do the second collection of items not in this list, but that code from above should give you a good start, still.

ErinCannon_0-1597417129066.png

Then insert your combo box and set its DataSource to your MonthTable that you connected to. In my case I filtered my list to only show months that are this month or earlier. And set my default selected item to the current month.

ErinCannon_1-1597417345705.png

ErinCannon_2-1597417430568.png

Then insert a gallery and set its Item property to the list you want to show filtered by date.

 

ErinCannon_3-1597417876408.png

 

 

Great info, but I am having issue with the AddColumns. They do not pull the month into the added columns.

 

dmac73_0-1597422847102.png

Code:

dmac73_1-1597422878367.png

 

The field in the SharePoint List is set to DateTime (Short Date)

 

Thanks again for the work.

Try using single quotes or no quotes on the InspectionDate field in your date value. If that field is already formatted as a date in SharePoint, you might not need to use the DateValue part of the equation at all.  

 

Happy to help. My next project is going to use this same logic, so it was a good exercise for me. 

Tried both, no go. I will keep working on it.

 

Thanks again. 

v-siky-msft
Community Support
Community Support

Hi @dmac73 ,

 

What is the Data type of DepartmentCode column in list2? Text or LookUp type?

 

For example, this month, 8/2020.

If the DepartmentCode column is Text type:

LookUp(List1, DepartmentCode in Distinct(Filter(list2, Month(InspectionDate)=8, Year(InspectionDate)=2020), DepartmentCode).Result)

 If the DepartmentCode column is  LookUp type:

 

LookUp(List1, DepartmentCode in Distinct(Filter(list2, Month(InspectionDate)=8, Year(InspectionDate)=2020), DepartmentCode.Value).Result)

Hope this helps.

Sik

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,533)