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

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

Please post a screenshot demonstrating the problem.

2 Likes

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

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

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!

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:

3 Likes

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?

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.

1 Like

Yeah that makes sense. Thanks for the explanation

1 Like