Let's say I have the following SharePoint list.
| Person | Device | DateReturned |
| Sam B | Laptop | 7/1/2024 |
| John S | Laptop | |
| Jane D | Laptop | |
| Jane D | Monitor | |
In my gallery, I want to display the following, where only those without a DateReturned have a count above 0:
Sam B
Devices: 0
----------------
John S
Devices: 1
----------------
Jane D
Devices: 2
The code below with GroupBy and CountRows is only returning records with counts above 0, which makes sense, since I am filtering for DateReturned = Blank(). This code returns:
John S
Devices: 1
----------------
Jane D
Devices: 2
Sort(
Search(
AddColumns(
GroupBy(
Filter('Inventory', DateReturned = Blank()),
Username,
GroupedItems
),
IssueCount,
CountRows(GroupedItems)
),
txtEmployeeSearch.Value,
Username
),
Username
)
Is there a way for me to return ALL records (without the DateReturned filter), but have the CountRows return 0 for those with no blank DateReturned values? It would be like a LEFT JOIN in SQL, but I haven't been able to figure it out. I tried placing the data in a collection and playing with it, but the GroupBy stopped working and it was showing separate records because I included the DateReturned in the results, so I could find a way to work around it.
