Count Related Items (Children) in form view

[Fabian_Weller]

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

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

Adding @MultiTech_Visions

[Fabian_Weller]

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()?

[Suvrutt_Gurjar]

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

Fabian:

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.

Maybe you wish to try

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

[Fabian_Weller]

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

---

Suvrutt_Gurjar:

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

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().

[Suvrutt_Gurjar]

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

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

[MultiTech]

Fabian:

But now let’s say I have another condition: Count only the Items with the same category.

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]
))

[Fabian_Weller]

MultiTech_Visions:

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

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?

[Steve]

I was fine with this:

Fabian:

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

Fabian:

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() ?

Gotta use SELECT().

1minManager:

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.

Correct.

Fabian:

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

Either is fine.

Fabian:

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

Correct.

Suvrutt_Gurjar:

I think more conditions will mean we may need to pull in SELECT() services in the picture

Yep.

Fabian:

I ended up with this expression:

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

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()).

[1minManager]

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?

[MultiTech]

You’re on the right track!

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

[Fabian_Weller]

Hi @MultiTech_Visions thank you so much.

I tried you tip using list math.

MultiTech_Visions:

     {1, 2, 3, 1, 2}
 +   {1, 4, 5}
--------------------
 =  {1, 2, 3, 4, 5}

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.

New Bug Encountered: Valid If removes Duplicates Questions

I see a difference in the Expression Assistant and the App. I have a simple Text Column that is scanable. In Valid If I say LIST(1,2,3)+LIST(1,2,3) The Expression Assistant gives me the result with duplicates. [image] But the App removes duplicates. [image] In my App I need to have the duplicates. (In real I’m saying: give me all Item Numbers and substract those already added. But since some Items can be added twice, I need to have duplicates.) I know that subtracting one list from anot…

---

One workaround is to use -LIST()

Best practice for unique reference list, no duplicates Questions

Although not supported behavior, subtracting one list from another has the side-effect of removing duplicates. So this: SELECT(..., ..., TRUE) is equivalent to this: SELECT(..., ..., FALSE) - LIST() Or, if you’re combining several lists that overlap: LIST(...) + SELECT(...) + { ... } - LIST() would merge the lists and remove duplicates. Again, this is a coincidental side-effect and unsupported behavior. The behavior may change in the future without notice.

{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.

[Fabian_Weller]

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

Steve:

This only works if the Items and Categories tables use the same key column values.

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.

[eli_debu]

count([Related Items])