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
Helper I
Helper 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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Carousel April Dunnam Updated 768x460.jpg

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

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