See if you catch what I’ve done here:
The list of “unique” order numbers for the cartons records in this query result included at least one array element that was itself a delimited string - representing one carton that was a consolidated carton comprised of components for two separate order numbers. Joining the array to a delimited string (using the same delimiter as the array element delimited string), then splitting it with that same delimiter, gives the array of separated order numbers (so we can use them for the next step in our analysis of order completeness).
LOL, spoke too soon - one more step may be needed to get the count right in case the above approach results in duplicated elements in the array.
To account for this possibility I can get an aggregation of unique values of the sales order numbers from a query looking for table records where the sales order number IS IN my array:
Then I just overwrite the order analysis array with the new aggregation results before calculating the array length / number of sales orders:
Great approach!
To handle duplicates and alphabetical order, I suggest using a List-Tool-Custom-Widget.
This applies to many other situations, where you can solve things way easier than adding more and more queries and aggregations.
E.g. when you have any object list to display in an interactive table widget, you can map any column to an array (e.g. status) and then order it and remove duplicates immediately for a drop down filter list. 
1 Like
I would love to do this but in my GxP work we have opted to not use custom widgets.
Is there a way we could do this through a connector function, somehow?
Sure:
The input is a comma separated string, the output is a list.
So you need to prepare the data, since you can not provide a list as array to the function.
WITH parts AS (
SELECT NULLIF(trim(x), ‘’) AS val
FROM regexp_split_to_table($input$, ‘\\s\*,\\s\*’) AS x
)
SELECT DISTINCT
val,
lower(val)
FROM parts
WHERE val IS NOT NULL
ORDER BY lower(val), val;
also you could build something with node red or so. However, when custom widgets are not allowed, this might also be …
