Count Related Items (Children) in form view

Lets’s say we have 2 Tables:
Orders - Parent
Items - Child

In the Orders Table, AppSheet automatically creates the column [Related Items].

In the Items table I want to know how many Items are created. So I use this expression:
COUNT([Orders].[Related Items])

This is working. But when I add a new item, this new item is not counted in the form view. I first have to save that new item, then it’s counted.

So my workaround is this:
COUNT(SELECT(Items[ID],AND([Order]=[_THISROW].[Order],[ID]<>[_THISROW].[ID])))+1

This counts all Related Items, except the row I’m in. Then it adds 1.

This is working, but it seems to me that there should be a better way :wink:

P.S.: I need this calculation in the form view. Another column needs this for calculation.

Adding @MultiTech_Visions

1 Like

Another basic question:

COUNT([Orders].[Related Items])
is the same as
COUNT(SELECT(Items[ID],[Order]=[_THISROW].[Order]))
but the first expression is more efficient.

What when I need to count only related items with a specific condition?
Is there any way to use [Orders].[Related Items]?
Or do I have to use SELECT()?

1 Like

Hi @Fabian

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?

3 Likes

I am sure @MultiTech_Visions vision will have much more efficient solution.

Maybe you wish to try

COUNT([Orders].[Related Items] -LIST([_THISROW]))+1

3 Likes

Thank you @1minManager This really seems to be the case.


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
Or:
COUNT(SELECT(Items[ID],AND([Order]=[_THISROW].[Order],[Category]=[_THISROW].[Category]))-LIST([_THISROW]))+1

It seems to me, that as soon as I have a condition, I’m forced to use SELECT().

2 Likes

@Fabian - Yes, you are correct. I think more conditions will mean we may need to pull in SELECT() services in the picture :slight_smile:

I am sure guidance from @MultiTech_Visions vision and @Steve will be insightful.

3 Likes

You’re on the right track! partyparrot (Appsheet)

Instead of trying to compensate for the missing ‘newly created record’ with a select(), make use of the nature of the AppSheet platform with list math.

When you perform list math with lists, the resulting list is automatically reduced to it’s simplest terms.

   {1, 2, 3, 1, 2}
-  {1, 2}
--------------------
= {3}

This also means that if you add duplicated items to a list… they’re ignored.

Examples
     {1, 2, 3, 1, 2}
 +   {1, 4, 5}
--------------------
 =  {1, 2, 3, 4, 5}
  • The original duplicated (1 & 2) is removed, along with the triplicated (1) that’s added.
     {Toyota, Ford, Buick}
 +   {Tesla, Ford}
----------------------------------
 =  {Toyota, Ford, Buick, Tesla}
  • The duplicate (Ford) is removed
     {Red, Yellow, Green}
 +   {Red}
---------------------------------
 =  {Red, Yellow, Green}
  • Since (Red) is already in the list, appsheet ignores it.

Try this as your formula

COUNT(
  [Orders].[Related_Line_Items]
  + list([LineItemID])
)
  • If the [LineItemID] value is already inside the [Related_Line_Items] it will automatically have the duplicate removed
    • So when you’re creating the record, you’ll get an accurate count because your newly created record will be added into the list manually with your formula
    • And when you’ve already created the record, you’ll get an accurate count because the duplicate record will be reduced away to a single value

Small moonwalker

3 Likes

In this instance, I would implement a Category table - and create a reference connection between that and the Items table;

  • this will give you a reverse reference of all the category items
    • and with the reference column on the item table, you can easily de-ref the related items

And since all of these lists are calculated with REF_ROWS() it’s super efficient.


Your final formula would look something like this:

COUNT(INTERSECT(
  [Orders].[Related_Line_Items]
  + list([LineItemID]),
  [Category].[Category_Items]
))
  • This takes two lists
    1. A list of all the Items from the order (along with adding in the specific item from the form if needed)
    2. 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.

Note

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

[Related Items][Item_ID_Selected]

Then you can use this column in you INTERSECT()

COUNT(INTERSECT(
  [Orders].[Order_Items_From_LineItems]
  + list([Item_ID_Selected]),
  [Category].[Category_Items]
))
3 Likes

Hi @MultiTech_Visions thank you so much.

I tried you tip using list math.

I’m sorry but this is not the case.

{1, 2, 3, 1, 2} + {1, 4, 5}
will give this result: 1 , 2 , 3 , 1 , 2 , 1 , 4 , 5
The COUNT() will be 8, not 5.

Subtracting one list from another has the side-effect of removing duplicates.
This is not the case when adding one list to another.

It only seems to be the case in the valid if.


One workaround is to use -LIST()

{1, 2, 3, 1, 2} + {1, 4, 5} - LIST()
will give this result: 1 , 2 , 3 , 4 , 5
The COUNT() will be 5.

But as @Steve mentioned: The behavior may change in the future without notice.

So I will use UNIQUE()
UNIQUE({1, 2, 3, 1, 2} + {1, 4, 5})
will give this result: 1 , 2 , 3 , 4 , 5
The COUNT() will be 5.

2 Likes

Thank you for this tip. I created a separate Category table.

I ended up with this expression:

COUNT(
  INTERSECT(
    UNIQUE(
      [Orders].[Related Items]
      +LIST([ID])
    ),
    UNIQUE(
      [Category].[Related Categorys]
      +LIST([ID])
    )
  )
)

@MultiTech_Visions @Steve Do you think this is most efficient?

1 Like

I was fine with this:

Gotta use SELECT().

Correct.

Either is fine.

Correct.

Yep.

This only works if the Items and Categories tables use the same key column values. I myself wouldn’t use this approach. I’d stick with SELECT() (or FILTER()).

3 Likes

Thank you very much for your insight @Steve
The point is that I want to use SELECT() as less as possible, because of performance.

The Items table now has 2 Ref columns: Order and Category.
That’s why [Order].[Related Items] and [Category].[Related Items] will give me key column values from the Inline table.

1 Like