My understanding is that when your in a form and you to a (Select( function its only reading whats in the table. Not in the actual form your in. I think I had a conversation about this with @Steve a few months ago so maybe he might be able to elaborate on this here?
Thank you @Suvrutt_Gurjar this is very nice.
But now let’s say I have another condition: Count only the Items with the same category.
So I would do: COUNT(SELECT(Items[ID],AND([Order]=[_THISROW].[Order],[Category]=[_THISROW].[Category],[ID]<>[_THISROW].[ID])))+1
It seems to me, that as soon as I have a condition, I’m forced to use SELECT().
A list of all the Items from the order (along with adding in the specific item from the form if needed)
A list of all the items from the associated category
INTERSECT() says: “Reduce these lists to only the items that overlap”
which will give us only the [Order].[Related_Items] that have a companion inside the [Category].[Category_Items] list
Thus giving us a list of all the order items for that category - which we can then count.
You’ll likely have to create a list dereference of the actual item IDs from your [Order].[Related Items], because that’s going to be a list of the IDs for the order line items - probly NOT a list of the items selected in those line items.
So you’ll most likely want to create a separate list on the order to hold the list of itemIDs - call it “Order_Items_From_LineItems” or something