Extra data in nested table

[JMacFeegle]

I'm building a small app to manage catering equipment (think plates, glasses, cutlery, but also larger appliances for live cooking, like barbecues and ovens).

Basis is an inventory list containing items and item count (e.g. 100 champagne glasses, 150 cocktail glasses, 2 barbecues, etc.)

The idea is to "book" X amount of available items (e.g. 40 of 100 available champagne glasses) for a certain time period. For this time period, there are now only 60 champagne glasses left available.
Subsequent bookings can now only use those 60 glasses (if the timeframe for the new booking overlaps with the previously reserved timeframe).

• Items (listing inventory)
• Bookings (details of the single bookings including start/end date)
• Item_bookings (listing the items and # of items for every booking)

The basic mechanics are working. Now, if I open details for an item in the Inventory table, I want to list the bookings (including start and end date) this item is used in, and in what quantity.
VC with [Related Item_bookings][ID_booking] gives me a nested table with related bookings, BUT I also want to display how many of this item have been allocated to those bookings.

I'm thinking I would need to add a VC to the bookings table that is calculated based on values from the current (Inventory) row I'm looking at? Is that even possible?

[yasarcan]

merhaba. çeviriden anlayabildiğim kadarıyla. SUM(SELECT(Item_bookings[Quantity], [ID_booking] = [_THISROW].[ID]))

[JMacFeegle]

Thanks @yasarcan !

Unfortunately, the items table does not contain [ID_booking]. It only contains the ID, names and absolute number of inventory items. So [ID_booking] = [_THISROW].[ID] would not work.

[Suvrutt_Gurjar · Accepted solution]

Maybe I am missing some obvious point but does [Related Item_bookings] in the Inventory table not show a child table with Items and their quantities?

[JMacFeegle]

DUH. Yes it does. Thanks!

In German I would say "Didn't see the forest for all the trees".

[JeeHill]

You're on the right track @JMacFeegle !

If you want to see how many units of an item are booked in each reservation when viewing an Inventory item, you need to pull that info from Item_bookings.

Try this in a Virtual Column in Inventory:

SELECT(Item_bookings[Quantity], [Item_ID] = [_THISROW].[Item_ID])

This gives you a list of all booked quantities for that item.

If you need the total booked quantity, use:

SUM(SELECT(Item_bookings[Quantity], [Item_ID] = [_THISROW].[Item_ID]))

Why not [Related Item_bookings].[Quantity]? Because [Related Item_bookings] is a list, and you can’t just grab [Quantity] directly—SELECT() is the way to go.

Now, when you open an item in Inventory, you’ll see all its reservations plus the quantities booked.

Jee

[JMacFeegle]

This would give me the total # of booked items, but I want/need a list of all the bookings with their respective quantities and dates etc.

@Suvrutt_Gurjar had the right and very obvious (except to me!) idea.