Virtual column type "List" sums wrong number of list entries

[Viktor]

Some context:
I have three tables, an “Orders” table, a “Units” table and a “Pallets” table.
There is a reference between “Orders” and “Units” and between “Units” and “Pallets”. So, a unit belongs to an order, and a unit is placed on a pallet. There is no direct relation between pallets and orders.
However, I’d like to get a list of all the pallets that contain units which belong to a specific order.

I have tried with a formula in the “Orders” table like this:
SELECT(Pallets[UID], IN([UID], SELECT(Units[Location (pallet)], [ION] = [_thisrow].[ION])))

This formula does retrieve the correct pallets but the sum that Appsheet automatically creates a the top of the list seems to sum the amount of units (the inner SELECT() statement), rather than the amount of pallets. Is this a bug? And is there anything I can do about it?

Best regards

[Steve]

Please post a screenshot demonstrating the problem.

[Viktor]

Of course.

Ignore the non-English, and notice how it says (62) in the top, which would normally be the sum of entries in the list. Here there are only three entries (pallets).

Here is an expanded view, where I’ve clicked the “View” showing there are still only three entries.

62 is also the exact number of units that are registered to this particular order, making it seem like it sums on the inner SELECT() statement in my formula, rather than the outer. Let me know if you need more information!

[Steve · Accepted solution]

My guess is that your (outer) SELECT() expression is returning 62 results, but those 62 results include only 3 distinct values. Try adding TRUE as a third argument to the SELECT() to remove duplicates:

SELECT(Pallets[UID], ..., TRUE)

See also:

help.appsheet.com

SELECT()

Gather column values from row in table or slice

[Viktor]

Ah, that would be logical, the outer SELECT() should indeed return 62 values in total, including duplicates. Adding true as an argument made it work. Although, I’m curious to why it only showed the distinct values in the list, when it returned duplicates?

[Steve]

Viktor:

I’m curious to why it only showed the distinct values in the list, when it returned duplicates?

That’s inherent in aggregate views: they show rows that occur in the given list of rows, rather than displaying the given list of rows as given, if that makes sense.

[Viktor]

Yeah that makes sense. Thanks for the explanation