Answered

Filter list based on sum of lookup values

(0) Share

Report

Posted on by Dioz

Hello everyone, following scenario:

2 SharePoint lists: Assets and Bookings. Every item in Bookings has a lookup to the booked asset and an amount of how many have been booked, while the Assets list has a total amount called stock.

Currently I have a Combobox with all available assets, and when I select one, I have a label that tells me how many are still available during a previously selected timeframe between StartDate and EndDate.

The code looks like this:

LookUp(
 Assets,
 ID = cmb_Asset.Selected.ID
).Stock - Sum(
 Filter(
 AssetBooking,
 Asset.Id = cmb_Asset.Selected.ID && StartDate <= eventSelected.EndDate && EndDate >= eventSelected.StartDate
 ),
 Amount
)

This work fine for me. But now I want to change the combobox in a way, that it only shows the assets, that have at least 1 available item during the event.

I played around a lot with filters and lookups, but I can't seem to get it right. Can anyone help me with this?

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Dioz 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @Rajkumar_404 ! I tried pretty much exactly that before, but it doesn't seem to work. I think the problem is with the "Asset.Id = ID" part. ID in that context would refer to the ID of the booking itself, while Asset.Id refers to the ID of the item in the asset list. I somehow have to check the Asset.Id against the ID of the Asset in the "first" filter.
Dioz 61 on at

Like
a
(0)

Report
Copy link

Link copied!

Anyone an idea what else I could try? Could a distinct function help me in this case?
Verified answer

Dioz 61 on at

Like
a
(1)

Report
Copy link

Link copied!

Just to properly close the case: Assets[@ID] was what I was looking for, to iterate through all items in the Assets list, didn't know that this is possible.