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

[dmac73]

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

[ErinCannon]

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))

[dmac73]

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.

[ErinCannon]

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.

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.

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

[dmac73]

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

Code:

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

Thanks again for the work.

[ErinCannon]

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. 

[dmac73]

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

Thanks again. 

[v-siky-msft · Accepted solution]

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